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.

16 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?)

Anonymous 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

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

تاج لتنظيف الكنب والتنظيف بالبخار said...

شركة شاهه المثالية أفضل شركة تنظيف بالجبيل بصفة خاصة وفي المنطقة الشرقية بصفة عامة فهي الشركة الاولى في مجال تنظيف الشقق والبيوت والفلل والقصور لا نحرص دائما على اكتساب العملاء وذلك عن طريق القيام بمهام عملنا على اكمل وجه فنحن لدينا افضل العمالة الماهرة والمدربة على اكمل وجه لاتمام عملية التنظيف بشكل مثالي وبدون اي خسائر قد تلحق بالعملاء .
اذا كنتم من ممن يهتمون بخدمات التنظيف سواء يالبخار او التنظيف الجاف فلا تترددوا في التواصل معنا نحن شركة شاهه المثالية للتنظيف بالدمام ومن أهم خدماتنا :
شركة تنظيف بالجبيل
شركة تنظيف فلل بالجبيل
شركة تنظيف منازل بالجبيل
شركة تنظيف شقق بالجبيل
شركة تنظيف موكيت بالجبيل
شركة تنظيف سجاد بالجبيل
شركة تنظيف مجالس بالجبيل
شركة تنظيف بيوت بالجبيل


تاج لتنظيف الكنب والتنظيف بالبخار said...

شركة طيور الدمام لمكافحة الحشرات
شركة ماسة لمكافحة الحشرات بالاحساء
شركة واثق لمكافحة الحشرات
شركة المثالية للتنظيف بالقطيف

شركة المثالية للتنظيف بالدمام

شركة المثالية للتنظيف بالخبر

شركة المثالية للتنظيف بالجبيل

افضل شركة مكافحة الحمام بالدمام
شركة ورس لمكافحة الحشرات بالاحساء
شركة ورس لمكافحة الحشرات

شركة ورس بالاحساء

تاج لتنظيف الكنب والتنظيف بالبخار said...

شركة واحة الخليج بجدة ومكة ورابغ والطائف الشركة الأولى والأفضل في خدمات نقل العفش بجدة اذا كنت تريد الحفاظ على الاثاث الخاص بك فلا بد من ان تتصل على الفور على الرقم التالي 0555583146 رقم شركتنا اولى شركات نقل العفش والاثاث بجدة
لدينا افضل العمالة الماهرة والمدربة على نقل العفش بدون حدوق اي خسائر للعميل كل مايهمنا هو ارضاء العملاء وكسب ثقتهم لا تترددوا في التواصل معنا
من اهم خدماتنا :

شركات نقل عفش برابغ
نقل عفش من رابغ إلى جدة
افضل شركة نقل عفش برابغ
افضل سيارات نقل عفش برابغ
شركة نقل عفش رابغ
افضل شركة نقل اثاث رخيص برابغ
شركات نقل عفش برابغ