First off, if you’re about to convert a MT database from iso-8859-1 to UTF-8 or export and reimport an exported MT3 MySQL database — this information is probably for you.
With that said, Six Apart has supported UTF-8 in their blogging platform Movable Type for a long time so I’ve kind of taken it for granted and never thought much about it. That is, until I tried exporting and then reimporting the database. As much as I tried (and I did try!) I could not get a proper export of the Movable Type tables from MySQL.
When it comes to international characters, or actually, any character that’s not within the 7-bit ASCII set, UTF-8 is using two or more bytes to store an individual character. Any of these bytes should be visible in the extended ASCII character set. For example the swedish character å (å) will be displayed as Ã¥ in ISO-8859-1 and consists of bytes 0xc3a5.
PhpMyAdmin, a common front end for MySQL, will show the above as ‘å’ if set to display UTF-8 but will show it as ‘Ã¥’ if set to display ISO-8859-1. Now, this is of course something that should’ve alerted me towards the solution of the problem because regardless of how I configured phpMyAdmin or my tables, I could not get it to display any swedish character properly. And if I edited a post by hand in phpMyAdmin, Movable Type would show it equally garbled.
Even stranger was that when I exported the MT tables, an ‘å’ would become, not ‘Ã¥’ as expected, but instead ‘Ã¥’; the ISO-8859-1 representation of the four byte UTF-8 string ‘Ã¥’ (0xc383c2a5). At this point, don’t do like I did and assume it must be a problem with the export. Most definitely do not write a script to correct the above. The thing is, even though Movable Type supports UTF-8, it’s using components that does not. So until that’s taken care of, they encode the data twice and any multibyte UTF-8 character will be twice as long, bytewise, in your database and your export file.
All you might want to do before importing the database is to add the following in the top of the file:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET NAMES utf8;
That’s it, now import.
