Moving UTF-8 encoded data from latin1 table to another latin1 table..

20 May 2008 9:34 (Edited: 20 May 2008 9:34)

Encountered weird problem today, when moving data from an ancient Wordpress installation to AfterDawn's own table structure. Apparently, for whatever reason, WP's tables were set by default to be in latin1 encoding, but the data that WP inserted to those tables was in UTF8 format.

Now, our data -- and site outputting -- is in latin1, so moving that data straight from table to table simply cocks up, as then all special characters are shown incorrectly on site.

Googled stuff and found couple of solutions, but these were related to a situation where people wanted simply to change the WP's tables to be in UTF-8 format (as the data was already in that format). These solutions typically included changing text columns to binary format (BINARY or BLOB) so that they wouldn't get converted by MySQL when changing the table's character encoding.

Now, figured out the solution to this: Lets simply follow the instructions first, by converting the text columns to BLOBs, then converting the table to be truly UTF8, then convert the BLOBs back to text columns and finally to convert the table's character encoding to latin1 (which then triggers the automatic conversion of text data by MySQL, exactly the thing we avoided when we converted the columns to BLOBs).

So, basically this should do the trick:

alter table wp_posts_copy modify post_title blob;
alter table wp_posts_copy modify post_content blob;
...etc, all text-based columns...

alter table wp_posts_copy charset=utf8;

alter table wp_posts_copy modify post_title text character set utf8;
alter table wp_posts_copy modify post_content text character set utf8;
...etc, all text-based columns...

alter table wp_posts_copy charset=latin1;

alter table wp_posts_copy modify post_title text character set latin1;
alter table wp_posts_copy modify post_content text character set latin1;
...etc, all text-based columns...

And now you can run the script or SQL statement that copies the data from 'wp_posts_copy' table to your target table that is in latin1 format.

Tags: alter  conversion  convert  latin1  mysql  table  utf-8  utf8 

List of my blog entries

• Two days of UTF-8 happiness (23 Jan 2009)
• 10 years as a web developer (01 Apr 2008)
• Sharding a forum database (29 Aug 2007)
• Over 100,000 suspended accounts... (12 Aug 2007)
• Holidays and weather (08 Aug 2007)
• Minor tweaks to profile pages (12 Jan 2007)
• "My links" RSS feed added (15 Aug 2006)
• Opera 9 and form enctype silliness (27 Jul 2006)
• Hectic spring is almost over... (30 Apr 2006)
• My personal thoughts about the new copyright law (27 Dec 2005)
• Added keywords, etc.. (20 Dec 2005)
• Couple of bugs fixed (19 Dec 2005)
• Net makes everything mobile? Not so... (16 Dec 2005)
• Profile pages launched as "public beta" (14 Dec 2005)
• Profile pages getting ready... (05 Dec 2005)
• Removal of pop-unders from AfterDawn.com for registered users (29 Nov 2005)

User comments

• by Ketola @ 20 May 2008 9:37

  Hooray for ugly.. I mean eleganthacks =)

Post your comment

In order to post your comments here, you need be logged in to our system. Simply follow this link in order to login and to post your comments here.