Wednesday, March 29, 2006

MySQL: CHARSET from latin1 to utf8

A website im supporting needs to have multilingual characters. The default character set for MySQL is latin1. This unfortunately will not support Chinese nor other wierd multibyte characters.

It will quietly support them, but returns gibberish and will cause frustration all round.

After digging around, the best character set to use is UTF8.
To set the default charset for the server, the my.cfg/my.ini file has to be modified:


Unfortunately, once a database and their tables have been defined as latin1, they remain as latin1 unless you run this for each database:

alter database mydatabase charset=utf8;

and for each table:
alter table mytable charset=utf8;

and for each varchar/char type column:
alter table mytable alter column mycol charset=utf8;

and repeat ad infinitum....

This is rather tedious and boring, so there should be a better way. And that is to dump out the sql, change the charset and dump it back in. Here is the script.


echo Script to convert MySQL latin1 charsets to utf8.
echo Usage: $0 dbname
echo 060329 yky Created.

echo Dumping out $1 database
mysqldump --add-drop-table $1 > db.sql

mydate=`date +%y%m%d`
echo Making a backup
mkdir bak &> /dev/null
cp db.sql bak/$1.$mydate.sql

echo String replacing latin1 with utf8
cat db.sql | replace CHARSET=latin1 CHARSET=utf8 > db2.sql

echo Pumping back $1 into database
mysql $1 < db2.sql

echo Changing db charset to utf8
mysql $1 -e "alter database $1 charset=utf8;"

echo $1 Done!


There must be a better way ?!


13 lewsers:

Simos said...

It's better to use "iconv" to convert the existing database to UTF-8. It's possible that the database already contains bad characters, so these have to be removed.
Once you have a sane UTF-8 database, you can import.

iconv -f utf-8 -t utf-8 < olddatabase.sql

To check if the database is sane.

Yoon Kit said...

Here are more resources:

where he rates my post a mere 4 of 10.

And rightly so, because this one is the ultimate resource:


Alf said...

Here's a better worked for me in MySQL 5, not sure about older versions:

alter table TABLE_NAME convert to character set utf8;

EoD said...


eod said...

Oh, didn't mention alf.

Converting from latin1 to utf8 caused some trouble with length of primary key (max 1000 byte) (since utf8 needs more space?)

Anonymous said...

Nice info

Saverio M. said...

Hello, there is no need to manually write all the queries.

After changing the cset for the database, you just:

1. Use INFORMATION_SCHEMA.TABLES to create a script for changing the cset for the tables, where you need it (you can detect them using `table_collation`)

create a script for changing the cset for the columns, using MODIFY column_name/character_set/data_type/char_lengt[where needed]/[if] null, possibly using a GROUP_CONCAT for a shorter script.

Can't disclose the script because I did it for work!

HÃ¥kan said...

You saved my life man! Thanks!

Chris said...

See Vladislav Rastrusny' script that use MySQL schema at the bottom of this page:

Humberto said...

Thank you Chris, this script fix my problem

Felipe said...

I tried what you said but it didn't work, I did something else instead, you can read it here: .

I had to do some transforming to binary.

thiyagi said...


MIBM Global said...

I really like it this kind of information..........Great blog post and really helpful...... and your blog are very interesting and inspiring.

Best distance mba in india