B.5.5.8 浮動小数点値に関する問題

浮動小数点数は、近似値であり正確な値として格納されないため、混乱の原因となることがあります。SQL ステートメントで出力される浮動小数点値は、内部で表された値と同じではないことがあります。比較で浮動小数点値を正確な値として扱おうとすると、問題となることがあります。これらはまた、プラットフォームまたは実装の依存関係にも従います。FLOAT データ型および DOUBLE データ型では、これらの問題が発生することがあります。DECIMAL カラムの場合、MySQL は演算を 65 桁 (10 進数) の精度で実行するため、ほとんどの一般的な精度の問題が解決されます。

次の例では、DOUBLE を使用し、浮動小数点演算を使用して行われる計算がどのように浮動小数点エラーとなるかを示しています。

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> 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.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

正しい結果です。最初の 5 レコードは比較を満たしていないように見えますが (ab の値は異なるように見えません)、コンピュータのアーキテクチャー、コンパイラのバージョン、最適化レベルなどの要因によって、小数点以下 1 桁などの数字が異なるためにこのような結果となっている可能性があります。たとえば、CPU が異なると、浮動小数点数の評価が異なることがあります。

カラム d1 および d2DOUBLE ではなく DECIMAL として定義されていた場合、SELECT クエリーの結果は 1 行のみ (上記の最後の行) となります。

浮動小数点数の比較を正しく行うには、最初に数値の差異に関して受け入れられる許容度を決定し、許容値に対して比較を行います。たとえば、1 万分の 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.4 |    0 |
+------+-------+------+
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.4 | 21.4 |
|    2 | 76.8 | 76.8 |
|    3 |  7.4 |  7.4 |
|    4 | 15.4 | 15.4 |
|    5 |  7.2 |  7.2 |
+------+------+------+
5 rows in set (0.03 sec)

浮動小数点値はプラットフォームまたは実装の依存関係の影響を受けます。次のステートメントを実行するとします。

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;

一部のプラットフォームでは、SELECT ステートメントは inf および -inf を返します。ほかのプラットフォームでは、0 および -0 が返されます。

前述の問題は、マスターで mysqldump を使用してテーブルのコンテンツをダンプし、そのダンプファイルをスレーブにリロードすることによってレプリケーションのスレーブを作成しようとすると、浮動小数点カラムを含むテーブルが 2 つのホストで異なる内容になる可能性があることを示しています。


User Comments
  Posted by Mohamed Infiyaz Zaffer Khalid on June 1, 2010
I've just implemented a PHP-MySQL-based application and it took me a while to figure this out. I hope all of you coders out there will benefit from this tip.

In PHP, I calculated a value that arrives at the amount 20072.64 and I wrote this into a mysql field of type FLOAT.

Strangely (despite the technical specs indicating a large range), the number that got stored was 20072.6 - note truncation!

Solution: I changed the field type to DOUBLE and this was resolved.

Alternatives that did not work: Even type-casting in PHP did not do any good since ultimately the values had to be stored by MySQL.

Happy coding!
Khalid
  Posted by Felipph Calado on December 29, 2010
I had this problem too. It's happen sometimes randomly. My solution was expecify colunm to float(10,2) with 2 decimals. This looks solve my problem.

Anyway I will try double fields
  Posted by Geoffrey Downs on March 10, 2011
Khalid -
This is not a mystery. The problem is that Float columns only store 4-bytes per entry. This means that the precision available to the decimal portion of your number depends on the size of the non-decimal portion of your number. The more bytes are requires to represent the non-decimal portion of your number, the fewer bytes are available to represent the approximate decimal value of your number. If you store a sufficiently large number, your entire decimal value will be truncated to 0. You have solved the problem by increasing your per-entry storage to 8 bytes instead of 4.
  Posted by Geoffrey Downs on March 10, 2011
Following up... I *think* this is correct for the default float columns in mysql:

var yourNumber = some floating point value
max decimal precision = 10 ^ (-5 + floor(yourNumber log 10))
So:
0 < x < 10 -> max precision is 0.00001
10 <= x < 100 -> max precision is 0.0001
100 <= x < 1000 -> max precision is 0.001
etc.
  Posted by Peter Soltesz on March 26, 2012
Geoffrey Downs is right!

Selecting a tolerance level is not good, because the tolerance level differs from value to value depending on the number. As I inspected duplicates in my db for example two float stored values both 13442 compared as NOT EQUAL to each other when using too high (0.01) tolerance level, however they were EQUAL when I used lower (0.1) tolerance level.

Therefore I also recommend to change the documentation because the recommended solution (compare the difference to a selected threshold) is not safe.

I translated the equation of Geoffrey Downs to MySQL as follows for FLOAT values:

IF(ABS(yourFloat1-yourFloat2)<POW(10,FLOOR(LOG10(GREATEST(ABS(yourFloat1),ABS(yourFloat2)))-5)),"E Q U A L","N O T - E Q U A L")

Sign Up Login You must be logged in to post a comment.