Changing Laravel MYSQL to utf8mb4 to support Emoji in an existing database

Question

I am using Laravel 5.3 and I have already set up my production server. All database migrations have already been created using the following database configuration:

'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', 'localhost'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ], 

But now some of my users have reported that they get an error when trying to save a form that has emoji icons .. After searching, I found out that I need to install mysql charset in utf8mb4 for this to work, so my configuration should have been with something like this:

 'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', 'localhost'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ], 

Since this is on a production server, I cannot do migrate:refresh . So my questions are:

• How can I modify an existing database that I created using laravel migration to use utf8mb4 instead of utf8 , and also update laravel on the same? Is there an easier way to do this?
• If possible, I better install utf8mb4 for all tables or use only for two columns of the table, where I will really use emoji.

Thank you for your help.

Answer 1

• Use raw mysql query to write script update table migration and execute 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'); } } 

• My personal preferences, update table. I have no evidence to say which is better

Note. You still need to save the utf8mb4 database utf8mb4 .

Hope this helps you

Answer 2

for those who want to achieve this in the entire database, I needed a script like this:

 <?php use 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"; } } 

Answer 3

Based on @ insomniak-dev's answer, but will only compress varchar when they exceed the limits AND are used in the index. Otherwise, they are converted, but the size remains as it is. If the column is shortened, then it checks to see if any data will be truncated.

It also processes all types of text and packages of all conversions for each table into one statement for speed.

The Dryrun flag displays useful sql instead of direct application.

 /** * 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); } }