データ型の選び方 — INT・DECIMAL・VARCHAR・DATETIME・JSON

テーブルを作ってデータを入れるでは、CREATE TABLE を書いて SHOW CREATE TABLE で答え合わせをしました。 そこでは型をほぼ既定のまま流しましたが、実務でテーブルを作るとき、時間を使うのは列の型を決めるところです。

学習者

数値は INT、文字列は VARCHAR。だいたいそれで動いてしまうので、選んでいる感覚がありません。

動きます。型を適当に決めても、アプリが正しい値だけを送っているうちは何も起きません。 問題が出るのは、正しくない値が来たときです。そこで型がどう振る舞うかが、選び方の基準になります。

型は「入るもの」ではなく「入らないもの」を決める

MySQL 8系は既定で厳格モード（STRICT_TRANS_TABLES）が有効です。型に合わない値は、警告ではなくエラーになります。

CREATE TABLE members (
  id  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  age TINYINT UNSIGNED NOT NULL
);
 
INSERT INTO members (age) VALUES (300);

ERROR 1264 (22003): Out of range value for column 'age' at row 1

age を INT にしていれば、この 300 は静かに保存されていました。人間の年齢として 300 は明らかにおかしいのに、テーブルは受け入れます。

広い型を選ぶのは安全に見えて、実際にはアプリケーションのバグが発覚する場所を後ろにずらしているだけです。

整数 — INTとBIGINTの分かれ目

MySQLの整数型は5種類あり、違いはサイズと表せる範囲だけです。

実務での目安はこうなります。

• 主キーの AUTO_INCREMENT — BIGINT UNSIGNED。8バイトを惜しむ場面はほとんどない
• 個数・回数・点数 — INT か、範囲が確実に狭いなら SMALLINT / TINYINT
• フラグ — BOOLEAN（実体は tinyint(1)）

主キーを INT UNSIGNED にすると、上限は約42億です。届かないように見えますが、AUTO_INCREMENT は削除した行の番号を再利用しないため、テーブルを作ってデータを入れるで見た欠番の分だけ、実際の行数より早く消費されます。上限に達するとこうなります。

ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

int(11) の11は桁数ではない

古い記事やダンプでは INT(11) という書き方をよく見ます。この 11 は表示幅で、保存できる桁数とは無関係です。INT(1) にしても 2147483647 は保存できます。

MySQL 8.0.17以降、この表示幅は非推奨になり、SHOW CREATE TABLE の出力からも消えました。

`price` int NOT NULL,

例外は tinyint(1) です。これは BOOLEAN として書いた列で、クライアント側が真偽値として扱うための目印になっているため、幅の表示が残っています。

UNSIGNEDの引き算

UNSIGNED は「マイナスを保存させない」制約として便利ですが、計算結果にも効きます。

CREATE TABLE items (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  stock INT UNSIGNED NOT NULL
);
INSERT INTO items (stock) VALUES (3);
 
SELECT stock - 10 FROM items;

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`myapp`.`items`.`stock` - 10)'

学習者

保存する値がマイナスにならないだけだと思っていました。計算の途中まで止まるんですか。

止まります。SELECT の式でも、UPDATE items SET stock = stock - 10 でも同じです。 在庫を減らす処理のように途中でマイナスになりうる計算を書くなら、CAST(stock AS SIGNED) - 10 のように符号ありへ変換するか、そもそも UNSIGNED を付けないかを決めておきます。

小数 — 金額をDOUBLEにしてはいけない

MySQLで小数を扱う型は3つあります。

構文： DECIMAL(M, D)

戻り値（保存される値）： 指定した桁で丸められた正確な10進数。DECIMAL(10, 2) なら整数部8桁・小数部2桁

金額を DOUBLE にすると何が起きるのか、MySQLの上で確かめられます。

SELECT 0.1 + 0.2 = 0.3;

+-----------------+
| 0.1 + 0.2 = 0.3 |
+-----------------+
|               1 |
+-----------------+

一致します。MySQLでは 0.1 のような小数リテラルが DECIMAL として扱われるためです。ここで安心してしまうのが落とし穴で、浮動小数点として計算させると結果は変わります。

SELECT 0.1e0 + 0.2e0 = 0.3e0;

+-----------------------+
| 0.1e0 + 0.2e0 = 0.3e0 |
+-----------------------+
|                     0 |
+-----------------------+

0.1e0 は指数表記の浮動小数点リテラルです。列を DOUBLE で作れば、保存された値はこちら側の世界に入ります。

文字列 — VARCHARの長さは何を決めているのか

VARCHAR(N) の N は文字数です。バイト数ではありません。VARCHAR(10) には絵文字10文字も日本語10文字も入ります。

TEXT 系は4種類あり、上限はバイト数で決まっています。

utf8mb4では1文字が最大4バイトなので、TEXT に確実に入る日本語はおよそ16,000文字です。「64KBだから6万文字」ではありません。

長さの上限は2か所にある

VARCHAR の長さを大きく取りたくなったとき、ぶつかる壁は2つです。

1つは1行の合計サイズで、65,535バイトが上限です。列を並べすぎるとテーブル作成の時点で止まります。

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.

もう1つはインデックスのキー長で、InnoDBでは3,072バイトです。utf8mb4なら768文字ぶんしかありません。

CREATE TABLE pages (
  id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(1000) NOT NULL,
  UNIQUE KEY uk_pages_slug (slug)
);

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

メールアドレスの VARCHAR(255) に UNIQUE を張れるのは、255文字 × 4バイト = 1,020バイトで上限に収まっているからです。インデックスを張る予定の文字列列は、utf8mb4なら768文字以内に収めます。

VARCHAR(255)に意味はあるのか

「VARCHAR(255) までは1バイトで済むから効率がいい」という話を見たことがあるかもしれません。これは列の最大バイト長が255以下のとき、長さの記録に1バイトしか使わないというMySQLの仕様から来ています。

utf8mb4では1文字4バイトなので、この境界は VARCHAR(63)（63 × 4 = 252バイト）です。VARCHAR(255) はとっくに2バイト側に入っており、255という数字自体に効率上の意味はありません。

とはいえ、チーム内で長さの基準を揃える値としては使えます。効率のためではなく、アプリ側のバリデーションと一致させることに意味があります。

学習者

悩むくらいなら、全部 TEXT にしてしまえば長さで困らないのでは？

そのぶん困ることが増えます。TEXT 系はインデックスを張るときに接頭辞の長さ指定（INDEX (body(100))）が必須になり、既定値も直接は書けません。

CREATE TABLE notes (
  id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body TEXT NOT NULL DEFAULT ''
);

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value

MySQL 8.0.13以降は DEFAULT ('') のように括弧を付けた式なら書けますが、VARCHAR を使えば起きない手間です。 なにより、VARCHAR(100) と書いてある列は「100文字までのものが入る」という設計の記録になります。全部 TEXT にすると、その情報が消えます。

日付と時刻 — DATETIMEとTIMESTAMP

MySQLで日時を保存する型は主に2つで、タイムゾーンの扱いが決定的に違います。

TIMESTAMP は、書き込み時にセッションの time_zone からUTCへ変換して保存し、読み出し時にそのときの time_zone へ戻します。同じ行を、接続のタイムゾーンを変えて読むと違う値に見えます。

SET time_zone = '+00:00';
SELECT created_dt, created_ts FROM logs WHERE id = 1;
SET time_zone = '+09:00';
SELECT created_dt, created_ts FROM logs WHERE id = 1;

created_dt（DATETIME）は2回とも同じ値、created_ts（TIMESTAMP）は9時間ずれた値が返ります。

学習者

勝手に変換してくれるなら TIMESTAMP のほうが便利そうに聞こえます。

便利ですが、2038年の壁と引き換えです。TIMESTAMP の上限は2038-01-19で、これは4バイトに秒数を詰めている以上動かせません。未来の日付を入れる列（契約終了日、有効期限）には使えません。

実務ではこう分けるのが無難です。

• 未来の日時を入れる可能性がある列 — DATETIME 一択
• created_at / updated_at — どちらでもよいが、プロジェクト内で混在させない
• アプリ側でタイムゾーンを扱う方針なら — DATETIME にUTCで統一して保存し、表示のときだけ変換する

日付だけなら DATE（3バイト）、時刻の長さなら TIME を使います。なお '0000-00-00' のようなゼロ日付は、MySQL 8系の既定の sql_mode（NO_ZERO_DATE）では受け付けられません。古いシステムからの移行でよく引っかかる場所です。

JSON — 正規化するほどでもないデータの置き場所

テーブル設計をしていると、必ずこういう列に出会います。項目が案件ごとに違うフォームの回答、外部APIから返ってきたレスポンス、イベントごとに中身が変わるログの詳細。 きれいに正規化しようとすると、値が3つしかない子テーブルが増えていくだけで、割に合いません。

学習者

テーブルを分けるほどでもないので、JSON.stringify() した文字列を TEXT の列に入れています。それで困ったことはないのですが。

困っていないなら、それも一つの選択です。ただ、MySQLにはJSONを保存するための専用の型（JSON 型）があり、5.7から使えます。 TEXT や LONGTEXT に文字列として入れるのと何が違うのかを知ったうえで選ぶと、あとで効いてきます。

JSON型とLONGTEXTの違い

メリットとデメリット

JSON 型を選ぶと得られるもの。

• 壊れたJSONが入らない。 アプリのバグがその場でエラーになる。TEXT だと、壊れたデータは読み出すときまで気づけない
• SQLだけで中身を取り出せる。 調査や管理画面のクエリで WHERE や SELECT に書ける。全件アプリに読み込ませてパースする必要がない
• 部分更新ができる。 1つのキーを書き換えるのに、ドキュメント全体を送り直さなくてよい
• あとから救える。 検索条件に使い始めたら、生成列に切り出してインデックスを張れる

引き換えに背負うもの。

• 原文が保持されない（上のCallout）
• 直接インデックスが張れない。 条件に使い始めた瞬間、全行スキャンになる
• スキーマがない。 キー名のtypoもnullの混入も、DBは止めてくれない。止めたいなら CHECK 制約と JSON_SCHEMA_VALID() を組み合わせる

ALTER TABLE events
  ADD CONSTRAINT chk_events_payload
  CHECK (JSON_SCHEMA_VALID('{"type":"object","required":["user_id"]}', payload));

中身を取り出す

構文： JSON_EXTRACT(json_doc, path[, path] ...)

戻り値： 該当するJSON値。パスが存在しなければ NULL

よく使うのは関数そのものではなく、短く書ける演算子のほうです。

CREATE TABLE events (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payload JSON NOT NULL
);
 
INSERT INTO events (payload) VALUES ('{"user_id": 12, "tags": ["a", "b"]}');
 
SELECT payload->>'$.user_id' AS user_id FROM events;

JSONの中身にインデックスは張れない

JSON 列に直接インデックスを作ることはできません。検索条件に使う値があるなら、生成列として取り出してからインデックスを張ります。

ALTER TABLE events
  ADD COLUMN user_id BIGINT UNSIGNED
    AS (CAST(payload->>'$.user_id' AS UNSIGNED)) STORED,
  ADD INDEX idx_events_user_id (user_id);

ここまでやるなら、と気づくところが判断の分かれ目です。

向いている例は、外部APIのレスポンスをそのまま残す、監査ログの詳細、項目が案件ごとに変わるフォームの回答など。 向いていないのは、「ユーザーの状態」「価格」「公開フラグ」のような、必ず WHERE に出てくる値です。

Node.jsから取り出すと型はどう見えるか

型の選択はSQLの中だけで完結しません。Node.jsからMySQLに接続するで使った mysql2 は、MySQLの型をJavaScriptの値に変換して返します。

学習者

DECIMAL が文字列で返ってくるのは不便では？

不便ですが、正しい挙動です。DECIMAL の正確さをJavaScriptの number は表現できないため、数値に変換した時点で、わざわざ DECIMAL にした意味が消えます。金額を扱うなら文字列のまま受け取り、計算が必要なところだけ専用のライブラリを使うか、SQL側で計算させます。

よくあるハマりどころ

金額を DOUBLE で持ち、合計だけ合わない

1件ずつ見ると正しい金額が入っているのに、SUM() の結果が請求書と1円ずれる。行数が増えるほどずれます。 DOUBLE から DECIMAL への変更は ALTER TABLE でできますが、すでに保存された誤差は戻りません。

「あとで広げればいい」で決めた型が広げられない

型の変更は ALTER TABLE です。行数が多いテーブルでは時間がかかり、外部キーで参照されていれば相手側の型も揃える必要があります。 「あとから直せる」は正しいのですが、「あとから直すのは高い」もまた正しいです。

ENUM に選択肢を足すたびに ALTER TABLE

ENUM('draft', 'published') は少ない容量で選択肢を表現できますが、値を1つ足すだけでテーブル定義の変更になります。 さらに ORDER BY status はアルファベット順ではなく定義順で並び、数値と比較すると添字（1始まり）として扱われます。選択肢が増えうるならステータス用のテーブルを作るか、VARCHAR + CHECK 制約のほうが扱いやすい場面が多くなります。

保存したJSONと、取り出したJSONが一致しない

JSON 型はキーを並べ替え、空白を削り、重複キーを落として保存します。ハッシュ値を比較する処理や、受け取った本文をそのまま検証する処理を書いていると、ここで落ちます。原文が要るなら LONGTEXT に生のまま保存し、検索用の値だけ別の列に切り出します。

JSONに入れた値で検索して、全行スキャンになる

WHERE payload->>'$.user_id' = '12' は動きますが、インデックスが使えず全行を読みます。 開発中のデータ量では気づけず、本番で表面化します。条件に使うと分かった時点で生成列に切り出します。

ちゃんと使うためのポイント

• 型は「入る値」ではなく「弾きたい値」で選ぶ。広い型はバグの発覚を遅らせる
• 主キーの AUTO_INCREMENT は BIGINT UNSIGNED。枯渇後の ALTER TABLE は高い
• INT(11) の 11 は表示幅で、桁数制限ではない（8.0.17以降は非推奨）
• UNSIGNED は計算結果にも効く。マイナスになりうる式は CAST(... AS SIGNED) を挟む
• 金額・数量・料率は DECIMAL。FLOAT / DOUBLE は誤差が許される値だけ
• VARCHAR(N) の N は文字数。インデックスを張る列はutf8mb4で768文字まで
• TIMESTAMP は2038年で頭打ち。未来の日時を入れる列には使わない
• DATETIME の既定は秒まで。ミリ秒が要るなら DATETIME(3) と NOW(3) を揃える
• JSON 型は保存時に検証され、SQLだけで中身を取り出せる。LONGTEXT にJSON文字列を入れるのとは別物
• ただし JSON 型は原文を保持しない。生のボディを残す必要があるなら LONGTEXT
• JSON は「条件に使わないデータ」向け。WHERE に出るなら生成列か通常の列にする

型が決まると、次に効いてくるのはその文字列が実際に何バイトで保存され、どう比較されるかです。VARCHAR(255) が1,020バイトになるのも、ABC と abc が同じ値として扱われるのも、列に設定された文字コードと照合順序が決めています。

文字列型まで決まったら、文字コードと照合順序で保存できる文字と比較規則を決めます。

アプリケーション側からこれらの型をどう受け取るかは、Node.jsからMySQLに接続するのコードとあわせて確認してください。

参考リンク

• MySQL 8.4 Reference Manual — Data Types
• MySQL 8.4 Reference Manual — The JSON Data Type
• MySQL 8.4 Reference Manual — CREATE TABLE Statement
• MySQL 8.4 Reference Manual — Server SQL Modes