How do I change a MySQL table to UTF-8?

Question

I know there are many settings for a language for a table and a database.

I already created the database. I believe when I created it, it was default/LATIN. I want to change everything-I mean...both the table and the database, to UTF-8.

How can I do that? thanks.

Answer 1

Have a look at Using alter command to change character set.

Another useful link: http://dev.mysql.com/doc/refman/5.0/en/charset-table.html

The general form is

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

and for a specific column in a table

ALTER TABLE column COLLATE utf8_general_ci

---

• MySQL’s character sets and collations demystified
• MySQL Forums :: Change Collation

Answer 2

Add to your my.cnf this:

[mysqld]
character-set-server=utf8
default-collation=utf8_unicode_ci

And restart mysqld deamon.

ADDED:

ALTER DATABASE your_base_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;

and my.cnf

SET collation_connection     = utf8_unicode_ci;
SET character_set_results    = utf8;
SET character_set_connection = utf8;
SET character_set_client     = utf8;

Answer 3

aioobe's answer tells how to change the character set of a database, table or column. You should keep in mind that

• setting the character set for a table just specifies the default character set for new columns in that table. It doesn't change the character set for preexisting columns; you have to do those columns individually, OR if you want to change every single string-type column in the table to the same character set there's a command you can use to do that: "alter table ... convert to character set" ( http://dev.mysql.com/doc/refman/5.1/en/alter-table.html )

• if you already have data that is stored mis-encoded in a column, then using "alter table ... modify" to change the column will not quite solve the problem. For example, if you're been storing UTF-8 data in a Latin1 column and you change the character set directly from Latin1 to UTF-8, it'll still be mis-encoded afterwards. This can be worked around by converting from Latin-1 to UTF-8 via binary.