Migrates MySQL database table columns from `latin1` to `utf8` that have been stored in a mixed environment

database-charset-converter.php

<?php
/**
 * Migrates MySQL database table columns that have been stored in a mixed
 * environment - e.g. database running on `latin1` but using `SET NAMES utf8`
 * during the connection when writing data to the DMBS.
 *
 * Expected source character set: latin1
 * Defined target character set: utf8
 * Defined target collation: utf8_general_ci
 *
 * @author Oliver Hader <oliver.hader@typo3.org>
 * @license GPLv2 (or any later version)
 * @modified 2018-06-05
 */

if ($argc < 4) {
    echo implode(PHP_EOL, [
        '+ Usage',
        '++++++++',
        sprintf('%s database username password [hostname]' . PHP_EOL, $argv[0])
    ]);
    exit(1);
}

$arguments = $argv;
array_shift($arguments);

$database = array_shift($arguments);
$username = array_shift($arguments);
$password = array_shift($arguments);
$hostname = array_shift($arguments);

if (empty($hostname)) {
    $hostname = '127.0.0.1';
}

$dsn = sprintf('mysql:dbname=%s;host=%s', $database, $hostname);
$pdo = new \PDO($dsn, $username, $password);


$statement = $pdo->query(
    'SELECT TABLES.TABLE_NAME, TABLES.TABLE_COLLATION, CCSA.CHARACTER_SET_NAME '
    . 'FROM information_schema.TABLES TABLES, '
    . 'information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA '
    . 'WHERE CCSA.COLLATION_NAME = TABLES.TABLE_COLLATION AND TABLES.TABLE_SCHEMA=' . $pdo->quote($database)
);

$changed = 0;
$tables = $statement->fetchAll(\PDO::FETCH_ASSOC);
foreach ($tables as $table) {
    $tableName = $table['TABLE_NAME'];
    $statement = $pdo->query(
        'SELECT COLUMN_NAME, COLUMN_DEFAULT, COLUMN_TYPE, IS_NULLABLE, '
        . 'DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS '
        . 'WHERE TABLE_NAME=' . $pdo->quote($tableName) . ' AND TABLE_SCHEMA=' . $pdo->quote($database)
    );
    $columns = array_filter(
        $statement->fetchAll(\PDO::FETCH_ASSOC),
        function (array $column) {
            return $column['CHARACTER_SET_NAME'] === 'latin1'
                || strpos($column['COLLATION_NAME'], 'latin1_') === 0;
        }
    );

    foreach ($columns as $column) {
        $columnName = $column['COLUMN_NAME'];
        echo sprintf('+ %s.%s... ', $tableName, $columnName);

        $parts = [
            'ALTER TABLE',
            $tableName,
            'CHANGE',
            $columnName,
            $columnName,
            'BLOB',
        ];
        $pdo->query(implode(' ', $parts));

        $parts = [
            'ALTER TABLE',
            $tableName,
            'CHANGE',
            $columnName,
            $columnName,
            $column['COLUMN_TYPE'],
            'CHARACTER SET utf8',
            'COLLATE utf8_general_ci',
            ($column['IS_NULLABLE'] ? '' : 'NOT ') . 'NULL',
        ];
        if ($column['COLUMN_DEFAULT'] !== null) {
            $parts[] = 'DEFAULT ' . $pdo->quote($column['COLUMN_DEFAULT']);
        }
        $pdo->query(implode(' ', $parts));
        $changed++;

        echo 'done' . PHP_EOL;
    }

    if ($table['CHARACTER_SET_NAME'] === 'latin1'
        || strpos($table['TABLE_COLLATION'], 'latin1_') === 0
    ) {
        echo sprintf('* %s... ', $tableName);
        $pdo->query(implode(' ', [
            'ALTER TABLE',
            $tableName,
            'CHARACTER SET utf8',
            'COLLATE utf8_general_ci',
        ]));
        echo 'done' . PHP_EOL;
    }
}

if ($changed === 0) {
    echo 'All fine. Not changes required...' . PHP_EOL;
}