MySQLの暗黙の型変換~文字列と数字~の罠

 先日開催した Club MySQL で徳丸さんが面白いものを紹介してくれたのが印象に残ったので、整理してみます。

テーマ

 「文字列と数字の自動変換」です。

基礎知識

 MySQLは、なるべく可能な限り自動的に型変換を行って、ユーザの希望を叶えるべく努力をしてくれます。
以下の2つを見てください。

mysql> SELECT 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

mysql> SELECT '1'+2;
+-------+
| '1'+2 |
+-------+
|     3 |
+-------+

 前者はふつうに 数値 1 と 数値 2 を足し算したものですが、後者は、文字列 '1' と 数値 2を足し算したもので、厳密には演算不可能なものですが、MySQLは「いち と に を足したいんだよね?」と気を遣ってくれて、その結果 数値 1 と 数値 2 の和算を実施してくれます。
 この文字列変換は、必ずしもすぱっと文字→数値 に変換できる場合以外でも、読めるところまで頑張って読んで変換してくれます。

mysql> SELECT '1nin'+'2ko';
+--------------+
| '1nin'+'2ko' |
+--------------+
|            3 |
+--------------+


余談ですが、最初に出てきた「数字以外の文字」のところまでを数値変換してくれるので、以下のように、直観には反する結果を得たりもしますが、これもルールどおりです。

mysql> SELECT '1,234,567' + 3;
+-----------------+
| '1,234,567' + 3 |
+-----------------+
|               4 |
+-----------------+

 ここまでが、文字と数値の自動変換の基礎知識のお話。


自動変換されて直感とは異なる動作

 ここに、以下のような構造と値を持つ テーブル t1 があるとしましょう。

mysql> select * FROM t1;
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 18:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 18:47:41 |
+------+---------+--------+---------------------+

 ここで、WHERE句に name列を指定しての抽出の例をいくつか見てみます。
まず普通にズバリ一致。

mysql> SELECT * FROM t1 WHERE name='oreore';
+------+--------+--------+---------------------+
| id   | name   | himitu | created_at          |
+------+--------+--------+---------------------+
|    2 | oreore | naisyo | 2021-01-21 18:47:41 |
+------+--------+--------+---------------------+

 WHERE句に指定された 'oreore' に一致する行が取得されます。普通の動きです。
試しに 'oreore' のかわりに 'ore' にしてみると、そんなものに一致する行はないので、ゼロ行が得られます。

mysql> SELECT * FROM t1 WHERE name='ore';
Empty set (0.00 sec)


 ではここで、'ore' に 数値ゼロを加えたものを指定するように変えてみます。

mysql> SELECT * FROM t1 WHERE name='ore'+0;
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 18:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 18:47:41 |
+------+---------+--------+---------------------+
2 rows in set, 3 warnings (0.00 sec)

 なんと、テーブルの全件が得られました!
徳丸さんの講演の中では、数値を加えるのではなく文字列を加えるものだったので、それも試してみます。

mysql> SELECT * FROM t1 WHERE name='ore'+'nanika';
+------+---------+--------+---------------------+
| id   | name    | himitu | created_at          |
+------+---------+--------+---------------------+
|    1 | watashi | NULL   | 2021-01-21 18:47:02 |
|    2 | oreore  | naisyo | 2021-01-21 18:47:41 |
+------+---------+--------+---------------------+
2 rows in set, 4 warnings (0.00 sec)

 こちらも t1 の全件が得られました。知らないとびっくりするような結果ですね。なので、ちゃんと知っておきましょうというのがこのブログの趣旨。


ポイントは「自動変換」

 数値の和算である "+" の演算子は、その両脇は数値であることを期待します。
先ほど紹介した「'1nin'+'2ko'」が 3 になったのは、左辺 '1nin" を左から見ていって数値と判断できるところまで(つまり 1)を採用したものであり、「'1,234,567'」は、同様に左から見ていって 1 の部分だけが数値として判断された(2文字目 "," は、すでに数値ではない)ので、 1 として扱われたということです。
 とすると「'ore'」はどうでしょうか。"+" の演算子により、これを数値として扱おうとするとき、先ほどと同様に左から見ていきます。すると、いきなり 'o' (おー) が現れて、はい終了、この場合 ゼロと見做されます。’nanika' も同様にゼロと見做され、ゼロプラスゼロはゼロ。これは以下のクエリでも確認できます。

mysql> SELECT 'ore'+'nanika';
+----------------+
| 'ore'+'nanika' |
+----------------+
|              0 |
+----------------+
1 row in set, 2 warnings (0.00 sec)

 ところで、全件がマッチしてしまったクエリ。

WHERE name='ore'+'nanika';

 となっていました。この右辺 'ore'+'nanika' がゼロになることは先ほど示されました。
そうすると、さて、左辺はどのように扱われるのでしょうか。そう。数値に変換されるのです。テーブル t1 で言うと各行のnameの値、'watasi'と'oreore'が数値に変換(0になる)されるということです。左辺もゼロ、右辺もゼロ。ということでマッチしたというわけです。
 なので、先ほど「全件がマッチ」と書きましたが、name列の値として '96nin' のように数字で始まる文字列がある場合、その行はマッチしない、というのが正確な言い方になりますね。

 ちなみに、先ほどのクエリ、4 warnings と出ているのでワーニングを確認してみると、こんな感じ。切り捨てられていますよ、と言われていますね。WHERE句の右辺に使用した 'ore', 'nanika' が、数値変換で切り捨てられましたよ、というものと、列の値である 'watashi', 'oreore' が切り捨てられましたよ、というものが含まれていることがわかります。

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'watashi' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ore'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'nanika'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'oreore'  |
+---------+------+---------------------------------------------+

 以上、知らないとちょっとコワいMySQLの自動変換のお話でした。まぁ文字列の列を検索キーにしている時にそこに数値を与えるなよ、とか、数値演算子を使うなよ、という当たり前の話なので、それほど怖がることでもないかなとも思います。正しい知識を付けましょう。

というのは先人たちも語っている

 という話は、じつは 5年ほどまえに そーだいさんもブログで紹介していました。さすが!

soudai1025.blogspot.com