現在管理しているシステムで「たまに覚えのない更新がある」というお話がありました。
調べてみると、ある更新処理のWHERE句でstring型カラムを指定していました。
そして、その条件になる値に0という数値が渡される場合がありました。
string型のカラムに対して、WHERE句で0を指定するとどうなるでしょうか。
実は、MySQLでは式評価で別の型同士の値を比較する際に型変換します。 どういうことかというと、両辺の型が同じ場合はそのまま比較しますが、 両辺の型が違う場合は、都合よく型を変換して比較するということをします。
演算子が別の型のオペランドとともに使用されると、オペランドの互換性を保つために型変換が発生します。 一部の変換は暗黙的に発生します。たとえば、MySQL では必要に応じて数字が文字列 (またはその逆) に自動的に変換されます。
引用 - MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.2 式評価での型変換 (https://dev.mysql.com/doc/refman/5.6/ja/type-conversion.html)
例えば以下のようなテーブルがあったとします。
animalはvarchar型です。
zoo
+----+--------+-----+------------+
| id | animal | cnt | date |
+----+--------+-----+------------+
| 1 | 0dog | 1 | 2017-01-07 |
| 2 | 1cat | 2 | 2017-12-08 |
| 3 | 3tiger | 3 | 2017-08-01 |
| 4 | lion | 4 | 2017-05-01 |
| 5 | wolf5 | 5 | 2017-07-01 |
+----+--------+-----+------------+
このテーブルに対して以下のクエリを実行すると1件のデータが返ってきます。
SELECT * FROM zoo WHERE animal = 1;+----+--------+-----+------------+
| id | animal | cnt | date |
+----+--------+-----+------------+
| 2 | 1cat | 2 | 2017-12-08 |
+----+--------+-----+------------+
1 row in set (0.00 sec)
暗黙の型変換が発生し、1catが1として判定されるため1件の結果が返ってきます。
何故1catが1になるかというと、
文字列の場合、先頭の文字から数値に変換出来る箇所までを数値として扱います。
そのため、1catの場合は1になってしまいます。
以下のクエリの場合、先頭の文字が5始まるデータが無いので1件も返ってきません。
SELECT * FROM zoo WHERE animal = 5;Empty set (0.00 sec)
今度は以下のクエリを実行すると3件のデータが返ってきます。
SELECT * FROM zoo WHERE animal = 0;+----+--------+-----+------------+
| id | animal | cnt | date1 |
+----+--------+-----+------------+
| 1 | 0dog | 1 | 2017-01-07 |
| 4 | lion | 4 | 2017-05-01 |
| 5 | wolf5 | 5 | 2017-07-01 |
+----+--------+-----+------------+
3 rows in set (0.00 sec)
0dogは先程と同じ理由です。
なぜlionとwolf5が返ってくるのでしょうか。
lionを数値に変換しても数字になりません。すると0として扱われることになります。
つまりWHERE 0 = 0になるので、lionはtrue扱いになってしまい、結果に出力されます。
wolf5も同じです。
何年も動いているシステムですが、前任者も気づかなかったようです。 幸いなことにバックアップもありましたし、変更されたデータは重要なものでは無かったので事なきを得ました。
調べてみると古い記事も多いので、基本的で当たり前のことなのかもしれません。 詳しい人からしたら「何だ今更か」と思われるかもしれませんが、知らなかったのでとても勉強になりました。 ただ、とてもとても怖い。