MySQLの文字列から数値への型変換について

MySQLで文字列から数値の型変換でちょっと変換のされかたが特殊だったので紹介します。公式のドキュメントはこちら

明示的な型変換

型変換というのはだいたいアプリケーション側でやるものであんまりMySQL側でやることは無いとは思いますが、明示的な型変換は以下のように行います。

SELECT CAST('1' AS SIGNED);

こちらの結果は 1 になります。
別に驚きの要素はないですね。

では、次の例です。

SELECT CAST('1_d' AS SIGNED);

実はこちらの結果も 1 になります。数字の後の文字列が切り捨てられるようですね。 Pythonなどでキャストするとエラーが出る例なので、違和感を覚えるかもしれませんが、 MySQLではこれが仕様です。

SELECT CAST('d_1' AS SIGNED);

ちなみに、こちらだと 0 になります。別に数字が入っていれば良いというわけではありません。
その上でエラーにもならず 0 になるので気をつけたほうが良いでしょう。

じゃあ、型変換しなければいいじゃない。と言われるかもしれませんが、MYSQLには暗黙の型変換というものが存在します。
例えば、以下の例です。

SELECT '1_d' + 1;

こちら、結果は 2 になります。
文字列の箇所が勝手に数値に変換されたのです。

こんなことしねーよというあなた。実はこれは大きな罠になるのです。
テーブルに id を振ってインクリメンタルな整数の数値にする方はいらっしゃると思います。
その時にこの暗黙の型変換が罠になることがあります。

SELECT FROM sampletable where id = '1_d';

こちら勘の良い方ならお気づきだと思いますが、以下の例と同等になります。

SELECT FROM sampletable where id = 1;

勝手に変換される方が安全という説もありますが、こちら全然意識してなかった方はいらっしゃるのではないでしょうか？
数値型が入ってくる想定の箇所では暗黙の型変換がされてしまうようです。

すべてを調べたわけではありませんが、このような仕組みになることを意識しないでいると思わぬバグにつながるので気をつけたほうが良いでしょう。