目的
丸め誤差を発生させずに、入力された10進数の小数部をMySQLに格納したい。
データの桁数を揃えること無くそのまま保管して、入出力の同一性テストにパスしたい。
結論
当初は入出力の結果が同じだったFloat型を勧めておりましたが、検証して下さった方によるフォローアップ記事で、 「DOUBLE型の場合は、SELECTで取り出した10進表現と内部的な表現とが1:1対応しているが、Float型はそうではない」ことが分かりました。
言い換えると、Float型をwhere句でfloat型のカラムを指定すると、selectで見える値とは異なる値で保管されるので要注意ということです。
その結果を踏まえて、下記の通り結論を改めます。
- 入力したデータの小数部の桁数を揃えること無くそのまま保管する事を優先するならば、varcharが安全です
- 与えられた小数を内部的には1000分率で扱うなどして、MySQL上では整数として格納するのがオススメ
- WHERE句で扱うならDecimal型を用いて、入出力の同一性テストは1000倍にした整数で比較するよう調整してパスさせる
この後、それぞれの型で、どのように数値が変換されるのか、まとめていきます。
結果
全てのカラムに同じ値を入力した場合、型によってどのような値に変化するかをまとめたものです。
varcharというのが、実際に入力したそのままの値です。
| id | varchar | int | float | float_decimal | double | decimal |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1.0000000000 | 1.0000000000 | 1.0000000000 |
| 2 | 1.1 | 1 | 1.1 | 1.1000000238 | 1.1000000000 | 1.1000000000 |
| 3 | 1.00 | 1 | 1 | 1.0000000000 | 1.0000000000 | 1.0000000000 |
| 4 | 1.001 | 1 | 1.001 | 1.0010000467 | 1.0010000000 | 1.0010000000 |
| 5 | 1.009 | 1 | 1.009 | 1.0089999437 | 1.0090000000 | 1.0090000000 |
| 6 | 1.1212121212 | 1 | 1.12121 | 1.1212121248 | 1.1212121212 | 1.1212121212 |
| 7 | 121212.12121 | 121212 | 121212 | 121212.1250000000 | 121212.1212100000 | 121212.1212100000 |
float型は小数部を含んで6桁までであれば、入力した値そのままに保存出来ます。
簡単に用途をまとめると、こんな感じでしょうか。
- 小数点以下桁数を揃えて正確に扱うならば、demical型またはdouble型を使う （例:緯度経度情報）
- 小数部を含んで6桁まで入力された通りに保存する用途であれば、float型を使う
- そのまま保存したい場合には、varchar型を使う
double型とdemical型との違いは次のページがとても詳しいです。
緯度経度情報などdemicalの方が望ましいのですが、double型の方がパフォーマンスは良いです。
Decimal’s declaration and functioning is similar to Double. But there is one big difference between floating point values and decimal (numeric) values. We use DECIMAL data type to store exact numeric values, where we do not want precision but exact and accurate values. A Decimal type can store a Maximum of 65 Digits, with 30 digits after decimal point.
http://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/
テストテーブル
CREATE TABLE `number` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`varchar` varchar(255) DEFAULT NULL,
`int` int(11) DEFAULT NULL,
`float` float DEFAULT NULL,
`float_decimal` float(20,10) DEFAULT NULL,
`double` double(20,10) DEFAULT NULL,
`decimal` decimal(20,10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
補足
次の型はDOUBLE型のエイリアスのため、挙動は同一です。
- DOUBLE PRECISION
- REAL
次の型はDECIMAL型のエイリアスのため、挙動は同一です。
- DEC
- NUMERIC
- FIXED