MySQLの文字列型から数値型への自動型変換が意味不明すぎる
SQLでは違う型同士の演算で暗黙の型変換が起こります。この挙動はDBMSごとにバラバラであるため、混乱の元になることがあります。これについては徳丸さんが「SQLの暗黙の型変換はワナがいっぱい - 徳丸浩の日記」などで指摘されているので、一読をお勧めします。
徳丸さんの記事に関連してMySQLの自動型変換について調べてみたところ、予想外の実験結果が得られました。本稿ではこれを紹介します。
先に結論を書いておくと、僕にはMySQLの気持ちがサッパリわからんということがわかりました。
マニュアルによれば、文字列を数値に型変換すると浮動小数点数になるらしい
先日の記事「MySQL5.1以降の小数の扱いがキモい」で、僕は「文字列から小数へキャストする場合には浮動小数点数になります。」と書きました。これはMySQLマニュアルに次のような記述があったためです。
比較の演算の両方の引数がストリングの場合、それらはストリングとして比較されます。
両方の引数が整数の場合、それらは整数として比較されます。
（略）
他のすべてのケースでは、引数は浮動少数点 ( 実 ) 数として比較されます。
この説明の通りなら、数値型と文字列型を比較すると必ず浮動小数点数比較されそうな気がします。しかし、この理解では説明できない現象2件に気づきました。
BIGINT型のカラム値と文字列リテラルとの比較は整数比較
マニュアルの記述に反し、BIGINT型と文字列リテラルを比較すると浮動小数点数を経由しません。これは次のように確認できます。以下、僕の手元のMySQL5.5.14での実行結果を示します。
mysql> create table bigint_test(id integer auto_increment primary key, a bigint); Query OK, 0 rows affected (0.02 sec) mysql> insert into bigint_test(a) values(9007199254740992),(9007199254740993),(9007199254740994),(9007199254740995),(9007199254740996); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from bigint_test; +----+------------------+ | id | a | +----+------------------+ | 1 | 9007199254740992 | | 2 | 9007199254740993 | | 3 | 9007199254740994 | | 4 | 9007199254740995 | | 5 | 9007199254740996 | +----+------------------+ 5 rows in set (0.00 sec)
実験用に2の53乗付近の数を5個INSERTしてみました。これらの値に対し、倍精度浮動小数点数ではピッタリ表現できない数である9007199254740993を利用して、どれとマッチするのか確認してみます。
mysql> select * from bigint_test where a='9007199254740993'; +----+------------------+ | id | a | +----+------------------+ | 2 | 9007199254740993 | +----+------------------+ 1 row in set (0.00 sec)
マッチしたのは1レコードだけでした。このことから、BIGINT型の値と文字列リテラルとを比較した場合は64ビット整数として比較されていることがわかります。というのも、'9007199254740993'が倍精度浮動小数点数に型変換されているならば、9007199254740992に丸められて2レコードがマッチするはずだからです。
BIGINT型のカラム値と文字列型のカラム値の比較は浮動小数点数比較
BIGINT型と文字列型の比較は常に整数比較になるというわけではなく、マニュアルの通り浮動小数点数比較になることもあるようです。次の例を見てください。
mysql> create table mixed_test(id integer auto_increment primary key, b bigint, c varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> insert into mixed_test(b,c) values(9007199254740992,'9007199254740992'),(9007199254740992,'9007199254740993'),(9007199254740993,'9007199254740992'),(9007199254740993,'9007199254740993'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from mixed_test; +----+------------------+------------------+ | id | b | c | +----+------------------+------------------+ | 1 | 9007199254740992 | 9007199254740992 | | 2 | 9007199254740992 | 9007199254740993 | | 3 | 9007199254740993 | 9007199254740992 | | 4 | 9007199254740993 | 9007199254740993 | +----+------------------+------------------+ 4 rows in set (0.01 sec)
カラムbはBIGINT型、カラムcはVARCHAR型です。これを比較してみましょう。
mysql> select * from mixed_test where b=c; +----+------------------+------------------+ | id | b | c | +----+------------------+------------------+ | 1 | 9007199254740992 | 9007199254740992 | | 2 | 9007199254740992 | 9007199254740993 | | 3 | 9007199254740993 | 9007199254740992 | | 4 | 9007199254740993 | 9007199254740993 | +----+------------------+------------------+ 4 rows in set (0.01 sec)
ここでは浮動小数点数として比較されたため、9007199254740992と9007199254740993が同じだと言われています。文字列リテラルのときと文字列型のカラムのときで挙動が変わるというのは一体どういうことなんでしょうか。
DECIMAL型と文字列リテラルの比較は浮動小数点数比較
同じ実験をDECIMAL型でもやってみましょう。
mysql> create table decimal_test(id integer auto_increment primary key, a decimal(20)); Query OK, 0 rows affected (0.01 sec) mysql> insert into decimal_test(a) values(9007199254740992),(9007199254740993),(9007199254740994),(9007199254740995),(9007199254740996); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from decimal_test; +----+------------------+ | id | a | +----+------------------+ | 1 | 9007199254740992 | | 2 | 9007199254740993 | | 3 | 9007199254740994 | | 4 | 9007199254740995 | | 5 | 9007199254740996 | +----+------------------+ 5 rows in set (0.01 sec)
BIGINTのときと同様のデータです。
mysql> select * from decimal_test where a='9007199254740993'; +----+------------------+ | id | a | +----+------------------+ | 1 | 9007199254740992 | | 2 | 9007199254740993 | +----+------------------+ 2 rows in set (0.00 sec)
'9007199254740993'と等しいものを探すと、9007199254740992と9007199254740993の2レコードが帰ってきました。つまり、浮動小数点数比較していることがわかります。
DECIMAL型にインデックスを作ると正しい値が取れる
DECIMAL型ならマニュアルの記述通り常に浮動小数点数比較になるかというと、そうではない例に気づきました。上記テーブルにインデックスを作ると結果が変わってしまうのです。
mysql> create index a_idx on decimal_test(a); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from decimal_test where a='9007199254740993'; +----+------------------+ | id | a | +----+------------------+ | 2 | 9007199254740993 | +----+------------------+ 1 row in set (0.01 sec)
今度は9007199254740993だけにマッチしました。インデックスからデータを引っ張ってくるときは比較の方法が変わるみたいですね。
まとめ
MySQLの数値型と文字列型の比較は浮動小数点数比較になるようにマニュアルに書いてありますが、整数比較になる例外ケースがいくつか見つかりました。具体的には、BIGINT型と文字列リテラル、インデックスつきのDECIMAL型と文字列リテラル、という2組が整数比較されているとわかりました。
今回はMySQL5.5.22のソースコード中にデバッグ出力を埋めて実験したのですが、数値と文字列の比較であれば大抵は浮動小数点数比較のメソッドArg_comparator::compare_realが呼ばれていました。
ですから、マニュアルの記述はそれほど間違っておらず、BIGINT型と文字列リテラルの比較が例外中の例外ということなのかもしれません。それにしたって詳細をマニュアルに明記してほしい気がします。
DECIMAL型の方は、インデックスのときだけは文字列ベースでB-Treeが作られていると考えれば説明はつきます。ただ、マニュアル上ではそんな記述を見つけられませんでした。
