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 Hunter II Avatar

Thomas is the author of Advanced Microservices and is a prolific public speaker with a passion for reducing complex problems into simple language and diagrams. His career includes working at Fortune 50's in the Midwest, co-founding a successful startup, and everything in between.