Mysql – Changing collation type database-wide in MySQL

alter-tablecollationMySQL

How do I retroactively update the collation type database wide without dropping and recreating it?

I accidentally created a database without specifying UTF8 as the default collation type. I then went and populated it with several gigs of data.

I know the ALTER commands to do this for the database/tables/columns individually…but I have dozens of tables, each with dozens of columns. Is there a simple non-tedious way of converting all these to use UTF8 collation?

Best Answer

I found an answer on this website: http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

I cleaned up the code to print out better from a linux shell and added the $new_charset and $new_collation variables to the alter database statement.

vi change_collation_everything.php

<?php

// Configuration Section
$server = 'localhost'; //probably localhost but change if required
$username = 'root';
$password = 'password';
$database = 'database';
$new_charset = 'latin1'; 
$new_collation = 'latin1_general_cs';

// Connect to database
$db = mysql_connect($server, $username, $password); if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database); if (!$select_db) die("could not select $database: ".mysql_error());

// change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

// Loop through all tables changing collation
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
    $table = $tables[0];
    mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

    // loop through each column changing collation
    $columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
    while($cols = mysql_fetch_array($columns)) {
        $column = $cols[0];
        $type = $cols[1];
        mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
        print "changed $table collation of $column to $new_collation\n";
    }

    print "changed collation of $table to $new_collation\n";
}
print 'The collation of your database has been successfully changed!\n';
?>

I tested the answer on my own database changing from charset latin7 to latin1 and latin7_general_cs to latin1_general_cs for the collation.

I chose _cs instead of _ci for the collation because if you choose _ci which is the default collation for charset latin_1, the collation no longer shows up in the show create table tablename column output.

Try it out

$> php change_collation_everything.php

You can see all of the available charsets and their corresponding collations with the show collation sql query

Related Question