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.

39 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 رقم شركتنا اولى شركات نقل العفش والاثاث بجدة
لدينا افضل العمالة الماهرة والمدربة على نقل العفش بدون حدوق اي خسائر للعميل كل مايهمنا هو ارضاء العملاء وكسب ثقتهم لا تترددوا في التواصل معنا
من اهم خدماتنا :

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

harish sharma said...

yowhatsapp apk

King User said...

1080p movies
4k video downloader

Jones Singh said...



download latest verion of netflix modded apk from here
tinder plus mod apk latest version

Haider Jamal Abbasi said...

good work keep working
yo Whatsapp

luckys said...

gta 4 apk

Techno Records said...

Great man really awesome article check out this:- yowhatsapp pubg mobile mod apk

Zubair Ismail said...

i am currently working on mysql database project.. Really very helpful. Thanks for this
Redbox Tv Apk

Mr Naveen said...



Mumbai to Delhi transporter
Navi Mumbai to Delhi transporter
Mumbai to Gurgaon transporter
Navi Mumbai to Gurgaon transporter
Navi Mumbai to Ghaziabad transporter
Mumbai to Ghaziabad transporter

Sharp Indonesia said...

Service Center lg
Service Center Nokia
Jasa Kursus Service HP
Service Center Vivo
Service Center Acer
Service Center Apple
Service HP
Distributor Kuota
iklan baris
Apple

mumbai said...

Transport Services Mumbai
Transport Services Mumbai
Packers and movers Mumbai
Packers and movers in Delhi
Packers and movers in Mumbai

unknown said...

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.
https://whatsopp.net/yowhatsapp-apk-download-latest-version/

Service Center iPhone said...

service
kursus
kursus
Elektronika
Bisnis
lampung
lampung
lampung
lampung

Packers Movers service said...

Transport Services Mumbai
Transport Services Mumbai
Packers and movers Mumbai
Packers and movers in Delhi
Packers and movers in Mumbai

Unknown said...

yowhatsapp

peter11 said...

Cyberflix tv
Cyberflix apk download
Cyberflix tv download here
Cyberflix tv app
Cyberflix tv
Cyberflix app
Cyberflix tv

Unknown said...

GBWhatsApp is a far more convenient and modified version of WhatsApp – available for use by everyone on the Internet. Currently, the application only operates on Android smartphones and is not supported by iOS devices.for more Updates Visit Our website

Gaurav Jain said...

icloud bypass tools

Unknown said...

Transform TV APK has a humongous line-up of your preferred motion pictures and shows. The https://trickswithus.com/ substance library is relied upon to be refreshed consistently since there are new designers behind it now

Unknown said...

MySolarCity APK Latest Version

unknow said...

I really enjoyed your blog Thanks for sharing such an informative post.
https://myseokhazana.com/
https://seosagar.in/
Indian Bookmarking list
Indian Bookmarking list
India Classified Submission List
Indian Classified List
Indian Bookmarking list
Indian Bookmarking list
India Classified Submission List
Indian Classified List
Friendship Club In India
Friendship Club In Mumbai
Friendship Club In Delhi
Friendship Club
Friendship Chandigarh

MobilePriceall said...

Thanks for sharing such a great information with us. Your Post is very unique and all information is reliable for new readers. Keep it up in future, thanks for sharing such a useful post.
Here Is Daily Latest Updated Mobile Price for Bangladeshi People Who are looking for Samsung mobile price in bangladesh to buy smartphone. I hope you guys will be like this Mobilepriceall Website. Thanks again.

Haider Jamal Abbasi (iAMHJA) said...

Hja Guru
Odin Download
Pubg Wallpaper

A.Nain said...

All free whatsapp dp download
Gym Whatsapp DP Download
Whatsapp DP for Medical Students
Happy Birthday Whatsapp Sticker
Sorry Whatsapp DP
GF BF Whatsapp DP
Punjabi Couples Whatsapp DP
Stylish Girls Whatsapp DP
Abhi and Pragya Whatsapp DP