Changing Laravel MYSQL to utf8mb4 for Emoji Support in Existing Database

Question

I am using Laravel 5.3 and I have already set-up my production server. All DB Migrations were already created using the following database config:

'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 had reported that they get an error when they try to save a form that has emoji 😊 icons in them. After searching I found out that I need to set the mysql charset to utf8mb4 for this to work so my config should have been something like this instead:

'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 in a production server, I cannot do migrate:refresh. So my questions are:

1. How can I change my existing database 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?
2. If the above is possible, am I better off setting utf8mb4 for all tables or only use that for the 2 table columns where I will really be using emoji.

Thank you for your help.

Answer 1

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.

Hope this will help you