MySQLのLIKE句の検索対象に文字列型と整数型を連結した場合の注意

MySQL tech

今日知ったこと。

CREATE TABLE emp (
  id INT AUTO_INCREMENT PRIMARY KEY
  , name VARCHAR(40) NOT NULL
);

とかいうテーブルの時に、下のSELECT文のLIKE句はケース依存になる。

SELECT id, name, CONCAT(emp, id) FROM emp WHERE CONCAT(emp, id) LIKE '%a%'

A5M2などでWHERE句を外したSELECT文を実行してみると、CONCAT(emp, id)のカラムがバイナリで表示された。整数型と文字列型を結合したらこうなるのか？ってことでリファレンスよく読んだらちゃんと書いてあった。

http://dev.mysql.com/doc/refman/5.1/ja/string-functions.html

(一部抜粋)

CONCAT(str1,str2,...)

引数を連結した結果であるストリングを戻します。ひとつ以上の引数を持つ場合があります。すべての引数が非バイナリ ストリングである場合、結果は非バイナリ ストリングになります。引数がひとつでもバイナリ ストリングを含む場合は、結果はバイナリ ストリングになります。数値の引数はそれに等しいバイナリ ストリング形態に変換されます。それを避けたい場合は、次の例のように、明示的なタイプ キャストを使用することができます :

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

キャストの他にも、

SELECT id, name, CONCAT(emp, id) FROM emp WHERE UPPER(CONCAT(emp, id)) LIKE UPPER('%a%')

とかでも回避できる。あんまりきれいじゃないですね。ちなみに、

SELECT id, name, CONCAT(emp, id) FROM emp WHERE CONCAT(emp, 1) '%a%'

これはバイナリ型にならずにケース非依存で検索できた。なぜ？