Changing Laravel MYSQL to utf8mb4 for Emoji Support in Existing Database

1. Use raw mysql query to write the update table migration script and run php artisan migrate command

    use Illuminate\Database\Migrations\Migration; class UpdateTableCharset extends Migration {     /**      * Run the migrations.      *      * @return void      */     public function up() {             DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4');     }     /**      * Reverse the migrations.      *      * @return void      */     public function down() {             DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8');     } }

2. My personal preference, update table. I don't have proof to say it is better

Note: You still have to keep the database config to utf8mb4.

Based on @insomniak-dev answer, but will only shrink varchar when they exceed limits AND are used in an index. Otherwise, they are converted, but size is left as is. If a column will be shrunk, it then checks if any data will be truncated.

This also handles all text types and batches all conversions for each table into a single statement for speed.

Dryrun flag outputs usable sql instead of applying directly.

/** * Run the migrations. * * @return void */public function up(){    $dryRun = true;    $this->convertDb('mysql', 'utf8mb4', 'utf8mb4_unicode_ci', $dryRun);    $this->convertDb('archive', 'utf8mb4', 'utf8mb4_unicode_ci', $dryRun);}/** * Reverse the migrations. * * @return void */public function down(){    $dryRun = true;    $this->convertDb('archive', 'utf8', 'utf8_unicode_ci', $dryRun);    $this->convertDb('mysql', 'utf8', 'utf8_unicode_ci', $dryRun);}private function convertDb($connection, $charset, $collate, $dryRun){    $dbName = config("database.connections.{$connection}.database");    $varchars = \DB::connection($connection)        ->select(\DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' and (CHARACTER_SET_NAME != '{$charset}' or COLLATION_NAME != '{$collate}') AND TABLE_SCHEMA = '{$dbName}'"));    // Check if shrinking field size will truncate!    $skip = [];  // List of table.column that will be handled manually    $indexed = [];    if ($charset == 'utf8mb4') {        $error = false;        foreach($varchars as $t) {            if ($t->CHARACTER_MAXIMUM_LENGTH > 191) {                $key = "{$t->TABLE_NAME}.{$t->COLUMN_NAME}";                // Check if column is indexed                $index = \DB::connection($connection)                    ->select(\DB::raw("SHOW INDEX FROM `{$t->TABLE_NAME}` where column_name = '{$t->COLUMN_NAME}'"));                $indexed[$key] = count($index) ? true : false;                if (count($index)) {                    $result = \DB::connection($connection)                        ->select(\DB::raw("select count(*) as `count` from `{$t->TABLE_NAME}` where length(`{$t->COLUMN_NAME}`) > 191"));                    if ($result[0]->count > 0) {                        echo "-- DATA TRUNCATION: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH}) => {$result[0]->count}" . PHP_EOL;                        if (!in_array($key, $skip)) {                            $error = true;                        }                    }                }            }        }        if ($error) {            throw new \Exception('Aborting due to data truncation');        }    }    $query = "SET FOREIGN_KEY_CHECKS = 0";    $this->dbExec($query, $dryRun, $connection);    $query = "ALTER SCHEMA {$dbName} DEFAULT CHARACTER SET {$charset} DEFAULT COLLATE {$collate}";    $this->dbExec($query, $dryRun, $connection);    $tableChanges = [];    foreach($varchars as $t) {        $key = "{$t->TABLE_NAME}.{$t->COLUMN_NAME}";        if (!in_array($key, $skip)) {            if ($charset == 'utf8mb4' && $t->CHARACTER_MAXIMUM_LENGTH > 191 && $indexed["{$t->TABLE_NAME}.{$t->COLUMN_NAME}"]) {                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR(191) CHARACTER SET {$charset} COLLATE {$collate}";                echo "-- Shrinking: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH})" . PHP_EOL;            } else if ($charset == 'utf8' && $t->CHARACTER_MAXIMUM_LENGTH == 191) {                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR(255) CHARACTER SET {$charset} COLLATE {$collate}";                echo "-- Expanding: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH})";            } else {                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR({$t->CHARACTER_MAXIMUM_LENGTH}) CHARACTER SET {$charset} COLLATE {$collate}";            }        }    }    $texts = \DB::connection($connection)        ->select(\DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE like '%text%' and (CHARACTER_SET_NAME != '{$charset}' or COLLATION_NAME != '{$collate}') AND TABLE_SCHEMA = '{$dbName}'"));    foreach($texts as $t) {        $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` {$t->DATA_TYPE} CHARACTER SET {$charset} COLLATE {$collate}";    }    $tables = \DB::connection($connection)        ->select(\DB::raw("select * from INFORMATION_SCHEMA.TABLES where TABLE_COLLATION != '{$collate}' and TABLE_SCHEMA = '{$dbName}';"));    foreach($tables as $t) {        $tableChanges["{$t->TABLE_NAME}"][] = "CONVERT TO CHARACTER SET {$charset} COLLATE {$collate}";        $tableChanges["{$t->TABLE_NAME}"][] = "DEFAULT CHARACTER SET={$charset} COLLATE={$collate}";    }    foreach ($tableChanges as $table => $changes) {        $query = "ALTER TABLE `{$table}` ".implode(",\n", $changes);        $this->dbExec($query, $dryRun, $connection);    }    $query = "SET FOREIGN_KEY_CHECKS = 1";    $this->dbExec($query, $dryRun, $connection);    echo "-- {$dbName} CONVERTED TO {$charset}-{$collate}" . PHP_EOL;}private function dbExec($query, $dryRun, $connection){    if ($dryRun) {        echo $query . ';' . PHP_EOL;    } else {        \DB::connection($connection)->getPdo()->exec($query);    }}

for anyone willing to achieve this on the whole database i found myself needing such a script :

<?phpuse Illuminate\Support\Facades\Schema;use Illuminate\Database\Schema\Blueprint;use Illuminate\Database\Migrations\Migration;use Illuminate\Support\Facades\DB;use Illuminate\Support\Facades\Config;class ChangeDbCharset extends Migration{    /**     * Run the migrations.     *     * @return void     */    public function up()    {        $charset = "utf8mb4";        $collate = $charset."_unicode_ci";        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');        $query = "ALTER SCHEMA $dbName DEFAULT CHARACTER SET $charset DEFAULT COLLATE $collate;\n";         DB::connection()->getPdo()->exec($query);        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');        $result = DB::select(DB::raw('show tables'));        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '$dbName';"));        //var_dump($test);        foreach($test as $t)        {            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME VARCHAR(191) CHARACTER SET $charset COLLATE $collate; \n";            echo $query;            DB::connection()->getPdo()->exec($query);        }        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' AND TABLE_SCHEMA = '$dbName';"));        foreach($test as $t)        {            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME TEXT CHARACTER SET $charset COLLATE $collate; \n";            echo $query;            DB::connection()->getPdo()->exec($query);        }        $result = DB::select(DB::raw('show tables'));        foreach($result as $r)        {            foreach($r as $k => $t)            {                $query = "ALTER TABLE `$t` CONVERT TO CHARACTER SET $charset COLLATE $collate; \n";                echo $query;                DB::connection()->getPdo()->exec($query);            }        }        echo "DB CHARSET set to $charset , $collate";    }    /**     * Reverse the migrations.     *     * @return void     */    public function down()    {        $charset = "utf8";        $collate = $charset."_unicode_ci";        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');        $query = "ALTER SCHEMA $dbName DEFAULT CHARACTER SET $charset DEFAULT COLLATE $collate;\n";         DB::connection()->getPdo()->exec($query);        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');        $result = DB::select(DB::raw('show tables'));        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '$dbName';"));        //var_dump($test);        foreach($test as $t)        {            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME VARCHAR(255) CHARACTER SET $charset COLLATE $collate; \n";            echo $query;            DB::connection()->getPdo()->exec($query);        }        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' AND TABLE_SCHEMA = '$dbName';"));        foreach($test as $t)        {            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME TEXT CHARACTER SET $charset COLLATE $collate; \n";            echo $query;            DB::connection()->getPdo()->exec($query);        }        $result = DB::select(DB::raw('show tables'));        foreach($result as $r)        {            foreach($r as $k => $t)            {                $query = "ALTER TABLE `$t` CONVERT TO CHARACTER SET $charset COLLATE $collate; \n";                echo $query;                DB::connection()->getPdo()->exec($query);            }        }        echo "DB CHARSET set to $charset , $collate";    }}