Changing the collation of all tables in a mysql database

If you're reading this, you're probably wanting to change the collation of your mysql database to utf8_general_ci in order to support non-English characters.

There seems to be lots of methods out there for doing this, probably none of which are as quick and simple as my script below :cool:

This script changes the collation of the mysql database, every table and every column that has an existing collation.

Cut and paste the script below into a file called (say) change_collation.php on your server. Set the connection properties at the top of the script, then call the script via your browser.

*** TAKE A BACKUP BEFORE RUNNING THIS SCRIPT ***

If you don't take a complete backup before running this script then you are extremely foolish and don't come crying to me when all your data is gone. This script worked fine for me but changing collations might have all sorts of odd effects that I can't predict on your system. Almost certainly everything will be fine, especially if you have a backup !!


<?php

// Configuration Section
$server = 'localhost'; //probably localhost but change if required
$username = 'your_username';
$password = 'your_password';
$database = 'your_database';
$new_charset = 'utf8'; // change to the required character set - you're probably changing to utf8 ?
$new_collation = 'utf8_general_ci'; // change to the required collation - you're probably changing to utf8_general_ci ?

// 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 utf8 COLLATE utf8_general_ci");

// 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 collation of $table to $new_collation<br/>";
}
print '<br/>The collation of your database has been successfully changed!<br/>';
?>