MariaDB講座 / 数値型を選ぶ：整数・DECIMAL・FLOAT・DOUBLE
個数や識別子には整数型、桁数を決めた正確な十進数にはDECIMALを検討します。FLOATとDOUBLEは近似値であり、精度を増やしても十進小数の等価比較が必ず正確になるわけではありません。
数値の意味から型を選ぶ
負数を許すか、将来どこまで増えるか、小数部にどんな意味があるかを先に決めます。注文数は通常整数、測定温度は近似値でもよい場合があります。一方、電話番号や商品コードは数字だけでも先頭の0や表記が重要なので文字列が適します。
TINYINT、SMALLINT、INT、BIGINTは範囲を、DECIMAL(M,D)は全体桁数と小数部、FLOATとDOUBLEは許容できる誤差を基準に選びます。最小の型へ無条件に詰めることも、最大の型をすべてに使うことも設計の代わりにはなりません。計算の丸め方はROUNDとCASTで別に決めます。
整数の範囲と十進数の桁を読む
| 型 | SIGNEDの範囲 | UNSIGNEDの範囲 |
|---|---|---|
| TINYINT | −128～127 | 0～255 |
| SMALLINT | −32,768～32,767 | 0～65,535 |
| INT | −2,147,483,648～2,147,483,647 | 0～4,294,967,295 |
| BIGINT | −9,223,372,036,854,775,808～9,223,372,036,854,775,807 | 0～18,446,744,073,709,551,615 |
SIGNEDが通常の既定で、UNSIGNEDは負数をなくして正側の範囲を広げます。INT(11)の表示幅はINTの範囲を増やしません。データベースが保存できても、言語やドライバーが大きな整数を正確に保持できるとは限らないため、アプリケーション側も確認します。
DECIMAL(6,2)は全体6桁のうち小数2桁で、正の最大値は9999.99です。1.235はそのまま保存できず1.24に丸められます。符号や小数点はMに数えません。小数桁の丸めはstrictモードでも範囲超過と同じエラーとは限らず、保存値とSHOW WARNINGSを読みます。
同じ値を三つの型に入れる
接続手順でcourse_userとして接続し、shopcourseを確認します。
SELECT VERSION() AS server_version, DATABASE() AS selected_database; USE shopcourse; SELECT DATABASE() AS selected_database;
次の全体を通常例として実行します。セッションのSQLモードを保存してSTRICT_TRANS_TABLESを使うため、サーバー全体の設定は変えません。
USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE numeric_lab (
item_id INT PRIMARY KEY,
quantity TINYINT NOT NULL,
exact_value DECIMAL(6,2) NOT NULL,
float_value FLOAT NOT NULL,
double_value DOUBLE NOT NULL
) ENGINE=InnoDB;
INSERT INTO numeric_lab VALUES
(1, 10, 0.10, 0.1, 0.1),
(2, 20, 0.20, 0.2, 0.2);
SELECT SUM(exact_value) AS exact_total,
SUM(exact_value) = 0.30 AS exact_equal,
SUM(float_value) = 0.30 AS float_equal,
SUM(double_value) = 0.30 AS double_equal
FROM numeric_lab;
INSERT INTO numeric_lab VALUES (3, 127, 1.235, 1.235, 1.235);
SHOW WARNINGS;
SELECT item_id, quantity, exact_value FROM numeric_lab ORDER BY item_id;
初めの二行のexact_totalは0.30、exact_equalは1、float_equalとdouble_equalは0になります。表示上の小数の桁を暗記するのではなく、比較が真か偽かを観察します。三行目のexact_valueは1.24です。正確に保存できる型を選んでも、税や割引をどの時点で丸めるかは別の業務規則です。
範囲超過を一文ずつ試す
正常例の後、同じ接続で個別に実行します。最初はSIGNED TINYINTの上限127を超えます。
INSERT INTO numeric_lab VALUES (4, 128, 1.00, 1.0, 1.0);
次はDECIMAL(6,2)の整数部の範囲超過です。
INSERT INTO numeric_lab VALUES (4, 1, 10000.00, 1.0, 1.0);
どちらも範囲外エラーとなり、各失敗後のCOUNT(*)は3のままです。失敗文を正常ファイルの途中に混ぜないでください。strictモードを解除すると値の切り詰めと警告に変わる場合があり、元の意味が失われます。診断する文の直後にSHOW WARNINGSを読みます。
選択を見直し、接続設定を戻す
測定値なら許容誤差を定め、十進数の完全一致に依存しない比較を検討します。金額なら桁と丸め時点、数量なら範囲と増加余地を記録します。
SET SESSION sql_mode = @saved_sql_mode;
設定の復元は保存変数を持つ同じ接続で行います。切断すると変数は失われるので、新しい接続で空の保存値を適用しません。繰り返すときは対象の定義とデータを確認・保存してから、この実習表だけを削除します。
DROP TABLE shopcourse.numeric_lab;
DROP TABLEは通常のROLLBACKで戻らない操作です。productsなど、他の実習表には範囲を広げません。
