MySQLでvarcharカラムをintと比較するとおかしな結果になる

9月7日に真相（？）について追記しました。

自明のことだがSQLではWHERE句で指定した条件にマッチしたレコードのみを取得することが出来る。だが、MySQLでは文字列型のカラムに対して = 数値 という条件にすると、おもしろい（おかしな）結果になる。

対象テーブルの定義とデータ

以下のようにとてもシンプルなテーブルがある。

CREATE TABLE `string_table` (
`id` int(11) NOT NULL,
`value_string` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

データはそれなりにたくさん入っている。

select count(*) from string_table;
→ 296,458行

クエリとその結果

まずは普通に文字列と完全一致で検索。

select * from string_table where value_string = 'aaa';

これは自明。

では次に0と完全一致で検索。予想では型が異なるのでSQLエラーになると思いきや、

select * from string_table where value_string = 0;

と、結果が取得出来る。それも件数を確認すると296,442行となっており、ほぼ全てのレコードが検索結果として出ていることが分かる。？？？なんだこれは。

ということで似たような条件で検索してみると…

select * from string_table where value_string = 1;

SQLはエラーにならず、結果は0件。

select * from string_table where value_string = 5;

SQLは当然エラーにならず、結果は3件。

select * from string_table where value_string like '5%';

で検索してみた結果と同一だったので、先頭が5の文字列のレコードがヒットしている。

結論

MySQLでは文字列型のカラムに対して条件を integer で指定すると、思わぬレコードが取得できてしまう。

なんでこうなるのかが気になるので色々と調べてみたが理由が分かっていない…。

真相

ブログを見てくれた方から情報をいただいた。

http://blog.usoinfo.info/article/180598548.html

解説はほぼ上記のブログで完結しているが、少しだけ補足する。

MySQLでは、左辺と右辺の型が異なる場合、どちらかに合わせて（変換して）比較が行われる。

文字列と文字列であれば変換は発生しないので意図した通りの結果になるが、今回は左辺が文字列で右辺が数値になっている。この場合、MySQLでは左辺と右辺の両方を数値に変換してから比較を行う。今回のケースでは左辺には既存のレコードの値が入ってくるが、数字になり得ないもの（ aaaaa, bbbbb, aa033e など）が来た場合は全て0に変換されてしまう。

今回の検索結果の1行目を例にすると、

select * from string_table where value_string = 0;

↓ （1行目のデータが入ったとすると）

select * from string_table where 'sc167' = 0;

↓ （sc167は数字になり得ないので0に変換される）

select * from string_table where 0 = 0;

この時、 where句は true となるためこのレコードが抽出結果として出てくる。

なお、=5 の時は先頭が5となっている文字列が全て抽出されている。

どうやらMySQLは先頭が数字で始まっている文字列は数字が終わるまでの部分で数値に変換してくれるようだ。

とてもすっきりした。

情報をくださった方、ありがとうございました。

補足

そもそも、こんなことが起きたのは Prepared Statement を使用して php から MySQL のデータを取得していたのだが、埋め込むパラメータを変数で渡していたのだが、明示的に文字列にキャスト等をしておらず、文字列が全て数字で構成されていた場合に文字列ではなく数値と扱われてしまい、最終的に生成されたSQLが = 0 というようになってしまって今回の現象が起きた。

ちなみに Oracle で = 0 を試したところ結果は0件だった。SQLの仕様ではなさそうだが真相はよく分かっていない。
ソースを読めば分かるけどね…。