どうも、シローです。
今回は、小数を表す列にFLOAT型は使わない方がいいよというお話です。
入力した値とは違う値が格納される
FLOAT型に値を追加してみます。
|
1 |
INSERT INTO Accounts(HourlyRate) VALUES (59.950000000000145); |
追加したレコードを表示してみます。
|
1 2 3 4 5 6 7 |
SELECT * FROM test_database.Accounts; +------------+ | HourlyRate | +------------+ | 59.95 | +------------+ 1 row in set (0.01 sec) |
59.95以降のデータが省略されてます。
次に値を1000000000倍してみます。
|
1 2 3 4 5 6 7 |
SELECT HourlyRate * 1000000000 FROM test_database.Accounts; +-------------------------+ | HourlyRate * 1000000000 | +-------------------------+ | 59950000762.93945 | +-------------------------+ 1 row in set (0.00 sec) |
実際に挿入した値（59.950000000000145）の1000000000倍ではないですね。
このように、少数部分に関しては実際に挿入した値通りにはならないことがわかります。
なぜ、そのまま格納されないのか
コンピュータでは全ての値がON/OFFで表現されます。
そしてONを1、OFFを0と変換して1,0の羅列になった数字を2進数と言います。
データベースに格納する際にも全ての値は2進数に変換されます。
そして、挿入した59.950000000000145という値は0 ~ 9の羅列で表現される10進数と言い、10進数を2進数で表現した形式を浮動小数点数と言います。
この浮動小数点数についてですが困ったことに全ての少数を2進数で表現できるという訳では無いのです。
そのため、格納する前に2進数で表現できるように少数部分を少し変更する必要があるのです。
今回挿入した59.950000000000145が上手く2進数で表現できるように59.95000076293945.....のような値に変わってしまった訳です。
FLOAT型の列をWHEREで絞り込むときの注意点
DBに入っている値をそのまま比較対象に扱ってもデータは取得できません。
|
1 2 |
SELECT HourlyRate FROM Accounts WHERE HourlyRate = 59.95; Empty set (0.01 sec) |
取得するにはABS関数を使うと良いです。
|
1 2 3 4 5 6 7 |
SELECT HourlyRate FROM Accounts WHERE ABS(HourlyRate - 59.95) < 0.000001; +------------+ | HourlyRate | +------------+ | 59.95 | +------------+ 1 row in set (0.00 sec) |
うーん、直接使えないのは不便ですね。。
解決策：NUMERIC型を使う
NUMERIC型では格納する値の内、整数部分と少数部分を何桁までかを定義することができます。
|
1 2 3 |
CREATE TABLE Accounts ( HourlyRate NUMERIC(9,2) ); |
NUMERIC(X,Y) ... Xが整数部分、Yが少数部分です。
少数部分を超えた値は全て切り捨てて格納されます。
|
1 |
INSERT INTO Accounts (HourlyRate) VALUES (59.950000000000145); |
格納した値は59.95までになるので、WHEREで絞り込むときにもその値をそのまま使用できます。
|
1 2 3 4 5 6 7 |
SELECT HourlyRate FROM Accounts WHERE HourlyRate = 59.95; +------------+ | HourlyRate | +------------+ | 59.95 | +------------+ 1 row in set (0.00 sec) |
ちなみに整数部分の桁を超える値は格納できません。
|
1 2 |
INSERT INTO Accounts (HourlyRate) VALUES (999999999999.99); ERROR 1264 (22003): Out of range value for column 'HourlyRate' at row 1 |
要するに
- 少数部分で超えた部分は切り捨て
- 整数部分で超えた桁数は格納できない
という感じです。
このNUMERIC型を定義するときには、格納する値の範囲を仕様として明確にする必要が出てきますが、
格納したデータと実態のズレを防ぐことが可能です。
まとめ
- 全ての少数はそのままDBに格納できる訳ではなく、場合によっては格納できるように値を変更している
- 格納した
FLOAT型のデータをWHEREでの絞り込みではそのまま扱っても期待したデータを取得できない - 少数を含む数値を格納する列は
NUMERIC型にすれば少数部分で定義した少数部分までのデータを格納することができ、はみ出した値は切り捨てられる
本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗（アンチパターン）があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。