2017-12-21

MySQLのデータ型としてFLOAT型という型があるのですが、これを採用するのは混乱の元ではないか？と感じたので、その詳細を紹介します。
そもそもこの話のきっかけは﹁MySQLで6桁までの小数点を丸めずに扱うならFLOAT型を使うべき理由﹂という記事が目に止まったことです。それなりに人気を集めている記事のようですが、私の読んだ限りではFLOAT型を使うだけの根拠が文中から読み取れず、さらに類似する一次情報や英語記事が全く見つからなかったので、真偽が怪しい情報だと感じました。
その後、MySQL上で実験したりCソースコードを読んでみたりした結果、私の得た結論は真逆のものになりました。MySQL警察の方や浮動小数点数警察の方、追試や反論など頂けると助かります。

MySQLのFLOAT型とは

MySQLのFLOAT型は原則としてIEEE754浮動小数点数単精度型︵32bit︶で実現されます*1。これ自体はMySQLマニュアルからも読み取れる内容です。 FLOAT および DOUBLE 型は概数値データ値を表します。MySQL は、単精度値には4バイトを、倍精度値には8バイトを使用します。
https://dev.mysql.com/doc/refman/5.6/ja/floating-point-types.html
名前としてもC言語のfloat型に対応していそうな名前ですから、これ自体に特に違和感は無いと言えるでしょう。

不思議な挙動(1)：+0すると見た目の値が変わる

ところで、MySQLでFLOAT型を使うと説明しづらい結果になることがあります。実際に試してみましょう。下記実験に利用したMySQLのバージョンは5.7.20です。

mysql> CREATE TABLE a (fFLOAT, d DOUBLE);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTOaVALUES(0.9,0.9);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM a;
+------+------+
| f    | d    |
+------+------+
|  0.9 |  0.9 |
+------+------+
1 row in set (0.00 sec)

FLOAT型とDOUBLE型のカラムを作り、両者に0.9を代入してみました。SELECTしてみると、それぞれのカラムの値は0.9と表示されます。
ところが、それぞれに0を足すと不思議な結果が返ってきます。

mysql> SELECT f+0,d+0 FROM a;
+--------------------+------+
| f+0                | d+0  |
+--------------------+------+
| 0.8999999761581421 |  0.9 |
+--------------------+------+
1 row in set (0.00 sec)

なんとFLOAT型の値の方が0.9では無くなってしまいました。これは+0したせいで値が変わったわけではなく、元々格納されていた値が表示されているだけです。﹁0.8999999761581421…﹂はIEEE754単精度浮動小数点数で0.9に一番近い数ですので、FLOAT型では単精度で値が保存されている証拠とも言えるでしょう。
一方、d+0が0.9と表示されているのはdの値が倍精度浮動小数点数で0.9に一番近い数だからです。MySQLはDOUBLE型の値を表示する際、取り得る表現の中で最短となる10進表現で表示します。このことはMySQLマニュアルにも記述があります。 dtoa ライブラリでは、次のプロパティーを使用した変換が提供されます。Dは DECIMAL または文字列表現を含む値を表し、Fはネイティブバイナリ (IEEE) 書式の浮動小数点数を表します。
F -> D の変換は、最大限の精度で実行され、読み取り時にFが生成されるもっとも短い文字列としてDが返され、IEEE で指定されているネイティブバイナリ形式でもっとも近い値に丸められます。
https://dev.mysql.com/doc/refman/5.6/ja/type-conversion.html
そんなわけで、10進小数を格納する場合にはFLOAT型もDOUBLE型も丸め誤差を含む値になるのですが、最初に紹介したQiita記事の読者がそれを読み取れるかは疑問です。もしかするとFLOAT型のことを10進6桁までをピッタリ扱える便利な型だと勘違いしてしまう人さえいるかも知れませんが、そんなわけはありません。

不思議な挙動(2)：INSERTした値がWHERE句の検索条件に使えない

先ほどのテーブルを使ってもう少し実験してみます。今度はWHERE句で浮動小数点型を持つレコードを検索してみましょう。

mysql> SELECT * FROMaWHERE f=0.9;
Empty set (0.00 sec)

mysql> SELECT * FROMaWHERE d=0.9;
+------+------+
| f    | d    |
+------+------+
|  0.9 |  0.9 |
+------+------+
1 row in set (0.00 sec)

fもdもINSERT文で0.9を入れたので、当然WHERE句の検索でも0.9が使えるだろうと考える人がいるかもしれません。ところが、実際に0.9を検索してみるとFLOAT型の方では1件もマッチしません。
この原因は、浮動小数点数絡みの演算は全てDOUBLE型で計算されるため*2です。fの値は﹁0.8999999761581421…﹂となりますが、DOUBLE型としての0.9は﹁0.900000000000000022…﹂となるため、異なる値だと判断されてしまうのです。一方で、DOUBLE型のカラムはINSERTもSELECTも全てDOUBLE型で行われるので、期待通りにレコードを取り出すことができます。
どうしてもFLOAT型の値を検索で引っかけたい場合は、欲しいFLOAT型の値をDOUBLE型にキャストしたときの浮動小数点数リテラルを渡す必要があります。実用的とは言いがたいですね。

mysql> SELECT * FROMaWHERE f=0.8999999761581421;
+------+------+
| f    | d    |
+------+------+
|  0.9 |  0.9 |
+------+------+
1 row in set (0.00 sec)

まとめると、MySQLでは多くの浮動小数点演算がDOUBLE型で行われるので、FLOAT型の利用には注意が必要です。FLOAT型からDOUBLE型へのキャストの性質にピンとこない人が利用すると混乱してしまうかもしれません。

不思議な挙動(3)：同じ値に見えても別の値のことがある

さらに別の例を紹介します。テーブルbを作って2レコードをINSERTしてみました。

mysql> CREATE TABLE b (f1 FLOAT, f2 FLOAT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTObVALUES(0.9,0.8999996);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTObVALUES(0.9,0.89999996);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM b;
+------+------+
| f1   | f2   |
+------+------+
|  0.9 |  0.9 |
|  0.9 |  0.9 |
+------+------+
2 rows in set (0.00 sec)

0.9および0.9に近そうな数2種類をINSERTしてみたところ、全て0.9として表示されました。

mysql> SELECT * FROMbWHERE f1=f2;
+------+------+
| f1   | f2   |
+------+------+
|  0.9 |  0.9 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT f1+0,f2+0 FROM b;
+--------------------+--------------------+
| f1+0               | f2+0               |
+--------------------+--------------------+
| 0.8999999761581421 | 0.8999996185302734 |
| 0.8999999761581421 | 0.8999999761581421 |
+--------------------+--------------------+
2 rows in set (0.00 sec)

ところが、f1とf2が等しいレコードをSELECTしてみると1レコードしかヒットしません。﹁0.89999996﹂と﹁0.9﹂とはFLOAT型として同じ値として格納されるのですが、﹁0.8999996﹂は異なる値として解釈されることが原因です。
しかし、比較したときは異なる値であるにも関わらずSELECT * FROM bしたときの表示が同じなのは問題です。FLOAT型の値をSELECTで取り出し、取り出した値をUPDATE文で再代入した場合に元の値と変わってしまうことになりますから、混乱の原因になりかねません。
これはMySQL ソースコードstrings/dtoa.c中のmy_gcvt関数のバグだと思われます。FLOAT型の値を表示する際、my_gcvt関数の内部で表示精度を決定する処理があるのですが、FLOAT型の場合はFLT_DIG桁となります。

  res= dtoa(x, 4, type == MY_GCVT_ARG_DOUBLE ? width : MY_MIN(width, FLT_DIG),
            &decpt, &sign, &end, buf, sizeof(buf));

このFLT_DIGは﹁C言語のfloat型で精度を失わずに表現できる10進桁数﹂を意味する定数で、通常6となります。しかし、この桁数ではFLOAT型として異なる数を同じ表記にしてしまい、適切とは思えません。本来的にはFLT_DIG+2とすべきで、こうすればFLOATとして異なる値を10進でも異なる表記で表示できるはずです。
これが本当にバグなのか仕様なのかはわかりませんが、混乱の元であるのは間違いないところでしょう。
また、この問題が今まで修正されていないこと自体がFLOAT型の利用リスクだと言えるかもしれません。利用者の多さで品質を担保できることが有名OSSの強みであるはずですが、逆に言うとあまり使われていない機能ほどバグは残ってしまうわけです。FLOAT型の採用を検討する場合、他の人がどれほどFLOAT型を採用しているのか？という観点も重要かと思います。
ちなみに、このような問題はDOUBLE型では発生しません。DOUBLE型の場合は、SELECTで取り出した10進表現と内部的な表現とが1:1対応しており、内部的に異なる値であれば必ず10進表現も異なっています。

まとめ

MySQLのFLOAT型の性質および問題点を指摘しました。 ●FLOAT型で小数を扱うと丸め誤差が発生するが、そのことを知らずに採用するのは危険 ●f+0などとするとDOUBLE型として評価され、誤差を含んだ値が観測できる ●FLOAT型の値は暗黙的にDOUBLE型にキャストされることがあり、利用者がキャストの挙動を知らないと期待通りの処理を行えないことがある ●FLOAT型をSELECTした結果と内部表現とは1:1対応していないが、これはバグだと考えられる ●そもそもFLOAT型の利用実績が少ない疑いがあり、他の型ほどバグが出きっていない可能性もあるのではないか
唯一FLOAT型を採用するメリットとしてサイズが小さいことが挙げられますが、大抵の状況では上記の問題によるデメリットの方が大きいのではないでしょうか。
今回指摘した問題を回避するために下記のような案が考えられます。 ●DECIMAL型を使う ●与えられた小数を内部的には1000分率で扱うなどして、MySQL上では整数として格納する ●与えられた10進表現の小数を文字列として格納する
どれが最適かは状況次第だと思いますが、私個人は2番目の案が好みです。また、浮動小数点数に十分詳しい場合はDOUBLE型を採用してもよいでしょう。

*1:FLOAT(25)のように精度指定した場合は倍精度型になることもあります

*2:MySQLマニュアル上でこれに関する記述は見当たりませんが、実質的には仕様に近いように感じています