Convert a MySQL DB from latin1 to UTF8
I had a latin1 collated MySQL 4.1 db that contained both English and Japanese text. I dumped the data and then tried to migrate it into my new MySQL 5.0.26 installation. Of course being a bit lazy I did not take the time to check things out properly and was not surprised to find that all Japanese text had become mojibake. I set off on a mission to fix the problem.
I had two options. Stick with latin1 or move to utf8. I chose to move to utf8 as the front end of my website is all in utf8 so making the whole thing utf8 from front to back would make sense. Here is how I did it:
Dumped the original db with the following command:
mysqldump -u root -p --opt --default-character-set=latin1 --skip-set-charset DBNAME > DBNAME<!--
Then using sed I changed all occurrences of the word latin1 to utf8:
sed -e 's/latin1/utf8/g' -i ./DBNAME.sql
From here I then created the new database and then imported the dumpfile.
mysql -p -e "create database DBNAME"
mysql -p --default-character-set=utf8 DBNAME < DBNAME.sql<!--
And that's it.
Regards, Bawdo
- bawdo2001's blog
- Login or register to post comments
Comments
Thanks
Thanks for this ideas, I had exactly the same problems when switching to SuSE 10.2 with my web server. It can be tricky to convert old MySQL databases to MySQL 5.x. Regards Axel - Handy mit Vertrag
Ensure your front end is talking utf-8
When converting your backend DB from latin1 to UTF-8 you may also need to ensure the front end connects to the DB using UTF-8.
This should happen if you have the appropriate settings made in you php.ini and my.cnf configuration files. However, on one of my sites things were not working out as they should have.
To fix things I issued the following query to the database after the initial mysql_connect: "SET NAMES 'utf8'
This page has more details on this command.
Regards, Bawdo2001
and what settings are needed
and what settings are needed for php.