Digg’s MySQL Internationalisation Gotcha

[savant]

Digg’s MySQL Internationalisation Gotcha

Recently at Digg we started doing internationalisation efforts. Our database was storing data in latin1 character set although PHP was using the UTF8 character set. That limited our language support, so we decided to convert the DB to UTF8.

MySQL has a “charset=” option at the end of the CREATE TABLE, so we ran through all our tables doing “alter table charset=utf8.” This worked, and we were pleased as punch.

But then examining a particular table, I noticed:

CREATE TABLE (
id integer not null default 0,
name varchar(32) character set latin1 default NULL,
…
) charset=utf8;

Every column that actually stored text specified the old “latin1″ encoding. It turns out in MySQL, when it comes time to change your character set, there’s a special ALTER command that will also change each of the columns:

alter table convert to character set utf8;

This changes all the text columns to the new character set as well.

Digg on,
–
timeless

Maybe some one could try this and see what effects it has