In a post about Ruby and Unicode<\/a> a while back, I mentioned the page at the Rails wiki called How To Use Unicode Strings in Rails<\/a>. (Btw, check out Why the Lucky Stiff\u2019s response to my post, some useful code there<\/a>.)<\/p>\n It turns out that there were a few more mostly MySQL-specific steps involved in getting Unicode to work correctly with Rails<\/span>. So I thought I\u2019d describe all the steps we<\/a> went through to get it set up in one place. This has only been tested with MySQL 4.1.<\/p>\n You need to explicity tell MySQL that you want your tables to be encoded in UTF-8. Here\u2019s a sample table with 3 columns, The line Bummer, that.<\/p>\n In any case, then add the (I wonder if there is some way to set this as a default, without adding the line to the DDL<\/acronym> for every table?)<\/p>\n This is also described at How To Use Unicode Strings in Rails<\/a>.<\/p>\n The previous steps were enough to get Unicode showing up in a little test app I generated with To test it, I pasted in some sample text in various languages<\/a>. It worked okay for text containing only the characters found in ASCII or latin1, but among other characters there were weird cases of random characters being removed or added. The problem seemed not to have anything to do with the script (i.e., the Unicode block). For instance, in an Esperanto text, \u201c\u0109\u201d (U+0109 LATIN SMALL LETTER C WITH CIRCUMFLEX) came out fine, but \u201c\u011d\u201d (U+011D LATIN SMALL LETTER G WITH CIRCUMFLEX) was borked. Go figure.<\/p>\n It took some digging to find the next bit \u2014 thanks to Ben Jackson of INCOMUM Design & Conceito<\/a> for getting the straight story on the Rails list<\/a>. The solution is\u2026<\/p>\n It came down to a MySQL configuration option: You have to tell MySQL to So, here\u2019s our (In case you\u2019re wondering, yes, you can have more than one UPDATED AGAIN<\/span> Argh, I forgot to remove the <\/p>\n Anyway, now our Rails installation seems to handle (almost) any nutty writing system<\/a> that we throw at it.<\/p>\n I\u2019m not sure these are all the Right Way\u2122, I\u2019m just saying it\u2019s worked for us (so far).<\/p>\n For one thing, it seems like it might make sense to just go ahead and set the default character set and collation<\/a> for MySQL to UTF-8, independently of any Rails stuff at all. Aren\u2019t all character sets supposed to be slouching toward Unicode, anyway? But that sounds like a rather apocalyptic measure, for some reason\u2026 I guess I\u2019ll hold off on the rapture of the character sets.<\/p>\n Collation is it\u2019s own topic \u2014 but then we\u2019ve not gotten to sorting anything yet. Here\u2019s a recommendation for building the tables<\/a> with:<\/p>\n To set the collation order. Wading through the MySQL docs on that is next on the agenda.<\/p>\n<\/div>\nIn MySQL: Set the Encoding when you Create Tables<\/h3>\n
id<\/code>, foo<\/code>, and bar<\/code>:<\/p>\ncreate table samples (\r\nid int not null auto_increment,\r\nfoo varchar(100) not null,\r\nbar text not null,\r\nprimary key (id)\r\n) Type=MyISAM CHARACTER SET utf8;<\/pre>\n
Type=MyISAM CHARACTER SET utf8;<\/code><\/span> is where the action is. The table type has to be MyISAM, not innoDB, because unfortunately innoDB tables don\u2019t support full text searching of UTF-8 encoded content (details here<\/a>). Apparently innoDB tables are more flexible in general<\/a>, but if full-text search is crucial for you, you\u2019ll have to go with MyISAM.<\/p>\nCHARACTER SET utf8<\/code><\/span> directive, as shown.<\/p>\nSet the \u201ccharset\u201d and \u201cContent-type\u201d in the Application Controller<\/h3>\n
class ApplicationController < ActionController::Base\r\nbefore_filter :set_charset\r\n\r\ndef set_charset\r\n@headers[\"Content-Type\"] = \"text\/html; charset=utf-8\"\r\nend\r\nend<\/pre>\n
scaffold<\/code> (the app just consisted of an input field and a textarea).<\/p>\nTell Rails to tell MySQL to Use UTF-8. (Got that?)<\/h3>\n
SET NAMES UTF8<\/code><\/span>, as DHH<\/a> pointed out in the previous link. You can either do it in the source to ActiveRecord, in mysql_adapter.rb<\/code>, or you can just make the change in your own application. We chose the latter route.<\/p>\napp\/controllers\/application.rb<\/code> as it stands:<\/p>\nclass ApplicationController < ActionController::Base\r\nbefore_filter :set_charset\r\nbefore_filter :configure_charsets\r\n\r\ndef set_charset\r\n@headers[\"Content-Type\"] = \"text\/html; charset=utf-8\"\r\nend\r\n\r\ndef configure_charsets\r\n@response.headers[\"Content-Type\"] = \"text\/html; charset=utf-8\"\r\n# Set connection charset. MySQL 4.0 doesn't support this so it\r\n# will throw an error, MySQL 4.1 needs this\r\nsuppress(ActiveRecord::StatementInvalid) do\r\nActiveRecord::Base.connection.execute 'SET NAMES UTF8'\r\nend\r\nend\r\nend<\/pre>\n
before_filter<\/code>.)<\/p>\n![\":)\"](\"http:\/\/ruphus.com\/blog\/wp-images\/smilies\/icon_smile.gif\"){kind=icon}
<\/p>\nclass ApplicationController < ActionController::Base\r\nbefore_filter :configure_charsets\r\n\r\ndef configure_charsets\r\n@response.headers[\"Content-Type\"] = \"text\/html; charset=utf-8\"\r\n# Set connection charset. MySQL 4.0 doesn't support this so it\r\n# will throw an error, MySQL 4.1 needs this\r\nsuppress(ActiveRecord::StatementInvalid) do\r\nActiveRecord::Base.connection.execute 'SET NAMES UTF8'\r\nend\r\nend\r\nend<\/pre>\n
set_charset<\/code> definition before. It should be correct now\u2026<\/div>\nCHARACTER SET utf8 COLLATE utf8_general_ci;<\/pre>\n