Easily convert MySQL database collations and alter character sets

I can’t remember what all I looked at for references, but this completed tool will generate the SQL¬† commands necessary to convert your database, tables and fields from any collation and character set into another.

It goes without saying to make a backup of your database before running such commands. If you don’t make a backup first, shame on you ūüėõ

<?php

function show_error($string) {
    header('Content-Type: text/plain');
    echo $string;
    exit(1);
}

function db_connect() {
    if ( ! mysql_connect($_POST['host'], $_POST['user'], $_POST['pass']) || ! mysql_select_db($_POST['name'])) {
        show_error(mysql_error());
    }
}

if (empty($_POST['host']) || empty($_POST['user']) || empty($_POST['pass']) || empty($_POST['name'])) {

?>
<form method="post">
    <fieldset>
        <legend>Database</legend>
        <label>Host</label>
        <input type="text" name="host" value="<?php echo (empty($_POST['host'])) ? 'localhost' : htmlentities($_POST['host']); ?>">
        <label>User</label>
        <input type="text" name="user" value="<?php echo htmlentities($_POST['user']); ?>">
        <label>Pass</label>
        <input type="text" name="pass" value="<?php echo htmlentities($_POST['pass']); ?>">
        <label>Name</label>
        <input type="text" name="name" value="<?php echo htmlentities($_POST['name']); ?>">
        <input type="submit" value="Go">
    </fieldset>
</form>
<?php

} elseif (empty($_POST['collate']) || empty($_POST['charset']) || !isset($_POST['alter_database']) || !isset($_POST['alter_tables']) || !isset($_POST['alter_fields'])) {

    db_connect();

    $collations = array();
    $sql = 'SHOW COLLATION WHERE `Compiled` = \'Yes\'';
    $rs = mysql_query($sql);
    while ($row = mysql_fetch_assoc($rs)) {
        $collations[] = $row['Collation'];
    }
    if (count($collations)) {
        sort($collations);
    } else {
        show_error('NO RESULTS FOR: ' . $sql);
    }

    $charsets = array();
    $sql = 'SHOW CHARACTER SET';
    $rs = mysql_query($sql);
    while ($row = mysql_fetch_assoc($rs)) {
        $charsets[] = $row['Charset'];
    }
    if (count($charsets)) {
        sort($charsets);
    } else {
        show_error('NO RESULTS FOR: ' . $sql);
    }

?>
<form method="post">
    <fieldset>
        <legend>Configuration</legend>
        <label>Collation</label>
        <select name="collate">
            <option><?php echo implode('</option><option>', $collations); ?></option>
        </select>
        <label>Character Set</label>
        <select name="charset">
            <option><?php echo implode('</option><option>', $charsets); ?></option>
        </select>
        <label>Show Database Alterations</label>
        <select name="alter_database">
            <option value="1">Yes</option>
            <option value="0"<?php echo (isset($_POST['tables']) && $_POST['tables'] == '0') ? ' selected="selected"' : ''; ?>>No</option>
        </select>
        <label>Show Table Alterations</label>
        <select name="alter_tables">
            <option value="1">Yes</option>
            <option value="0"<?php echo (isset($_POST['tables']) && $_POST['tables'] == '0') ? ' selected="selected"' : ''; ?>>No</option>
        </select>
        <label>Show Field Alterations</label>
        <select name="alter_fields">
            <option value="1">Yes</option>
            <option value="0"<?php echo (isset($_POST['fields']) && $_POST['fields'] == '0') ? ' selected="selected"' : ''; ?>>No</option>
        </select>
        <input type="submit" value="Go">
    </fieldset>
    <input type="hidden" name="host" value="<?php echo htmlentities($_POST['host']); ?>">
    <input type="hidden" name="user" value="<?php echo htmlentities($_POST['user']); ?>">
    <input type="hidden" name="pass" value="<?php echo htmlentities($_POST['pass']); ?>">
    <input type="hidden" name="name" value="<?php echo htmlentities($_POST['name']); ?>">
</form>
<?php

} else {

    db_connect();
    set_time_limit(0);
    $convert_to = $_POST['collate'];
    $character_set = $_POST['charset'];
    $show_alter_database = (bool) $_POST['alter_database'];
    $show_alter_table = (bool) $_POST['alter_tables'];
    $show_alter_field = (bool) $_POST['alter_fields'];
    $database = mysql_real_escape_string($_POST['name']);

    header('Content-Type: text/plain');
    echo "/* DATABASE: $_POST[name] */\n";
    echo "/* COLLATE: $convert_to */\n";
    echo "/* CHARSET: $character_set */\n";
    echo "/* ---------------------------------------- */\n";

    if ($show_alter_database) {
        $rs = mysql_query("SHOW CREATE DATABASE `$database`") or show_error(mysql_error());
        $row = mysql_fetch_assoc($rs);
        if (strpos($row['Create Database'], "CHARACTER SET $character_set") === FALSE) {
            echo "ALTER DATABASE `$database` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\n";
        }
    }
    $rs_tables = mysql_query('SHOW TABLES') or show_error(mysql_error());
    while ($row_tables = mysql_fetch_row($rs_tables)) {
        $table = mysql_real_escape_string($row_tables[0]);
        if ($show_alter_table) {
            $rs = mysql_query("SHOW CREATE TABLE `$table`") or show_error(mysql_error());
            $row = mysql_fetch_assoc($rs);
            if (strpos($row['Create Table'], "DEFAULT CHARSET=$character_set") === FALSE) {
                echo "ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\n";
            }
        }
        if ($show_alter_field) {
            $rs = mysql_query("SHOW FULL FIELDS FROM `$table`") or show_error(mysql_error());
            while ($row = mysql_fetch_assoc($rs)) {
                if ($row['Collation'] == '' || $row['Collation'] == $convert_to) {
                    continue;
                }
                $nullable = ($row['Null'] == 'YES') ? 'NULL' : 'NOT NULL';
                if ($row['Default'] === NULL && $row['Null'] == 'YES') {
                    $default = ($nullable == 'NOT NULL') ? '' : ' DEFAULT NULL';
                } elseif ($row['Default'] != '') {
                    $default = ' DEFAULT \'' . mysql_real_escape_string($row['Default']) . '\'';
                } else {
                    $default = '';
                }
                $field = mysql_real_escape_string($row['Field']);
                echo "ALTER TABLE `$table` CHANGE `$field` `$field` LONGBLOB;\n";
                echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable$default;\n";
            }
        }
    }
    echo "/* ---------------------------------------- */\n";
}
  1. You have to provide your MySQL connection information (duh)
  2. Select the desired collation, and character set
  3. Select whether to show database, table and/or field alterations
  4. The SQL code is output to the screen for review

Note: Collations and character sets are automatically retrieved from the MySQL server ūüėČ

I used to go through phpMyAdmin and do this manually on each table and field… seriously. This tool could easily save you an hour or two having to do that with a robust database schema ūüėÄ

Leave a Reply

Your email address will not be published. Required fields are marked *