背景
文字列を送信して、DBの対象のフィールドと比較してマッチしたものを返すという簡単なAPIを作りテストを行っていました。
その最中、なぜか数値の0を送ると全てのデータが返ってくるバグを見つけプログラムを見直したところ
数値は比較対象に限らず数値のままクエリ文に埋め込まれてしまっていたようで、その時のSQLをざっくり表すと下のような感じ。
mysql> SELECT id, title FROM comics WHERE title = 0;
+---------+---------------------------+
| id | title |
+---------+---------------------------+
| 1 | 紅殻のパンドラ |
| 2 | ゴールデンカムイ |
...
+---------+---------------------------+
78 rows in set (0.02 sec)
mysql> desc comics;
+---------------+---------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | text | NO | | | |
...
はて？
となりながらも、とりあえず数値も文字列と比較させるなら明示的にしておく方が良いかなと修正。
mysql> SELECT id, title FROM comics WHERE title = '0';
Empty set (0.00 sec)
無事想定通りの結果に。
コマンド上から行っているように見えるのはこっちの方がわかりやすいかなという理由です。
あとクラリン可愛い。
とまぁ、
とりあえずは解決したのですが、text型のフィールドに=0としてしまうと
なぜか全件返ってくる事に違和感を感じ調査してみました。
文字列と数値の比較
結論から言うと、文字列と数値を比較したときに文字列が数値として扱れ
その際、数値に変換できない文字列は全て0として扱われるために
title=0でデータが取得できていたというわけです。
ちなみに全件取得できてしまったのは偶然で、
titleの中に数字だけのデータが存在したらそのデータは取得されませんでした。
mysql> INSERT INTO comics (title) VALUES ('300');
mysql> SELECT count(1) FROM comics WHERE title = 0;
+----------+
| count(1) |
+----------+
| 78 |
+----------+
1 row in set (0.10 sec)
mysql> SELECT count(1) FROM comics;
+----------+
| count(1) |
+----------+
| 79 | -- 増えた！
+----------+
ではどうして数値として扱われるのか？
型違いの式評価について下記URLに書いてありました。
https://dev.mysql.com/doc/refman/5.6/ja/type-conversion.html
・引数のいずれかが 10 進値の場合、比較はその他の引数に依存します。その他の引数が 10 進値または整数値の場合、引数は 10 進値として比較され、その他の引数が浮動小数点値の場合、引数は浮動小数点値として比較されます。
・ほかのすべてのケースでは、引数は浮動小数点 (実) 数として比較されます。
つまり、数値との比較では何でもかんでも数値に変換して比較するよ。とのこと。
やっとスッキリしました！
なんで0になるかも調べたかったけどそれは時間があるときにでも追記しておきます。
0になる？ならない？
最後にこの文字列は数値にするとどうなるの？
というのを列挙しておきます。
-- 文字列だけ
SELECT 'a' = 0 -- TRUE
SELECT 'abc' = 0 -- TRUE
-- 10進値
SELECT '1' = 0 -- FALSE
SELECT '123' = 0 -- FALSE
-- まぜまぜ
SELECT 'x1' = 0 -- TRUE
SELECT '1x' = 0 -- FALSE 最初に数値だとその値になる
SELECT '1x' = 1 -- TRUE
SELECT '12x1' = 0 -- FALSE
SELECT '12x1' = 121 -- FALSE
SELECT '12x1' = 12 -- TRUE 最初に文字が出るまでが数値となる
結論
MySQLで文字列で比較するときは理由がないとき以外はちゃんとシングルクォーテーションなりで囲んだりして明示的に文字列としておきましょう。
SELECT _col FROM _table_ WHERE _string_col = '0';
とっても当たり前な結論でした。
蛇足
SELECT _col FROM _table_ WHERE _string_col is false;
SELECT _col FROM _table_ WHERE _string_col = false;
上記もfalseが0として扱われるので結果的に同じ現象になります。