MySQLの絶対値計算ABS()で異常な数値になる理由

僕以外にはまった人はいるのかな？

DB上にある数値データを、持っている数値と比較して絶対値を測る場合にABS()を利用しますが、結果で「184467440737…」といった異常な数値が出てしまう事例を取り上げます。

異常な数値が出る条件

• 絶対値算出前の計算をすると合計が負数である。
• フィールドの型が「UNSIGNED INT」や「UNSIGNED TINYINT」などである。

極端な例だと、以下の式でも異常値が出ます。

SELECT CAST(1-2 AS UNSIGNED);

18446744073709551615

何をやったかというと、「1-2」の計算の結果を「符号なし」の値にして出力してみた、結果です。

んん？　「-1」にならないの？と思いますが「『UNSIGNED（符号なし）』として扱う」というCAST関数が使われているのですね。　ちょっと異常な式です。　だから答えも異常ですね。笑

変換されてしまうのは仕様である

こうなってしまうのはマニュアルにも書いてあって、どうやら仕様のようです。

MySQL は、符号付きでも、符号無しでも、64 バイト値での演算をサポートします。
算術演算子 ( + または - など ) を使用しており、演算のひとつは符号のない整数である場合、
結果は符合なしになります。
SIGNED  および UNSIGNED  キャスト演算子を使用して、演算を符号付き、もしくは
符号なしの 64 ビットの整数にキャストすることで、これをそれぞれオーバーライドすることが
できます。

計算元の値が1つでもUNSIGNEDの場合、計算（今回の例でいうと引き算）後に符号なしの整数に自動的に変換されるため、このような値になるようです。

ABS()を実行する前の計算では負数になることもあるのですから、結果を符号つき（SIGNED）にしておかないと異常な数値となってしまうわけです。

解決法

というわけで、解決法は上にもあったCAST()を利用してSIGNEDに変換してあげることで、合計値がマイナスの計算でも正しく絶対値を求めることができます。

SELECT ABS(CAST(1-2 AS SIGNED));

1

学習のポイント

MySQLは奥が深いですなぁ。