MySQLでIsNumericを行いたい場合CASTしてはいけない

MySQL

2021.04.01

MySQLにて文字列と数値がごっちゃになったvarcharカラムで、SQLServerで言うIsNumericを使用したい場合、
CAST等で変換してる人は非常に危険。正しく判別するにはどうすればいいか？纏めてみた。

型変換を使う
式評価を行う
正規表現を使う

型変換を使う

タイトルで言ってた危険パターンがこちら。

 SELECT
  -- CAST可能ならその値を表示し、不可なら0を返す。
  CAST(VALUE AS SIGNED) AS 'VALUE'
 FROM
  TABLE;

SELECT

-- CAST可能ならその値を表示し、不可なら0を返す。

CAST(VALUE AS SIGNED) AS 'VALUE'

FROM

TABLE;

例えばこんなテーブルが有ったとして、

VALUE

10000

テスト

明後日10000

CASTで変換するとこうなる↓

VALUE

10000

上手く行く。でも、もしこんなテーブルだった場合↓

VALUE

10000

1万

3日後

同じSQLを実行するとこうなってしまう↓

VALUE

10000

頭が数値だと勝手に数値にCASTしてくれやがるのです。そう、MySQLならね。

入る値が決まっててイレギュラーが無いならこれでもいいのかも知れないけど、そうでない場合には思わぬエラーとなる得るので非常に危険。要注意。ちなみにCONVERTでも同じ挙動。

式評価を行う

こんなパターンで行ってる人も多いのでは？

MySQLの挙動として文字列が数値変換可能なら1(TRUE)となり、不可な場合は0(FALSE)となる。それを利用した方式。

 SELECT
  -- VALUEが数値変換可ならその値を表示し、不可な場合は0を表示させる
  CASE WHEN VALUE = 1 THEN VALUE ELSE 0 END AS 'VALUE'
 FROM
  TABLE;

SELECT

-- VALUEが数値変換可ならその値を表示し、不可な場合は0を表示させる

CASE WHEN VALUE = 1 THEN VALUE ELSE 0 END AS 'VALUE'

FROM

TABLE;

しかし残念ながらこれも結果は型変換の時と同じなのです。

無駄な所で頑張ってくれちゃうMySQLは仕様自体がそうなっちゃってるので、素直にvarcharに数値なんか入れるな、ちゃんと設計せぇって事ですね。

でも致し方無いパターンや、既に運用に乗っててクソ設計だけど今更どうにも出来ん!って場合はこう↓

正規表現を使う

This is 安全パターン。

 SELECT
  -- VALUEが0~9で形成されている場合はその値を表示し、0~9以外が含まれる場合は0を表示させる
  CASE WHEN VALUE REGEXP '^［0-9］+$' THEN VALUE ELSE 0 END AS '数値かどうか'
 FROM
  TABLE;

SELECT

-- VALUEが0~9で形成されている場合はその値を表示し、0~9以外が含まれる場合は0を表示させる

CASE WHEN VALUE REGEXP '^［0-9］+$' THEN VALUE ELSE 0 END AS '数値かどうか'

FROM

TABLE;

これならば先程の’1万’だろうと’3日後’だろうと0となり、10000のみ10000が返る。比較的WHERE句で用いられる事が多い正規表現ですけども、実はどこでも活用出来る凄い奴。

マッチングパターンは色々有り、[a-z]で小文字の英字だったり、^で文字列の先頭に一致したり～等々…

中々初めは結構構えてしまいがちだけども要はLIKEの拡張版みたいなもので、慣れればどんな抽出も出来る様になるので是非覚えて活用していきましょう(ᵔᴥᵔ)

とんくんより:

2020年6月29日 5:49 PM

記述ミスてますよw

VALUE REGEXP \’^[0-9]+$\’ THEN VALUE

↓

VALUE REGEXP \’^[0-9]+$\’ THEN VALUE

返信

はまみより:

2020年7月9日 5:36 PM

>とんくんさん
文字化けしてたようなので修正しました！
ご指摘ありざす(ᵔᴥᵔ)

返信