evil.cz
Linux do každé rodiny, linux do každé kanceláře!

HOW-TO: MySQL - UTF8 strings saved as Latin1 solution

MySQL, UTF8 database, UTF8 table, UTF8 field ... but odd characters inside. How to convert "á" to "á" etc...

Problem description:
We have a MySQL database completely done with UTF8. Default character set for database is UTF8, defaults for the table is UTF8 even the row is exactly defined as UTF8. It's ok and working fine with the (php) application, but data stored in database look strange and characters like "á" are shown instead of "á". Data transfer and/or dump just duplicates odd characters and the data cannot be used in any other application (or newer version of the application used before).

Answer to "Why it happend?":
Bad code in application. Data are stored in UTF8 columns but they are encoded (probably) as latin1.

Sources/links for more info:

How-to correct the problem:
Export data from database as LATIN1 instead of default UTF8. Then re-import them back as UTF8. No extra conversion is needed. See links above for more information. Don't play this game on production database, make a backup before playing ;-)

No votes yet