Changing MySQL Default Character Sets

We've recently begun building databases which require multilingual support. What this means is that the default MySQL Collation of latin1_swedish_ci just isn't cutting it anymore. What we needed to do is enable Unicode support in the text tables (not to mention language management, but that's a whole different story).

Here's what you do to change your default character sets in MySQL. Open your my.ini configuration file for MySQL, and located the [mysqld] block. After that your going to add the following line:

[mysqld]
default-character-set=utf8

Once you've changed the file and saved it, you need to restart your MySQL server.

Now, say that you've already got all of your databases in latin1_swedish_ci or some other character set. To update those, run the following script in PHP. This script goes through and updates all of your tables and sets the defaults for the specified database:

<?php
$dbname = 'database_name';
mysql_connect('localhost', 'root', '');
mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
$res = mysql_query("SHOW TABLES FROM `$dbname`");
while($row = mysql_fetch_row($res)) {
 $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
 mysql_query($query);
 $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
 mysql_query($query);
}

The new character set is utf8_general_ci, not utf8_unicode_ci. The difference between these two options is that the prior is faster, and all accented A characters will be treated as an A when being sorted (click their names to view technical specifics). The latter allows for proper sorting of accented characters but with a performance cost.

Thomas has contributed to dozens of enterprise Node.js services and has worked for a company dedicated to securing Node.js. He has spoken at several conferences on Node.js and JavaScript and is an O'Reilly published author.