How to change the collation of a table and its columns
Posted on July 14, 2010
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 expforum tables were in
latin1! 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 latin1 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):
forumposts CONVERT TO CHARACTER SET utf8 COLLATE utf8generalci;
ALTER TABLE exp
After this, I was able to post with multibyte characters, which were saved as is in the database under the right utf8general_ci-collated column.