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

Written on April 27, 2007 by 7 Comments

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;

Tags: , 
Filed Under: Drupal

Related Posts

Subscribe to Updates

Subscribe to My Blog via RSS!

Follow @filination on Twitter

Or, receive updates via email:

  • Grant

    There is a semi colon missing in your code

  • Grant

    Sorry before the done in the for line.

  • fiLi

    Oh, yeah :$

    Thanks for catching that.

  • Grant

    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.

  • fiLi

    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.

  • http://iguide.travel iGuide

    Great! Can this also be done with the ALTER TABLE syntax?

  • Grant

    That is using the Alter table syntax! So yes.

    If you have no data to worry about, you can do a mysqldump, change the collation in the text file, and then upload the dumpfile.

    Comment 5 suggestion , looks overblown but maybe an easy solution if you are a windows user.

    Otherwise you can do a mysqldump, change the table defs collation, and use recode or iconv unix utilities on the dump file. I think I had success with iconv in the past.

    Make sure you keep a backup!