Zenn
✅

【MySQL】MySQL 8.0.17以降に非推奨となったDECIMAL型×UNSIGNEDの代替方法

2024/04/29に公開
MySQL
DB
check制約
tech

この記事について

  • MySQLにおいて、金銭データを扱う場合など、正確な精度を保持することが重要な場合にDECIMAL型を使用します。さらにデータ型の後に UNSIGNED を付けると 0 と正の数しか格納できなくなります。これらを活用することにより、不正な値を格納できないようにするわけです。しかし、MySQL 8.0.17 以降、この組み合わせは非推奨となりました。そのため、代替案が必要です。この記事ではそちらについて紹介していきます。

先に結論を1つ

  • Check制約を活用しましょう！

MySQLにおける DECIMAL型とUNSIGNEDについて

固定小数点型 (真数値) - DECIMAL、NUMERIC

DECIMAL および NUMERIC 型は真数値データ値を格納します。 これらの型は、金銭データを扱う場合など、正確な精度を保持することが重要な場合に使用されます。 MySQL では、NUMERIC は DECIMAL として実装されるので、DECIMAL に関する次の注意事項が NUMERIC にも同様に適用されます。

DECIMAL型の使い方

DECIMAL(X,Y)
  • Xには整数部分の桁数を定義します。Yには小数部分の桁数を定義します。よって、DECIMAL(5,2)と記載した場合、整数部分5桁まで、小数部分2桁までの値であれば格納可能と言うことになります。それに反する値を挿入しようものならば、エラーが出力されます。
INSERT INTO total_money (id, price) VALUES (1, 1000000);

Error synchronizing data with database
[1264] [22001]: Data truncation: Out of range value for column 'price' at row 1

数値データ型の構文

  • 公式ドキュメントより

    UNSIGNED が指定されている場合、負の値は許可されません。

    • UNSIGNEDをカラムに設定することで、負の値を使わないようにできます。それだけでなく、負の値を使わない分、正の値で扱える範囲の幅を広げることが出来ます。
      例えば、tinyint型の場合は
        TINYINT[(M)] [UNSIGNED] [ZEROFILL]
    普通サイズの整数。 符号付きの範囲は -128 から 127 です。 符号なしの範囲は 0 から 255 です。

このDECIMALとUNSIGNEDを合わせることにより、正の値を使いつつ、桁数の指定もできるようになっていました。MySQL 8.0.16までは。。。

MySQL 8.0.17以降の対策

MySQL 8.0.17 では、FLOAT、DOUBLE および DECIMAL(およびすべてのシノニム) タイプのカラムに対して UNSIGNED 属性は非推奨になりました。将来のバージョンの MySQL ではサポートされなくなる予定です。

このため、対策を考える必要があります。そこで使うのがMySQL 8.0.16で追加されたCHECK制約です。

CHECK制約に関する記事は以下でも触れているので、確認してみてください。

【MySQL】CHECK制約を使って不正なデータからテーブルを守ろう

DECIMAL型とCHECK制約の組み合わせ

  • CHECK制約を使いつつ、負の値をカラムに挿入されないようにするには以下のようなクエリを実行しましょう。

    • 変更前のテーブル定義

      CREATE TABLE total_money (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    price DECIMAL(5, 0) UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

    • 変更後のテーブル定義

      CREATE TABLE total_money (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    price DECIMAL(5, 0) NOT NULL,
    PRIMARY KEY (id),
    CHECK (price >= 0)
);

これだけです。

  • 既存のカラムに制約をつけたい場合は以下のクエリを実行しましょう。
ALTER TABLE total_money ADD CONSTRAINT CHECK(price >= 0);

これで完璧です


INSERT INTO total_money (id, price) VALUES (1, -1);

Error synchronizing data with database
[3819] [HY000]: Check constraint 'price' is violated.

懸念点

  • DECIMAL型とCHECK制約を組み合わせることで、負の数は弾くことはできました。しかし、懸念点もあります。以下のような例です。
    • payment_statusと言うカラムを追加します。
CREATE TABLE total_money (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    price DECIMAL(5, 0) NOT NULL,
    payment_status TINYINT NOT NULL,
    PRIMARY KEY (id),
    CHECK (price >= 0),
    CHECK (payment_status >= 0)
);

上記の場合、payment_statusに負の値は入りません。payment_status TINYINT NOT NULL UNSIGNEDと同じような制約はついているでしょう。しかし、TINYINT UNSIGNEDの場合は、255まで値が許容されますが、TINYINT CHECK (payment_status >= 0)では127までしか値が許容されません。以下まとめです。

設定 下限値 上限値
TINYINTのみ -128 127
TINYINT & UNSIGNED 0 255
TINYINT & CHECK (payment_status >= 0) 0 127

そのため、TINYINT & CHECK制約に変更したことによりエラーになる場合があるので、使い方には注意が必要です。

最後に

  • 非推奨になったUNSIGNEDの代替方法を紹介しました。バージョンアップに伴い、使いやすくなる機能もあれば、制約も出てくるということを学びました。今後もその観点は持って、技術選定やキャッチアップを進めてきたいと思います。

Discussion