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は奥が深いですなぁ。
MySQL徹底入門 第4版 MySQL 8.0対応
¥2,090 (2025-04-17 23:18 GMT +09:00 時点 - 詳細はこちら価格および発送可能時期は表示された日付/時刻の時点のものであり、変更される場合があります。本商品の購入においては、購入の時点で当該の Amazon サイトに表示されている価格および発送可能時期の情報が適用されます。)MySQL運用・管理[実践]入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ
¥3,018 (2025-04-17 07:46 GMT +09:00 時点 - 詳細はこちら価格および発送可能時期は表示された日付/時刻の時点のものであり、変更される場合があります。本商品の購入においては、購入の時点で当該の Amazon サイトに表示されている価格および発送可能時期の情報が適用されます。)3ステップでしっかり学ぶ MySQL入門[改訂第3版]
¥2,860 (2025-04-17 07:46 GMT +09:00 時点 - 詳細はこちら価格および発送可能時期は表示された日付/時刻の時点のものであり、変更される場合があります。本商品の購入においては、購入の時点で当該の Amazon サイトに表示されている価格および発送可能時期の情報が適用されます。)2010-07-26