UTF-8, ColdFusion and MySQL

I've just been setting up my first true UTF-8 application with MySQL and just wanted to share the basic setup incase you ever need to.

The Database
First you need to use MySQL version 4.1+ as this offers two new character set for storing unicode data
  • ucs2, the UCS-2 Unicode character set.
  • utf8, the UTF8 encoding of the Unicode character set.
When you create your table you need to state the following character set "CHARACTER SET utf8" at the end of the SQL string.
CREATE TABLE `person` (
\n   `name` varchar(100) NOT NULL,
\n   `email` varchar(100) NOT NULL,
\n   PRIMARY KEY (`name`),
\n   UNIQUE KEY `name` (`name`)
\n   ) TYPE=InnoDB "CHARACTER SET utf8;


The Datasource
Goto "Advanced Settings" and the connection string: "useUnicode=true&characterEncoding=utf-8"

The HTML page
In your Application.cfm place:
<cfset setEncoding("form","utf-8")>
\n   <cfset setEncoding("url","utf-8")>
\n   <cfcontent type="text/html; charset=utf-8">
Then on everypage that makes a DB call place:
<cfprocessingdirective pageEncoding="utf-8">
And before you think it - no this cannot go into the Application.cfm, or be <cfinclud>'ed
For more information goto http://mysecretbase.com/ColdFusion_and_Unicode.cfm. Matt Robertson explains in more details about this set up, including setting the HTML page as well.

And thats the bare bones of it.

I just want to say thank you to Paul Hastings for helping me on CF-TALK