Help us understand the problem. What is going on with this article?

MySQLで文字列=0の比較をすると結果が全て返ってきた

More than 3 years have passed since last update.

背景

文字列を送信して、DBの対象のフィールドと比較してマッチしたものを返すという簡単なAPIを作りテストを行っていました。

その最中、なぜか数値の0を送ると全てのデータが返ってくるバグを見つけプログラムを見直したところ
数値は比較対象に限らず数値のままクエリ文に埋め込まれてしまっていたようで、その時のSQLをざっくり表すと下のような感じ。

select
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   |     |                     |                |
...

はて?
となりながらも、とりあえず数値も文字列と比較させるなら明示的にしておく方が良いかなと修正。

modify_select
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として扱われるので結果的に同じ現象になります。

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away