MySQL の "Illegal mix of collations" エラーについて

MySQL で「Illegal mix of collations」というエラーが出ることがあります。テーブルの charset と接続の charset 等、すべてを utf8 などで統一してれば出ないので、あまり見ることはないかもしれません。

私はカラム毎に charset を指定することがあるので、時々このエラーにハマります。

たとえば、次のようなテーブルを作ったりします。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(320) CHARSET ascii UNIQUE,
  name VARCHAR(30) CHARSET utf8
);

メールアドレスの規約は、ローカル部が最大64バイト、ドメイン部が最大255バイト、それと @ の1バイトで合計最大320バイトなので、 VARCHAR(320) にします。UNIQUE インデックス持つカラムを utf8 charset で VARCHAR(320) として宣言すると、「Specified key was too long; max key length is 767 bytes」というエラーになってしまいます。どうせメールアドレスは ASCII文字しか入らないはずなので ascii charset にします。*1

name は漢字が入るので utf8 にしておきます。

email に ascii に変換できない文字を入れようとするとエラー(または警告)になります。変なデータが入らないので良いですね。*2

mysql> INSERT INTO users (email) VALUES ('ほげ');
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\xBB\xE3\x81\x92' for column 'email' at row 1

問題は、SELECT の WHERE で ascii 以外の文字と比較した時です。

mysql> SELECT id FROM users WHERE email='ほげ';
ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

ascii カラムと utf8 文字列を比較するとエラーになります(比較できないんだったら、エラーにしないで黙って不一致扱いにしとけばいいのに…。ブツブツ…)。

ちなみに utf8 カラムと ascii 文字列の比較はエラーになりません。あたりまえですが。

mysql> SELECT id FROM users WHERE name='ABC';
Empty set (0.00 sec)

カラムとリテラル文字列を比較する場合、カラムの charset が使用され、リテラル文字列をカラムの charset に変換してから比較します。変換できない場合に「Illegal mix of collations」エラーになります。utf8 の "ほげ" は ascii に変換できないのでエラーになります。

カラムが utf8 でリテラル文字列が ascii の場合は、ascii→utf8 の変換に問題ないのでエラーにならないのですね。

まあ、自分でクエリを書くときにわざわざ ascii カラムを utf8 文字列と比較するようなクエリを書いてエラーになる人はいないでしょうけど、ユーザーにメールアドレスを入力させて検索するようなアプリケーションの場合は、入力された文字列をそのままクエリに渡すとエラーになってしまいます。

解決策1 COLLATE を指定する

比較の際にカラムの charset ではなくリテラル文字列の charset に合わせることができます。

SELECT id FROM users WHERE email='ほげ' COLLATE utf8_general_ci;

このようにすればカラムが utf8_general_ci に変換されて比較されます。ただ、カラムの値を変換して比較するということはインデックスが使用されなくなるということなので、他にインデックスを使用する条件がなくて大量のレコードがある場合は避けたほうがいいでしょう。

解決策2 文字列を比較対象のカラムの charset に無理矢理キャストする

MySQLリテラル文字列では charset を指定することができます。

SELECT id FROM users WHERE email=_ascii'ほげ';

これは UTF-8 の「ほげ」ではなく、「0xE3 0x81 0xBB 0xE3 0x81 0x92」という6バイトの ASCII 文字列*3として扱われるため、エラーになりません。

リテラルでない文字列、たとえばプリペアドステートメント等ではこの表記は使えません。

解決策3 CONVERT() を使用する

式中で CONVERT() を使用して charset を変換することができます。

SELECT id FROM users WHERE email=CONVERT('ほげ' USING binary);

これは「ほげ」を binary charset に変換しています。binary charset は特殊な charset で、charset 情報だけを変換してコードはそのまま保持されます。ですので、convert('ほげ' using binary) の結果は「0xE3 0x81 0xBB 0xE3 0x81 0x92」の6バイトの binary 文字列になります。また、binary charset とその他の文字列を比較する場合は、charset を変換することなく、バイトコードが一致しているかどうかで比較されるので、どのような charset の文字列と比較してもエラーになりません。

これはプリペアドステートメントでも使用できます。

PREPARE stmt1 FROM 'SELECT id FROM users WHERE email=CONVERT(? USING binary)';
SET @val='ほげ'
EXECUTE stmt1 USING @val;

その他

フレームワークがクエリを生成していて変更できない場合等は、しょうがないので、プログラムでエラーメッセージをチェックして、「Illegal mix of collations」だったら、一致したものがなかったとみなすという方法があります。変換できないということは、その文字列がカラムに入ってるわけないので。

ただし「email<>"ほげ"」みたいに一致していないものを探す条件でも「Illegal mix of collations」は発生するので、その場合にはこの方法は使えません。

どうせプログラムで頑張るのであれば、MySQL に問い合わせる前にユーザーの入力をチェックして入力値エラーとして扱ったほうがいいような気もします。

おわりに

というわけで、特別な事情がなければカラム毎に charset を指定するとかマニアックなことは避けたほうがやっぱり無難という、身も蓋もない結論でした。

*1:このエラーは設定によって回避することも可能です。詳しくは id:kamipo さんの記事 http://blog.kamipo.net/entry/2012/11/13/102024 等を参照してください

*2:sql_mode が STRICT_ALL_TABLES か STRICT_TRANS_TABLES の時にエラーになります。

*3:MySQL の ascii charset は8ビットの範囲も含んでいます。0x80-0xFF の範囲は ascii charset 同士であれば比較できますが、他の charset には変換できない特殊な文字です。