Make mysql databases default to UTF8

By default MySQL databases are created with one of the latin charsets – as if globalization never happened – and just asking for future internationalization and charset issues. UTF8 is the generally accepted way forward (for now) and there are few occasions that it isn’t the best charset to use.

Ideally, some sort of framework config system will take care of this but otherwise for existing MySQL databases, you can find out their current charset by entering the MySQL terminal and typing:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "myDatabase";

The problem is changing the character set of an in-use databases can be troublesome and you have to remember to specify UTF8 as the charset every time you create a new database. Also many database tools (e.g. PHP’s Doctrine) do not currently support creating a database with a non-default charset.

The best solution is to set the default charset to UTF8 straight after installing mysql and avoid the problems before the occur. This can be done simply by modifying /etc/mysql/my.cnf (note my.conf may be located elsewhere) and adding the following lines to the [mysqld] section:

collation-server = utf8_general_ci
character-set-server = utf8

Then restart the MySQL service:

sudo service mysql restart

Now new databases will now be created using the UTF8 character by default.

Advertisements

One thought on “Make mysql databases default to UTF8”

Leave a Reply