Skip to content

Instantly share code, notes, and snippets.

@anned20

anned20/convert_latin1_to_utf8mb4.php

Created April 23, 2020 09:53
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save anned20/e574db4fb93e2ece89a0301ace99b606 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save anned20/e574db4fb93e2ece89a0301ace99b606 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. anned20 revised this gist Apr 23, 2020. 1 changed file with 31 additions and 31 deletions.
    62 changes: 31 additions & 31 deletions convert_latin1_to_utf8mb4.php
    View file
    Original file line number Diff line number Diff line change
    @@ -50,18 +50,18 @@
    */

    $dsn = 'mysql:host=localhost;port=3306;charset=utf8';
    $user = 'user'; // @TODO CHANGE ME
    $password = 'password'; // @TODO CHANGE ME
    $user = 'sibben'; // @TODO CHANGE ME
    $password = 'sibben'; // @TODO CHANGE ME
    $options = [
    \PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY,
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET CHARACTER SET latin1',
    \PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY,
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET CHARACTER SET latin1',
    ];

    $dbManager = new \PDO($dsn, $user, $password, $options);

    // Databases to actually convert
    $databasesToConvert = ['database']; // @TODO CHANGE ME
    $databasesToConvert = ['oz_dev'];
    $typesToConvert = ['char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext'];

    // Disable foreign_key_checks for this session
    @@ -91,65 +91,65 @@ function handlePossibleError($dbManager) {

    foreach ($databasesToConvert as $database) {
    line($database);
    line(str_repeat('=', strlen($database) + 1));
    line(str_repeat('=', strlen($database) + 1));

    $dbManager->exec("USE `{$database}`");
    $dbManager->exec("USE `{$database}`");

    line('Converting database to correct locale');
    line('Converting database to correct locale');

    $dbManager->exec("ALTER DATABASE `{$database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci");
    $dbManager->exec("ALTER DATABASE `{$database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci");

    $tablesStatement = $dbManager->query('SHOW TABLES');
    while (($table = $tablesStatement->fetchColumn())) {
    $tablesStatement = $dbManager->query('SHOW TABLES');
    while (($table = $tablesStatement->fetchColumn())) {
    line('Table %s:', $table);
    line(str_repeat('-', strlen($table) + 8));
    line(str_repeat('-', strlen($table) + 8));

    $columnsToConvert = [];
    $columnsToConvert = [];

    $columsStatement = $dbManager->query("DESCRIBE `{$table}`");
    $columsStatement = $dbManager->query("DESCRIBE `{$table}`");

    while (($tableInfo = $columsStatement->fetch(\PDO::FETCH_ASSOC))) {
    $column = $tableInfo['Field'];
    $type = preg_replace('~\(\d+\)~', '', $tableInfo['Type']);
    while (($tableInfo = $columsStatement->fetch(\PDO::FETCH_ASSOC))) {
    $column = $tableInfo['Field'];
    $type = preg_replace('~\(\d+\)~', '', $tableInfo['Type']);

    if (in_array($type, $typesToConvert)) {
    if (in_array($type, $typesToConvert)) {
    $action = 'must be converted';

    $columnsToConvert[] = $column;
    } else {
    $columnsToConvert[] = $column;
    } else {
    $action = 'not relevant';
    }
    }

    line('* %s: %s => %s', $column, $tableInfo['Type'], $action);
    }
    }

    $convert = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `{$table}` ROW_FORMAT=DYNAMIC";
    $convert = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `{$table}` ROW_FORMAT=DYNAMIC";

    line();
    line($convert);

    $dbManager->exec($convert);
    $dbManager->exec($convert);

    handlePossibleError($dbManager);

    if (!empty($columnsToConvert)) {
    if (!empty($columnsToConvert)) {
    $converts = array_map(function ($column) {
    return "`{$column}` = CONVERT(BINARY(CONVERT(`{$column}` USING latin1)) USING utf8mb4)";
    }, $columnsToConvert);

    $query = "UPDATE IGNORE `{$table}` SET ".implode(', ', $converts);
    $query = "UPDATE IGNORE `{$table}` SET ".implode(', ', $converts);

    line();
    line($query);

    $dbManager->exec($query);
    $dbManager->exec($query);

    handlePossibleError($dbManager);
    }
    }

    line('--');
    line();
    }
    }
    }

    $dbManager->exec('SET foreign_key_checks=1');
    $dbManager->exec('SET foreign_key_checks=1');
  2. anned20 renamed this gist Apr 23, 2020. 1 changed file with 0 additions and 0 deletions.
    0 conert_latin1_to_utf8mb4.php → convert_latin1_to_utf8mb4.php
    View file
    File renamed without changes.
  3. anned20 created this gist Apr 23, 2020.
    155 changes: 155 additions & 0 deletions conert_latin1_to_utf8mb4.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,155 @@
    <?php

    /**
    * This script can convert a latin1 database(s) to utf8mb4 and then actually convert the data too.
    *
    * Actually stolen from and tweaked a lot from
    * @link https://gist.github.com/pobegov/f8b293fb6eb658a13feb1c318e6c07ed
    *
    * Disclaimer: This is a really _hacky_ script but it does the job.
    *
    * =====
    * USAGE
    * =====
    *
    * This script is not designed to be run more than once.
    *
    * This is designed to be use as a CLI script.
    *
    * Fill in the correct username, password and the database(s) you want to convert and it'll do the heavy lifting.
    *
    * It's probably a good idea to repair and optimize all tables afterwards from the command line using:
    * mysqlcheck -u root -p --auto-repair --optimize --all-databases
    *
    * =========
    * IMPORTANT
    * =========
    *
    * Make sure your database has the following parameters set:
    * innodb_file_per_table : ON
    * innodb_large_prefix : ON
    * innodb_file_format : Barracuda
    * innodb_file_format_max : Barracuda
    * innodb_default_row_format : dynamic
    *
    * You can verify this from the MySQL command line by using the following commands:
    * SHOW VARIABLES LIKE "innodb_file_per_table";
    * SHOW VARIABLES LIKE "innodb_file_format%";
    * SHOW VARIABLES LIKE "innodb_large_prefix";
    * SHOW VARIABLES LIKE "innodb_default_row_format";
    *
    * You can put these settings in your MySQL config like this:
    * innodb_file_per_table=ON
    * innodb_large_prefix=ON
    * innodb_file_format=Barracuda
    * innodb_default_row_format='DYNAMIC'
    *
    * Verify this script before running it, and make sure to BACKUP YOUR DATABASE BEFORE
    *
    * @author https://github.com/anned20
    */

    $dsn = 'mysql:host=localhost;port=3306;charset=utf8';
    $user = 'user'; // @TODO CHANGE ME
    $password = 'password'; // @TODO CHANGE ME
    $options = [
    \PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY,
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET CHARACTER SET latin1',
    ];

    $dbManager = new \PDO($dsn, $user, $password, $options);

    // Databases to actually convert
    $databasesToConvert = ['database']; // @TODO CHANGE ME
    $typesToConvert = ['char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext'];

    // Disable foreign_key_checks for this session
    $dbManager->exec('SET foreign_key_checks=0');

    /**
    * Helper function to print a nice line
    */
    function line($msg = '', ...$vars) {
    echo sprintf($msg, ...$vars).PHP_EOL;
    };

    /**
    * Helper function to handle any MySQL errors
    */
    function handlePossibleError($dbManager) {
    $databaseErrors = $dbManager->errorInfo();

    if (!empty($databaseErrors) && reset($databaseErrors)[0] != 0) {
    line('!!!!!!!!!!!!! ERROR OCCURED %s', print_r($databaseErrors, true));

    $dbManager->exec('SET foreign_key_checks=1');

    exit(1);
    }
    };

    foreach ($databasesToConvert as $database) {
    line($database);
    line(str_repeat('=', strlen($database) + 1));

    $dbManager->exec("USE `{$database}`");

    line('Converting database to correct locale');

    $dbManager->exec("ALTER DATABASE `{$database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci");

    $tablesStatement = $dbManager->query('SHOW TABLES');
    while (($table = $tablesStatement->fetchColumn())) {
    line('Table %s:', $table);
    line(str_repeat('-', strlen($table) + 8));

    $columnsToConvert = [];

    $columsStatement = $dbManager->query("DESCRIBE `{$table}`");

    while (($tableInfo = $columsStatement->fetch(\PDO::FETCH_ASSOC))) {
    $column = $tableInfo['Field'];
    $type = preg_replace('~\(\d+\)~', '', $tableInfo['Type']);

    if (in_array($type, $typesToConvert)) {
    $action = 'must be converted';

    $columnsToConvert[] = $column;
    } else {
    $action = 'not relevant';
    }

    line('* %s: %s => %s', $column, $tableInfo['Type'], $action);
    }

    $convert = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `{$table}` ROW_FORMAT=DYNAMIC";

    line();
    line($convert);

    $dbManager->exec($convert);

    handlePossibleError($dbManager);

    if (!empty($columnsToConvert)) {
    $converts = array_map(function ($column) {
    return "`{$column}` = CONVERT(BINARY(CONVERT(`{$column}` USING latin1)) USING utf8mb4)";
    }, $columnsToConvert);

    $query = "UPDATE IGNORE `{$table}` SET ".implode(', ', $converts);

    line();
    line($query);

    $dbManager->exec($query);

    handlePossibleError($dbManager);
    }

    line('--');
    line();
    }
    }

    $dbManager->exec('SET foreign_key_checks=1');