Drupal : Change DB and table collation to Unicode (UTF-8)

By fiLi • Apr 27th, 2007 • Category: Drupal, Web Development

Drupal supports Unicode (UTF-8) out of the box and so Unicode should be used for all your Drupal installations. Problem is, some things you do with your Drupal installation might result in a database or a table that have non-unicode  collation.

Here are a few general tips on making sure everything is using Unicode :

Install manually, don’t use Fantastico 

One of the things that Fantastico does is install the database in the wrong collation. Create your database by yourself, make sure that the collation is set right, and then proceed with the Drupal installation.

Verify by running a Unicode change script

Some Drupal modules still have bugs of creating tables with the wrong collation. Here’s a piece of quick code you can run from your shell if you notice mixed collation in your DB. I will go table by table and change the collation to Unicode. If you don’t have shell, try a shell solution for PHP.

Change all tables to Unicode:

for table in `mysql -h<DBserver>-u<DBuser> <DBname> -p<DBpassword> -e "show tables" | grep -v Tables`; do mysql -h<DBserver>-u<DBuser> <DBname> -p<DBpassword> -e "alter table $table convert to character set utf8" ; done

Change DB to Unicode :

mysql -h<DBserver> -u<DBuser> -p<DBpassword> alter database <DBname> character set utf8;

Tagged as: ,

5 Responses »

  1. Grant on May 10th, 2007 at 4:36 am:

    There is a semi colon missing in your code

  2. Grant on May 10th, 2007 at 4:57 am:

    Sorry before the done in the for line.

  3. fiLi on May 10th, 2007 at 6:49 am:

    Oh, yeah :$

    Thanks for catching that.

  4. Grant on May 10th, 2007 at 9:43 am:

    Thankyou by the way - this still results in some character recall issues on my database i.e. GB pound signs that were previously okay must have been encoded in latin. I experienced this problem exporting from mysql 4 database to 4.1. Using mysqldump.

  5. fiLi on May 20th, 2007 at 6:27 am:

    I’m not sure this is what you mean, but the following might be helpful:
    Content search-n-replace?

    Naturally, this can be done on the dump with a big-text-files editor.

Leave a Reply