MySQL リファレンスマニュアル :: A.5.7 浮動小数点比較の問題

浮動小数点数は、コンピュータアーキテクチャ内部では正確な値として格納されないため、混乱を引き起こす場合があります。通常画面に表示される値は、正確な値ではありません。

フィールドタイプ FLOAT、DOUBLE、および DECIMAL が該当します。

CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
(6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+

結果は正確です。最初の 5 つのレコードは比較テストにパスしないように見えますがパスできます。コンピュータアーキテクチャによりますが、数の差異は、小数点第 10 位くらいで現れるためです。

結果は浮動小数点数なので、ROUND( )（または同様の関数）を使用して問題を解決することはできません。たとえば、以下のとおりです。

mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+

カラム 'a' 内の数は以下のようになります。

mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,
    -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b;
+------+----------------------+-------+
| i    | a                    | b     |
+------+----------------------+-------+
|    1 |  21.3999999999999986 | 21.40 |
|    2 |  76.7999999999999972 | 76.80 |
|    3 |   7.4000000000000004 |  7.40 |
|    4 |  15.4000000000000004 | 15.40 |
|    5 |   7.2000000000000002 |  7.20 |
|    6 | -51.3999999999999986 |  0.00 |
+------+----------------------+-------+

コンピュータアーキテクチャによって、同じ結果が表示されたりされなかったりします。 CPU ごとに浮動小数点数の評価方法が異なります。たとえばマシンの中には、両方の引数を 1 で掛け合わすと '正確な' 結果を得られるものがあります。たとえば以下のようになります。

警告: あなたのアプリケーションでは、この方法を決して信用しないでください。これは間違った方法の例です。

mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+------+
| i    | a      | b    |
+------+--------+------+
|    6 | -51.40 | 0.00 |
+------+--------+------+

上記の例が動作するように見える理由は、テストが実行されたマシンで、CPU 浮動小数点演算が複数の数字を偶然同じ数字に四捨五入してしまうことにあります。しかし、CPU がそうしなければならないという規則はないため、この例は信用できません。

浮動小数点数比較を行う正しい方法は、まず数の許容範囲を決定し、続いて許容範囲の数に対して比較を行うことです。たとえば、浮動小数点数は同一と見なすということにすれば、それらが 10000 分の 1（0.0001）の精度で同じである場合、比較が以下のように行われます。

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+--------+------+
| i    | a      | b    |
+------+--------+------+
|    6 | -51.40 | 0.00 |
+------+--------+------+
1 row in set (0.00 sec)

逆に、数が同じになるレコードを取得したい場合は、テストは次のようになります。

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) < 0.0001;
+------+-------+-------+
| i    | a     | b     |
+------+-------+-------+
|    1 | 21.40 | 21.40 |
|    2 | 76.80 | 76.80 |
|    3 |  7.40 |  7.40 |
|    4 | 15.40 | 15.40 |
|    5 |  7.20 |  7.20 |
+------+-------+-------+