【保存容量も爆速もUP！】MySQLでUNSIGNEDとSIGNEDを使い分ける秘訣

2024-07-02

MySQLにおけるUNSIGNEDとSIGNED INTの使い分け

MySQLで整数型データを扱う際、UNSIGNEDとSIGNEDというオプションを選択することができます。それぞれのオプションは、格納できる値の範囲と用途に影響を与えます。この解説では、それぞれのオプションの特徴と、適切な使い分けについて詳しく説明します。

UNSIGNEDとSIGNEDの違い

UNSIGNED: 0から最大値までの正の整数のみを格納できます。負の値は格納できません。
SIGNED: -最大値から最大値までの整数を格納できます。正の値と負の値の両方を格納できます。

それぞれのオプションが適しているケース

UNSIGNED

0以上の値のみを扱うことが確実な場合。例えば、ID、カウント値、スコアなど。
データベースの容量を節約したい場合。UNSIGNEDはSIGNEDよりも少ないメモリ領域を使用します。
符号付き演算を頻繁に行わない場合。UNSIGNEDは符号なし演算に最適化されています。

正の値と負の値の両方を扱う必要がある場合。例えば、温度、座標、損失額など。

その他の考慮事項

外部システムとの互換性: 外部システムとのデータやり取りがある場合は、そのシステムがサポートするデータ型と整合性をとる必要があります。
将来的なデータの変更可能性: 将来的に負の値が必要になる可能性がある場合は、SIGNEDを使用するのが安全です。
パフォーマンス: データベースの容量やパフォーマンスが重要な場合は、UNSIGNEDの使用を検討してください。

UNSIGNEDとSIGNEDは、それぞれ異なる特性と用途があります。データの性質、将来的な変更可能性、パフォーマンスなどを考慮し、適切なオプションを選択することが重要です。

例

以下は、それぞれのオプションの使用例です。

-- 商品IDを格納する列 (0以上の整数のみ)
CREATE TABLE products (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

-- 温度を格納する列 (正負の値)
CREATE TABLE temperatures (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  timestamp DATETIME NOT NULL,
  value FLOAT NOT NULL
);

このサンプルコードでは、以下の2つのテーブルを作成します。

products テーブル: 商品ID、商品名、価格を格納するテーブル
temperatures テーブル: 時刻と温度を格納するテーブル

productsテーブル

CREATE TABLE products (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

temperaturesテーブル

CREATE TABLE temperatures (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  timestamp DATETIME NOT NULL,
  value FLOAT NOT NULL
);

データ挿入

-- productsテーブルにデータ挿入
INSERT INTO products (name, price) VALUES ('T-Shirt', 19.99), ('Jeans', 59.99), ('Shoes', 129.99);

-- temperaturesテーブルにデータ挿入
INSERT INTO temperatures (timestamp, value) VALUES (NOW(), 22.5), (NOW() + INTERVAL '1 HOUR', 23.2), (NOW() + INTERVAL '2 HOUR', 24.1);

データ取得

-- productsテーブルの全データを取得
SELECT * FROM products;

-- temperaturesテーブルの最新10件のデータを取得
SELECT * FROM temperatures ORDER BY timestamp DESC LIMIT 10;

このサンプルコードは、以下のことを示しています。

UNSIGNEDは、0以上の値のみを格納する列に使用されています。
各テーブルの列のデータ型は、格納するデータの種類に合わせて選択されています。

補足

このサンプルコードは、あくまでもUNSIGNEDとSIGNEDの使い分けを理解するための例です。実際のアプリケーションでは、より複雑なクエリやデータ操作を行う可能性があります。
データベースの設計と開発を行う際には、データの性質、パフォーマンス、将来的な変更可能性などを考慮し、適切なデータ型を選択することが重要です。

MySQLにおけるUNSIGNEDとSIGNED INTの使い分け：その他の方法

列挙型を使用する

データの範囲が限られている場合、列挙型を使用する方が効率的かつ安全な場合があります。
例えば、曜日を表す列には、ENUM('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')のような列挙型を使用できます。

CREATE TABLE appointments (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  patient_name VARCHAR(255) NOT NULL,
  appointment_day ENUM('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun') NOT NULL,
  appointment_time TIME NOT NULL
);

チェック制約を使用する

データの値が特定の範囲内に収まっていることを保証したい場合、チェック制約を使用できます。
例えば、年齢が18歳以上であることを保証するには、次のようなチェック制約を追加できます。

ALTER TABLE users
ADD CHECK (age >= 18);

トリガーを使用する

データ挿入や更新時に、自動的に処理を実行したい場合、トリガーを使用できます。
例えば、負の値がproductsテーブルに挿入されないようにするには、次のようなトリガーを作成できます。

CREATE TRIGGER products_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative';
  END IF;
END;

関連するテーブル間のデータ整合性を保ちたい場合、外部キーを使用できます。
例えば、ordersテーブルのproduct_id列がproductsテーブルのid列を参照するように外部キーを設定できます。

CREATE TABLE orders (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);