MySQL Latin1 to UTF8 mismatch conversion

Find an article
Feb 21
Published by in · Leave your thoughts
( words)
Warning! There was an error loading some of the images for this post.

I recently had an extremely painful experience migrating data from one software to another. Annoyingly the data in the original software seemed to be a bit confused with the data being stored as latin1 and displayed as UTF8. When I migrated the data it obviously converted ‘as is’ and hence displayed wrong in the second software. After a number of hours researching I stumbled across a clever post that seemed promising:

From what you describe, it seems you have UTF-8 data that was originally stored as Latin-1 and then not converted correctly to UTF-8. The data is recoverable; you’ll need a MySQL function like
convert(cast(convert(name using latin1) as binary) using utf8)
It’s possible that you may need to omit the inner conversion, depending on how the data was altered during the encoding conversion.

I hope this helps anyone else who has a similar issue.

Leave a Reply

Your email address will not be published.