Wednesday, January 25, 2012

How to change the collation for all tables in a MySQL database to UTF-8?

In response to How to change the collation for all tables in a MySQL database to UTF-8? where they only changed the default collation for any column which will be created in the future, I post the script which also changes the collation of each FIELD in the database. I've used it for http://www.mantisbt.org/.

Beware (!) that if your database already contains data in the incorrect encoding this script may corrupt the data. It should be safe to use it for English though.

<?php
require '../config_inc.php';
$db = mysql_connect($g_db_hostname, $g_db_username, $g_db_password);
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db($g_database_name);
$result=mysql_query('show tables');
while ($tables = mysql_fetch_array($result)) {
    foreach ($tables as $value) {
        echo '<b>'.$value.'</b><br />';
        mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
        alterTable($value);
    }
}

function alterTable($table) {
    $result=mysql_query('show columns from '.$table);
    while ($fields = mysql_fetch_assoc($result)) {
        $fieldName = $fields['Field'];
        $fieldType = $fields['Type'];
        if (substr($fieldType, 0, 7) == 'varchar') {
            echo $table.'.'.$fieldName.'<br />';
            mysql_query("ALTER TABLE $table CHANGE $fieldName $fieldName $fieldType COLLATE utf8_general_ci");
            echo mysql_error();
            //print '<pre>'.print_r($fields, true).'</pre>';
        }
    }
}

echo "The collation of your database has been successfully changed!";


No comments: