GA

2019/03/29

そのvarchar型のカラムに入っている値が数字だけかどうかを確認する方法

TL;DR

  • なんか SELECT * FROM t1 WHERE CONCAT('',c1 * 1) != c1 であぶりだせるらしいけどなんで？ と聞かれたのでその解説。
    • 俺は↑のやり方初めて聞いた。。
  • 個人的には WHERE c1 NOT RLIKE '^[0-9][0-9]*$' でいいんじゃない？ と思う。

前提（？）

「数値しか入らないはずのカラムに文字列が入っていてバッチが転けてるので削除しました」
「？？ カラムの型は？」
「varchar型です」
「ちょｗｗｗ」

こんな状態

mysql57 8> SELECT num FROM t1;
+----------------------+
| num                  |
+----------------------+
| 1                    |
| 2                    |
| 3                    |
| 4                    |
| ＼(^o^)／ｵﾜﾀ         |
| 0x12345              |
| 123hoge456           |
| 5                    |
| 6                    |
| 7                    |
+----------------------+
10 rows in set (0.00 sec)

ここから NOT 数字だけで構成されている 行を引っ張り出す

mysql57 8> SELECT num FROM t1 WHERE CONCAT('', num * 1) != num;
+----------------------+
| num                  |
+----------------------+
| ＼(^o^)／ｵﾜﾀ         |
| 0x12345              |
| 123hoge456           |
+----------------------+
3 rows in set, 3 warnings (0.00 sec)
Σ(ﾟдﾟlll) うわああホントに引けた、びっくり！

ここが不思議だったらしい

mysql57 8> SELECT num FROM t1 WHERE CONCAT('', num) != num; -- * 1 を省くと何も返ってこない
Empty set (0.00 sec)

mysql57 8> SELECT num FROM t1 WHERE num * 1 != num; -- CONCATを省いても何も返ってこない
Empty set, 6 warnings (0.00 sec)

動作の解説

ルーク、 SHOW WARNINGS を見るのだ

CONCAT を省いたパターン

mysql57 8> SELECT num FROM t1 WHERE num * 1 != num;
Empty set, 6 warnings (0.00 sec)

mysql57 8> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '＼(^o^)／ｵﾜﾀ'         |
| Warning | 1292 | Truncated incorrect DOUBLE value: '＼(^o^)／ｵﾜﾀ'         |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0x12345'              |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0x12345'              |
| Warning | 1292 | Truncated incorrect DOUBLE value: '123hoge456'           |
| Warning | 1292 | Truncated incorrect DOUBLE value: '123hoge456'           |
+---------+------+----------------------------------------------------------+
6 rows in set (0.00 sec)
非数値な行に対して 2回ずつ 、「CHAR型からDOUBLE型にキャストして切り詰められたよ」のワーニングが出ている。
ということは num * 1 で1回、 != num で1回、それぞれDOUBLEにキャストしているので
  • (DOUBLE) '＼(^o^)／ｵﾜﾀ' = 0 なので 0 * 1 != 0 IS FALSE
  • (DOUBLE) '0x12345' = 0 なので 0 * 1 != 0 IS FALSE
  • (DOUBLE) '123hoge456' = 123 なので 123 * 1 != 123 IS FALSE
になっていて出てこない。

* 1 を省いたパターン

mysql57 8> SELECT num FROM t1 WHERE CONCAT('', num) != num;
Empty set (0.00 sec)

mysql57 8> SHOW WARNINGS;
Empty set (0.00 sec)
いずれの行にもワーニングは出ていないので、DOUBLE型にキャストされずにCHAR型のまま判定されている。
空文字列とCONCATした結果が元の文字列と一致するのは自明なので省略。

CONCAT と * 1 を両方つけたパターン

mysql57 8> SELECT num FROM t1 WHERE CONCAT('', num * 1) != num;
+----------------------+
| num                  |
+----------------------+
| ＼(^o^)／ｵﾜﾀ         |
| 0x12345              |
| 123hoge456           |
+----------------------+
3 rows in set, 3 warnings (0.00 sec)

mysql57 8> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '＼(^o^)／ｵﾜﾀ'         |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0x12345'              |
| Warning | 1292 | Truncated incorrect DOUBLE value: '123hoge456'           |
+---------+------+----------------------------------------------------------+
3 rows in set (0.00 sec)
非数値な行に対して 1回ずつ DOUBLE型にキャストしている。
ということは CONCAT を省略した時と違ってどちらか片方はもともとのデータ型であるCHAR型のまま使っていて、CHAR型同士で比較している。
じゃあどこでキャストされたのかというと、 num * 1 の時にDOUBLE型にキャストして（乗算は数値型同士でしかできない）、 CONCAT の時にCHAR型に戻っている（ CONCAT は当然文字列型を返すから、だと思う）
DOUBLE型からCHAR型への変換は無損失変換（？）なのでワーニングが発生しないあたりに気が付けるかどうかだと思う（この仕様がどうなのかは置いておく）
  • (DOUBLE) '＼(^o^)／ｵﾜﾀ' = 0 なので CONCAT('', 0 * 1) != '＼(^o^)／ｵﾜﾀ' IS TRUE
  • (DOUBLE) '0x12345' = 0 なので CONCAT('', 0 * 1) != '0x12345' IS TRUE
  • (DOUBLE) '123hoge456' = 123 なので CONCAT('', 123 * 1) != '123hoge456' IS TRUE
  • CONCAT('', 6 * 1) = '6' なので数字だけで構成されている場合はキャストも発生しないし != は成立しない

とはいえ

MySQLには昔から RLIKE, REGEXP演算子 があるのでそれを使えば良いのでは？
mysql57 8> SELECT num FROM t1 WHERE num NOT RLIKE '^[0-9][0-9]*$';
+----------------------+
| num                  |
+----------------------+
| ＼(^o^)／ｵﾜﾀ         |
| 0x12345              |
| 123hoge456           |
+----------------------+
3 rows in set (0.00 sec)
^[0-9]*$ にしてないのは、空白だけの行があった時にこっちだとマッチしちゃうから。それを嫌わないのであれば2回繰り返す必要はないと思う。
MySQL 5.7の時点では、 \d+ みたいな拡張記法は使えない。
MySQL 8.0からは使えるようになった。
これで数値しか入らないvarchar型に変なデータが入っても安心ですね（）
_|￣|○
​
投稿者 時刻:
ラベル:

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )