テーブル設計でカラムを追加するとき、「とりあえず VARCHAR(255) にしておこう」「ID だから INT」と直感で決めていないでしょうか。データ型の選択はストレージ容量だけでなく、インデックスの効き方や集計精度にも直結します。この記事では MySQL 8.0 を対象に、整数型・文字列型・数値型・日付型それぞれの判断基準を実例と表で整理します。
このシナリオで考える
EC サイトの簡易スキーマを題材にします。ユーザーテーブルと商品テーブルを設計するとき、各カラムにどの型を選ぶかを意識しながら読み進めてください。
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
bio TEXT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock SMALLINT UNSIGNED NOT NULL DEFAULT 0,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
型の選び方の違いが、以降のセクションで具体的に分かってきます。
整数型の選び方：TINYINT から BIGINT まで
MySQL には 5 種類の整数型があります。格納できる範囲とストレージサイズを整理しておきましょう。
| 型 | サイズ | 符号あり（SIGNED）範囲 | 符号なし（UNSIGNED）範囲 |
|---|---|---|---|
| TINYINT | 1 バイト | -128 〜 127 | 0 〜 255 |
| SMALLINT | 2 バイト | -32,768 〜 32,767 | 0 〜 65,535 |
| MEDIUMINT | 3 バイト | -8,388,608 〜 8,388,607 | 0 〜 16,777,215 |
| INT | 4 バイト | 約 -21 億 〜 21 億 | 0 〜 約 43 億 |
| BIGINT | 8 バイト | 約 ±9.2 × 1018 | 0 〜 約 1.8 × 1019 |
主キーには INT UNSIGNED（最大 約 43 億）か BIGINT UNSIGNED を選びます。SNS の投稿 ID や EC サイトの注文 ID は将来的にレコード数が膨らむため、最初から BIGINT UNSIGNED にしておくと安全です。INT の上限（約 21 億）は意外と早く到達します。
一方、在庫数・ステータスコード・フラグ値など値域が決まっているカラムには、TINYINT や SMALLINT を積極的に使いましょう。ストレージの節約だけでなく、「このカラムに 255 以上は入らない」という設計意図をコードで示せます。
-- 主キーはUNSIGNEDで正の範囲を最大活用
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-- 在庫数は0〜65535で十分
stock SMALLINT UNSIGNED NOT NULL DEFAULT 0,
-- フラグはTINYINT(1)が慣例。BOOLEANは内部でTINYINT(1)に変換される
is_active TINYINT(1) NOT NULL DEFAULT 1
文字列型の選び方：CHAR・VARCHAR・TEXT
文字列型を選ぶ判断軸は「長さが固定かどうか」と「インデックスを貼るかどうか」の 2 点です。
| 型 | 最大長 | ストレージ | インデックス | 向いているデータ |
|---|---|---|---|---|
| CHAR(n) | 255 文字 | 常に n バイト（右スペース埋め） | 可 | 国コード・固定フォーマット |
| VARCHAR(n) | 65,535 バイト | 実データ長 + 1〜2 バイト | 可 | メールアドレス・商品名 |
| TEXT | 65,535 バイト | 実データ長 + 2 バイト（行外保存あり） | プレフィックス指定のみ | ブログ本文・コメント |
| MEDIUMTEXT | 約 16 MB | 実データ長 + 3 バイト | プレフィックス指定のみ | 長文ドキュメント |
最も迷うのが VARCHAR と TEXT の使い分けです。VARCHAR はインデックスをそのまま貼れますが、TEXT は INDEX (col(100)) のようにプレフィックス長を指定しなければなりません。WHERE 句や UNIQUE 制約で使うカラム（メールアドレス・スラッグ・タグ名）は VARCHAR を選んでください。
検索しない長文（記事本文・コメント・説明文）には TEXT が適します。TEXT は行外にデータを保存できるため、1 行あたりのサイズ上限（65,535 バイト）を圧迫しにくい利点もあります。
CHAR は「必ず同じ長さになる」データ専用です。ISO 2 桁の国コード（JP、US）や固定長コードに CHAR(2) を使うと、パディング処理が均一になり比較が高速になります。
-- 検索・UNIQUE制約があるのでVARCHAR
email VARCHAR(255) NOT NULL UNIQUE,
-- 長文でインデックス不要なのでTEXT
body TEXT,
-- 常に2文字なのでCHAR(2)
country_code CHAR(2) NOT NULL DEFAULT 'JP'
数値型：DECIMAL vs FLOAT vs DOUBLE
小数値には精度の要件に応じて型を選びます。最大の違いは「正確な値か、近似値か」です。
| 型 | サイズ | 精度 | 向いている用途 |
|---|---|---|---|
| FLOAT | 4 バイト | 約 7 桁（近似値） | センサーデータ・科学計算 |
| DOUBLE | 8 バイト | 約 15 桁（近似値） | 高精度が必要な近似計算 |
| DECIMAL(M, D) | 可変 | M 桁・小数点以下 D 桁（正確） | 金額・税率・重量 |
FLOAT と DOUBLE は近似値です。浮動小数点演算の誤差で 0.1 + 0.2 = 0.30000000000000004 のようなズレが生じます。金融系の計算には絶対に使ってはいけません。
DECIMAL(M, D) の M は全桁数、D は小数点以下の桁数です。価格カラムの定番は DECIMAL(10, 2) で、最大 99,999,999.99 まで正確に保持します。消費税率なら DECIMAL(5, 4)（例: 0.1000）が適切です。
-- 金額はDECIMAL(10, 2)が定番
price DECIMAL(10, 2) NOT NULL,
-- 消費税率（0.0000〜9.9999）
tax_rate DECIMAL(5, 4) NOT NULL DEFAULT 0.1000,
-- センサーの気温ならFLOATで十分
temperature FLOAT
日付・時刻型の選び方：DATE・DATETIME・TIMESTAMP
日付型で特に迷うのが DATETIME と TIMESTAMP の使い分けです。2 つの決定的な違いはタイムゾーン変換の有無と格納できる日付の範囲にあります。
| 型 | サイズ | 範囲 | タイムゾーン変換 | 典型的な用途 |
|---|---|---|---|---|
| DATE | 3 バイト | 1000-01-01 〜 9999-12-31 | なし | 誕生日・契約日 |
| DATETIME | 5 バイト | 1000-01-01 〜 9999-12-31 | なし | イベント開催日時 |
| TIMESTAMP | 4 バイト | 1970-01-01 〜 2038-01-19 | UTC ↔ セッション TZ | 作成日時・更新日時 |
| TIME | 3 バイト | -838:59:59 〜 838:59:59 | なし | 所要時間・営業時間 |
TIMESTAMP は INSERT 時に UTC へ変換して保存し、SELECT 時にセッションのタイムゾーンへ変換して返します。created_at や updated_at のような「記録した瞬間の UTC 時刻を保持したい」カラムに向いています。また ON UPDATE CURRENT_TIMESTAMP を使えば更新時刻を自動記録できます。
ただし TIMESTAMP には 2038 年問題があります。2038 年 1 月 19 日以降は値を格納できなくなるため、長寿命サービスの設計では DATETIME を検討しましょう。DATETIME はタイムゾーン変換を行わないため、アプリケーション側で統一して UTC を渡す運用が前提になります。
-- 生年月日は時刻不要なのでDATE
birthday DATE,
-- イベント日時は特定TZの日時なのでDATETIME
event_start DATETIME NOT NULL,
-- 作成・更新日時はTIMESTAMPでUTC保存 + 自動更新
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
よくある落とし穴と注意点
データ型選択でよく見かける失敗を 3 点まとめます。
- VARCHAR の長さを大きく取りすぎる:
VARCHAR(255)とVARCHAR(50)の通常の検索速度はほぼ変わりませんが、インデックス長の上限（デフォルト 767 バイト）に引っかかる場合があります。実際の最大長に合わせた設定が推奨です。 - 金額カラムに FLOAT を使う: 小さな誤差が集計時に積み重なり、帳票の合計が合わなくなります。金額には必ず DECIMAL を使ってください。
- 主キーを INT にして後悔する: INT UNSIGNED の上限 約 43 億は、アクティブユーザーが多いサービスでは数年で到達します。AUTO_INCREMENT が上限に達すると新規 INSERT が Duplicate entry エラーになるため、成長が見込まれるテーブルの主キーは最初から BIGINT UNSIGNED にしておきましょう。
まとめ
MySQL のデータ型選択は「今の用途に対して最小限の型を選ぶ」が基本方針です。整数型は上限に余裕を持ちつつ無駄に大きくしない、文字列型はインデックスの有無で VARCHAR と TEXT を使い分ける、金額は必ず DECIMAL を使う、日付時刻はタイムゾーン変換の有無で DATETIME と TIMESTAMP を選ぶ。この 4 つの軸を押さえておくと、設計時の迷いが大きく減ります。
参考リンク
- MySQL 8.0 リファレンス: データ型
- MySQL 8.0 リファレンス: 整数型
- MySQL 8.0 リファレンス: CHAR と VARCHAR 型
- MySQL 8.0 リファレンス: DATETIME と TIMESTAMP 型
- MySQL 8.0 リファレンス: 精度計算（DECIMAL）
アイキャッチ画像: Photo by Hanna Morris on Unsplash
