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.

55 lewsers:

Anonymous 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 Mikula 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;

Anonymous said...

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

Anonymous 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/

Jim Corney 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!

dun 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

Unknown 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

Ajay yadav 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

Unknown said...

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


Unknown said...

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

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

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

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

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

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

Unknown said...

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

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

harish sharma said...

yowhatsapp apk

Unknown 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 (iAMHJA) said...

good work keep working
yo Whatsapp

luckys said...

gta 4 apk

Unknown 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

Anonymous 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/

Nino Nurmadi 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...
This comment has been removed by the author.
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

Unknown 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

MobilePriceall said...

Arijit Singh Love Songs
, Breakup Mashup 2018
, Punjabi Videos 2018
, BollyWood Videos 2018
, Exclusive English Songs
, Honey Singh All Videos
, Cars And Bikes Stunts Videos

parvina said...

Your article is very helpful.You can visit my website:apk editor pro download free

Lily said...

Its really awesome try more : nba apk download

ghostus said...

iOS devices like iPhone and iPad online and offline. So, if you want to enjoy GTA 5 anywhere without internet then you need to download GTA V Apk portable version.

Unknown said...

Awesome And Excellent Article Thanks For Sharing.If users Want To Donwload FMWhatsApp click Here To Download



Minecraft PE Apk

Dia Singh said...

Awesome post! Such a very informative article, thanks for sharing with us. Contact us if are you looking logistics companies in Delhi, here you will find best service for logistics.
Logistics Company
Logistics Company in Delhi

takelarge789 said...

the tutu helper is the one of the best
ios,android App tutuapp download apk store to get the tons of free app and game. Here the latest version of TutuApp of free.

ellaliamop said...


the WhatsApp Plus APK is one of the best mod app for official WhatsApp Plus Download for free, get unlimited features of whatsapp plus for free. Here Click to download the latest version whatsapp plus apk.

Unknown said...

The [url=https://tutuapp.download/]Tutu App[/url] download is the best app to download latest app

and Apk for free so use now to [url=https://tutuapp.download/tutuapp-ios/]TutuApp ios[/url]free

App.

Unknown said...


The Tutu App download is the best app to download latest

app and Apk for free so use now to TutuApp ios

free App.

panda helper apk said...


the panda helper apk is the best app to download latest app for free so download now panda app to unlock a many feature now download now.

sare said...

you can download best app for your mobile download unlimited games and apps tweakbox for android

mohsin fida said...

Spotify APK Download now

Unknown said...

americanexpresscomconfirmcard.live/ read reviews.

Unknown said...

Best guidaNCE ABOUT https://krogerfeedbackz.info/

Unknown said...

republic day 2020 wallpapers
republic day 2020 wishes
republic day images
republic day 2020 pictures

26 January 2020 Images