Nicolasbottari.com

How to change the collation of a table and its columns


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):
    ALTER TABLE expforumposts CONVERT TO CHARACTER SET utf8 COLLATE utf8generalci;

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.