migration to fix table collations

2025_04_08_175042_fix_collations_in_all_tables.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration {
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        if(app()->environment() === 'local') {
            return;
        }
        
        $db_name = config('database.connections.mysql.database');
        DB::statement("SET SESSION sql_mode = '';");
        Schema::disableForeignKeyConstraints();

        $tables = DB::select("SELECT `TABLE_NAME`, `TABLE_COLLATION`, `ENGINE` FROM `INFORMATION_SCHEMA`.`TABLES`
                                       WHERE `TABLE_SCHEMA` = '$db_name'");

        foreach ($tables as $table) {
            DB::statement("ALTER TABLE `$db_name`.{$table->TABLE_NAME} ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

            $sql = <<<'sql'
SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` MODIFY `",`COLUMN_NAME`,"` ",COLUMN_TYPE," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ",IF(`IS_NULLABLE`='YES', 'NULL', 'NOT NULL')," ",IF(`COLUMN_DEFAULT` IS NOT NULL, CONCAT(" DEFAULT '", `COLUMN_DEFAULT`, "'"), ''),";") as tmp FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = ':dbname' AND `TABLE_NAME` = ':table_name' AND (`CHARACTER_SET_NAME` != 'utf8mb4' OR `COLLATION_NAME` != 'utf8mb4_unicode_ci');
sql;
            $modified_Statements = DB::select(str_replace([':dbname', ':table_name'], [$db_name, $table->TABLE_NAME], $sql));

            foreach ($modified_Statements as $modified_sql) {
                DB::statement($modified_sql->tmp);
            }
        }

        Schema::enableForeignKeyConstraints();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        //
    }
};