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:

default-character-set=utf8


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.


===== latin1ToUTF8.sh

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 ?!

yk.

12 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:

http://blog.firebright.com/2006/09/12/mysql-charset-latin1-to-utf8-and-back-again-seeking-xen-with-character-encoding/

where he rates my post a mere 4 of 10.

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

http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html


yk.

Alf said...

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

alter table TABLE_NAME convert to character set utf8;

EoD said...

Try:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;
(http://dev.mysql.com/doc/refman/5.0/en/alter-table.html)

eod said...

Oh, didn't mention alf.

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

db2 said...

Nice info
Regards,
Jack
http://db2examples.googlepages.com/

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`)

2. Use INFORMATION_SCHEMA.COLUMNS to
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: http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html

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: http://blog.boldlab.com/en/myslq-how-to-transform-character-set .

I had to do some transforming to binary.

thiyagi said...

thanks