Blog
How to change the collation of a table and its columns
2010/07/14
One of my ExpressionEngine 2 installations wouldn't accept multibyte characters (Japanese, to be exact) in the Discussion Forum module. Those characters would be displayed as question marks in thread posts. I thought that the default EE2 install created tables in Unicode (UTF-8) automatically, and that I shouldn't have any problems with multibyte characters. After a closer look at my database, I realised that the exp_forum tables were in latin_1! I had two options to fix the problem:
- Turn on the "Convert to high ASCII" option in ExpressionEngine's control panel (under Admin / Channel Administration / Global preferences). Multibyte characters would then be saved as entities in the database. For example, "あ" would be saved as
あ. This is not my favorite strategy anymore, as I would rather have foreign characters readable in my database, using the command line or phpMyAdmin. - Alter the latin_1 MySQL tables, and its columns, into UTF-8. This can be done with phpMyAdmin, but the following SQL is more expedient (just repeat for each affected table):
ALTER TABLE exp_forum_posts CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
After this, I was able to post with multibyte characters, which were saved as is in the database under the right utf8_general_ci-collated column.
