How to make MySQL handle UTF-8 properly

Question

One of the responses to a question I asked yesterday suggested that I should make sure my database can handle UTF-8 characters correctly. Anyone know how I can do this with MySQL?

Thanks!

Ben

Answer 1

mysql 4.1 and above has a default character set of UTF-8. you can verify this in your my.cnf file, remember to set both client and server (default-character-set and character-set-server).

if you have existing data that you wish to convert to UTF-8, dump your database, and import it back as UTF-8 making sure:

• use SET NAMES utf8 before you query/insert into the database
• use DEFAULT CHARSET=utf8 when creating new tables
• at this point your mysql client and server should be in UTF-8 (see my.cnf). remember any languages you use (such as PHP) must be utf-8 as well. some versions of PHP will use their own mysql client library, which may not be UTF-8 aware.

if you do want to migrate existing data remember to backup first! lots of weird choping of data can happen when things don't go as planned!

some resources:

• complete UTF-8 migration (cdbaby.com)
• article on UTF-8 readiness of php functions (note some of this information is outdated)

Answer 2

SET NAMES UTF8

This is does the trick

Answer 3

to make this 'permanent', in my.cf:

[client]
default-character-set=utf8
[mysqld]
character-set-server = utf8

to check, go to the client and show some variables:

SHOW VARIABLES LIKE 'character_set%';

verify that they're all utf8, except ..._filesystem, which should be binary and ..._dir, that points somewhere in the MySQL installation.

Answer 4

The charset is a property of the database (default) and the table. You can have a look (MySQL commands):

show create database foo; 
> CREATE DATABASE  `foo`.`foo` /*!40100 DEFAULT CHARACTER SET latin1 */

show create table foo.bar;
> lots of stuff ending with
> ) ENGINE=InnoDB AUTO_INCREMENT=252 DEFAULT CHARSET=latin1

In other words; it's quite easy to check your database charset or change it:

ALTER TABLE `foo`.`bar` CHARACTER SET utf8;

Answer 5

These tips on MySQL and UTF-8 may be helpful. Unfortunately, they don't constitute a full solution, just common gotchas.