Mysql – Setting default charset/collation for MySQL database

Setting the default character set and collation is completely safe. This will ensure that future DDL changes will use utf8, but will not affect existing columns that use latin1. Those will have to be converted to utf8. Make a backup of the data, because there are risks of data corruption (one example).

And for completeness, I will point out that adding the changes in the my.cnf will require a server restart.

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