先日開催した Club MySQL で徳丸さんが面白いものを紹介してくれたのが印象に残ったので、整理してみます。
テーマ
「文字列と数字の自動変換」です。
基礎知識
MySQLは、なるべく可能な限り自動的に型変換を行って、ユーザの希望を叶えるべく努力をしてくれます。
以下の２つを見てください。
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の自動変換のお話でした。まぁ文字列の列を検索キーにしている時にそこに数値を与えるなよ、とか、数値演算子を使うなよ、という当たり前の話なので、それほど怖がることでもないかなとも思います。正しい知識を付けましょう。
