💨

MySQLの小数値型　詳解

2023/09/01に公開

MySQL

tech

研修医をしているgorogoroumaruと申します。
普段は病院で働いていますが、最近ソフトウェアエンジニアへの転職活動を始めました。転職オファー等あればいつでもご連絡ください。

この記事の対象者

MySQLのデータ型を知ってdisk効率の良いスキーマ設計をしたい方
MySQLで各データ型がどのようにdisk上に格納されているか興味のある方

MySQLの小数値型

MySQLには小数点数を格納できる4つの異なるデータ型が存在します。

DECIMAL：　正確な値を格納する固定精度データ型です。
NUMERIC：　DECIMALの別名であり、MySQLでは同じものです。
FLOAT：　おおよその値を格納する浮動小数点数データ型です。
DOUBLE：　FLOATよりも大きな、またはより正確な値を格納することのできる浮動小数点数データ型です。

これらのデータ型はそれぞれ異なるユースケースがあり、必要な精度に応じて適切なデータ型を決定することが重要です。

小数値型のデータサイズ

FLOAT ４バイト
DOUBLE 8バイト
DECIMAL 可変長

Decimalのデータサイズ計算について

整数部と小数部は分けて格納されます。 整数部、小数部ともに９桁ごとに４バイト消費されます。桁数が９で割り切れない場合は以下の表に従って追加のバイト数が決まります。

たとえば、DECIMAL(18,9)の場合は小数点の両側に9桁あるため、整数部と小数部のそれぞれに4バイトが必要となり、計8バイト消費されます。DECIMAL(20,6)の場合は整数部に14桁、小数部に6桁あります。整数部の桁は、そのうちの9桁に4バイト、残りの5桁に3バイトが必要です。小数部の6桁には3バイトが必要であり、計１０バイト消費されます。

余りの桁	バイト数
0	0
1-2	1
3-4	2
5-6	3
7-9	4

基本的にDECIMALは消費サイズが大きいので、正確な演算が必要でなければFLOAT, DOUBLEで格納するのが良いでしょう。

小数値型のユースケース

Decimal

通貨やその他の金融データなど、絶対的な精度を必要とする値を格納する必要がある場合、DECIMALデータ型を使用するべきです。DECIMALでは小数を10進数として格納するため誤差が発生しません。

FLOATまたはDOUBLE

正確な小数値が必要でない場合、FLOATまたはDOUBLEを使用できます。これらのデータ型は小数を浮動小数点数で格納するため丸め誤差が発生します。
DOUBLEはFLOATよりも大きく、より正確な値を格納できます。

絶対的な精度は不要で相対的な精度があれば問題ない場合はFLOATやDOUBLEが選択肢となります。

例

これらのデータ型がどのように動作するかを見てみましょう。doublesという名前のテーブルを作成し、列D1とD2にデータを挿入します。両列ともDOUBLEデータ型として定義します。

CREATE TABLE doubles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  D1 DOUBLE,
  D2 DOUBLE,
);

次にデータを挿入します。

INSERT INTO doubles (D1, D2)
VALUES (100.4, 20.4), (-80.0, 0.0);

以下のselect文を実行します。

SELECT * FROM doubles;

結果は以下のようになります。

| id | D1 | D2 |
|----|----|----|
| 1  | 100.4 | 20.4 |
| 2 | -80.0 | 0.0 |

ではこのデータに対して列ごとに和を計算しましょう。

SELECT SUM(D1), SUM(D2) FROM doubles;

以下の結果が得られます。

| SUM(D1)           | SUM(D2)    |
|-------------------|------------|
| 20.4000000000006  | 20.4       |

正確な計算結果はどちらも20.4となるはずですが、列D1の和に関しては丸め誤差により僅かに誤差が発生しています。

ではDECIMALで定義した場合も試してみましょう。
以下のように整数部3桁、小数点１桁分の１０進小数として定義します。

CREATE TABLE decimals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  D1 DECIMAL(4,1),
  D2 DECIMAL(4,1),
);

先ほどと同じデータを挿入します。

INSERT INTO decimals (D1, D2)
VALUES (100.4, 20.4), (-80.0, 0.0);

先ほどと同様に列ごとに和を取ります。

SELECT SUM(D1), SUM(D2) FROM decimals;

以下の結果が得られます。

| SUM(D1) | SUM(D2) |
|---------|---------|
| 20.4    | 20.4    |

正確な値が算出されていることが確認できます。

小数値格納の代替手段と限界

DECIMAL型は正確な値を格納、演算できますが、データサイズが大きいというデメリットがあります。データサイズを小さく保ったまま正確な値を格納する代替手段として以下のような手法が考えられます。

数値の小数部分が特定の桁数に収まる場合に１０の累乗をかけた状態で整数として格納する

例えば、数値が小数第3位までの精度で表現されると仮定し、1.375という数値を $10^3$ をかけて整数の1375として格納する方法があります。小数を整数として格納することで、データサイズを節約しながらも正確な演算が可能です。しかし、10の累乗をかけてカラムに格納した場合、そのカラム同士で演算を行う際には加減算には問題がありませんが、乗除の場合には注意が必要です。

具体的に、実際の数値が1.275と2.346の乗算を考えてみましょう。データベース上ではそれぞれ1275と2346という整数で表現されていますが、これらを単純に掛け算すると1275 * 2346 = 2991150という値が得られます。しかし、この結果は実際の計算結果である2.99115に $10^6$ をかけたものであり、実際の数値に変換するためには指数を適切に調整する必要がありコードの複雑性が増す可能性があります。

与えられた10進表現の小数を文字列として格納する

文字列として格納することにより、等価演算子による比較が可能となります。
この方法は、FLOATやDOUBLEを使用した場合に発生する丸め誤差による等価演算の問題を回避できます。
ただし、文字列として格納するため、四則演算などの計算を行うことはできなくなります。

まとめ

MySQLで小数値を扱う際には、必要に応じて適切なデータ型を選択することが重要です。絶対的な精度が必要な場合は、DECIMALが最適な選択肢です。正確な値が必要でない場合は、FLOATまたはDOUBLEが適しているでしょう。

FLOATまたはDOUBLEを使用する際には、結果が完全に正確でないこと、値が少し予想と異なる可能性があることを覚えておいてください。