Created
February 7, 2018 11:56
-
-
Save NBZ4live/04d5981eaf0244b57d0296b381e04195 to your computer and use it in GitHub Desktop.
Laravel migration to migrate the database from utf8 to utf8mb4
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
use Illuminate\Support\Facades\Schema; | |
use Illuminate\Database\Schema\Blueprint; | |
use Illuminate\Database\Migrations\Migration; | |
class MigrateToUtf8mb4 extends Migration | |
{ | |
/** | |
* Run the migrations. | |
* | |
* @return void | |
*/ | |
public function up() | |
{ | |
$this->migrateCharsetTo('utf8mb4', 'utf8mb4_unicode_ci'); | |
} | |
/** | |
* Reverse the migrations. | |
* | |
* @return void | |
*/ | |
public function down() | |
{ | |
$this->migrateCharsetTo('utf8', 'utf8_unicode_ci'); | |
} | |
protected function migrateCharsetTo($charset, $collation) | |
{ | |
$defaultConnection = config('database.default'); | |
$databaseName = config("database.connections.{$defaultConnection}.database"); | |
// Change default charset and collation | |
DB::unprepared("ALTER SCHEMA {$databaseName} DEFAULT CHARACTER SET {$charset} DEFAULT COLLATE {$collation};"); | |
// Get the list of all tables | |
$tableNames = DB::table('information_schema.tables') | |
->where('table_schema', $databaseName)->get(['TABLE_NAME'])->pluck('TABLE_NAME'); | |
// Iterate through the list and alter each table | |
foreach ($tableNames as $tableName) { | |
DB::unprepared("ALTER TABLE {$tableName} CONVERT TO CHARACTER SET {$charset} COLLATE {$collation};"); | |
} | |
// Get the list of all columns that have a collation | |
$columns = DB::table('information_schema.columns') | |
->where('table_schema', $databaseName) | |
->whereNotNull('COLLATION_NAME') | |
->get(); | |
// Iterate through the list and alter each column | |
foreach ($columns as $column) { | |
$tableName = $column->TABLE_NAME; | |
$columnName = $column->COLUMN_NAME; | |
$columnType = $column->COLUMN_TYPE; | |
$null = 'DEFAULT NULL'; | |
if ($column->IS_NULLABLE == 'NO') { | |
$null = 'NOT NULL'; | |
} | |
$sql = "ALTER TABLE {$tableName} | |
CHANGE `{$columnName}` `{$columnName}` | |
{$columnType} | |
CHARACTER SET {$charset} | |
COLLATE {$collation} | |
{$null}"; | |
DB::unprepared($sql); | |
} | |
} | |
} |
Really nice !!!
This is exactly what I needed. In my case I had tables that had reserved keywords. I had to wrap {$tableName}
with backticks for the migration to get through.
Thanks, i've been looking for this migration for quite a time !
A proposal for improvement:
- it doesn't work if your database has views, especially with views that have spaces in their names,
- it overwrites the default values for all columns - and that can be a big problem!
Thanks, i've been looking for this migration for quite a time !
A proposal for improvement:
- it doesn't work if your database has views, especially with views that have spaces in their names,
- it overwrites the default values for all columns - and that can be a big problem!
Great job.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks!!