MySQLのデータ型選び — FLOATで金額は1円ずれる

MySQLのデータ型選び。文字と数値のカードを分けた収納箱、計算機と定規。

FLOATで金額を持つと1円ずれます。MySQLのデータ型を、用途から選べるようにします。

前回: SELECTの実行順序とNULL — WHEREで別名が使えない これは【深掘り】です。本線の順路ではありません。 本線はMySQLの導入とSQLの基本|テーブル作成とSELECT・INSERTの回から先へ進んでいます。この記事はその回の続きで、「列にどの型を選ぶか」だけを1本かけて掘り直すものです。飛ばしても本線は繋がります。テーブルを作る手が止まったときに戻ってきてください。

作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて、appdb というデータベースがある状態から始めます。

CREATE TABLE を書くとき、こう考えていました。

  • 数字だから INT
  • 文字だから VARCHAR
  • 長さは……とりあえず 255

これで動きます。 動くので、しばらく何も起きません。

問題が出るのは、ずっと後です。

  • 郵便番号を INT で持ったら、0123456 が 123456 になっていた(先頭の0が消えた)
  • 金額を FLOAT で持ったら、合計が1円合わない。何度数えても合わない
  • TINYINT の在庫数に 200 を入れようとしたら、Out of range で止まった
  • 状態を ENUM にしたら、選択肢を1つ増やすだけで表全体が作り直され、その間サービスが止まった
  • 電話番号を VARCHAR で持って WHERE tel = 09012345678 と書いたら、動くけれど猛烈に遅くなった

この6つは、全部「型の選び方」1つから出ています。

そして、いちばん怖いのは最初の2つです。

型を間違えると、エラーが出ないことがあります。 エラーが出ずに、値のほうが黙って変わります。

0123456 を INT の列に入れても、MySQL は何も文句を言いません。数値として正しいからです。0 は数値の先頭では意味を持たないので、親切に落としてくれるだけです。あなたが「これは番号であって数ではない」と思っていることを、データベースは知りません。

型とは、「入るか入らないか」の話ではありません。 「データベースに、この列を何だと思わせるか」の宣言です。数だと思わせれば数として扱われ、文字だと思わせれば文字として扱われる。そこがズレたときに、値が黙って書き換わります。

この記事は、そのズレが起きる場所を、全部その場で起こして見にいきます。

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • 型を間違えると、エラーではなく値のほうが変わること。0123456 を INT に入れると 123456 になるのは、バグではなく仕様だということ
  • 整数型には範囲があり、UNSIGNED を付けると上限がちょうど倍になること。その代わり、負の数を入れた瞬間にエラーになること
  • FLOAT と DOUBLE で金額を扱ってはいけないこと。0.1 + 0.2 が 0.3 にならないのを、SQLでその場で確かめられること。代わりに使うのが DECIMAL だということ
  • MySQL に本物の論理型は無いこと。BOOLEAN は TINYINT(1) の別名で、2 が平気で入ること
  • AUTO_INCREMENT の番号は、ロールバックしても戻らないこと。だから連番として数えてはいけないこと
  • CHAR と VARCHAR は、後ろの空白の扱いが違うこと。TEXT には索引がそのまま張れないこと
  • ENUM は文字列に見えて、中身は番号だということ。WHERE status = 1 が通ってしまうこと
  • WHERE 文字列列 = 1 と書くと、その列の索引が丸ごと効かなくなること。WHERE 数値列 = '1' は効くこと。逆ではない
  • JSON 型は便利だが、そのままでは索引が張れないこと。生成列を1本出して、そこに張ること
  • NULL を許すかどうかは、後から直すのがいちばん高いこと。既定は NOT NULL にすること
  • 型の変更は、大きな表ではテーブルを丸ごと作り直すこと。その間、書き込みが止まること

この記事でやること

  1. 練習用のテーブルを6つ作る(d_type d_seq d_str d_enum d_conv d_json)。本線の tasks にも、深掘りの d_emp にも触らない
  2. TINYINT に 128 を入れて Out of range を出す。 UNSIGNED に -1 を入れて、もう一度出す
  3. DOUBLE の列に 0.1 と 0.2 を入れて合計し、0.3 にならないのを見る。 同じことを DECIMAL でやって、合うのを見る
  4. CHAR と VARCHAR に同じ文字列を入れて、返ってくる長さが違うのを見る。 TEXT に索引を張ろうとして弾かれる
  5. ENUM を数値と比較して、通ってしまうのを見る。 選択肢を途中に挟む ALTER が弾かれるのも見る
  6. 1000行の表を作り、EXPLAIN で「索引が使われている行」と「使われていない行」を見比べる
  7. JSON から生成列を1本出して索引を張り、EXPLAIN が変わるのを確かめる
  8. 練習用テーブルを片づける

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
データ型列に「これは何か」を宣言するもの。 数なのか、文字なのか、日付なのか
桁あふれ(オーバーフロー)その型に入る範囲を超えること。 入れ物より中身が大きい状態
UNSIGNED(符号なし)負の数を扱わないという宣言。マイナス側を捨てて、その分プラス側を倍にする
浮動小数点数小数点の位置を動かして、広い範囲の数を近似で持つ方式。FLOAT DOUBLE。速いが誤差が出る
固定小数点数小数点以下の桁数を固定して、正確に持つ方式。DECIMAL。遅いが誤差が出ない
丸め誤差正確に書けない数を、近い値に丸めたときに出るズレ。積み重なると目に見える
AUTO_INCREMENT行を足すたびに、自動で1ずつ増える番号を振る仕組み
固定長 / 可変長常に同じバイト数を使うか、中身に応じて伸び縮みするか。CHAR が固定長、VARCHAR が可変長
暗黙の型変換型が違うもの同士を比べたとき、データベースが勝手に片方を変換すること。何も言わずにやる
厳格モード(strict mode)入らない値を入れようとしたら、警告で済ませずエラーにする設定。MySQL 8.0 は既定でON
索引(インデックス)「この値ならこの行」の早見表。 本の巻末の索引と同じ
一時テーブル並べ替えや集計のために、データベースが裏で勝手に作る作業用の表
生成列他の列から計算して作られる、書き込めない列。 式を1回書いておくと、以降は普通の列のように使える
ALTER TABLEすでにある表の形を変えるコマンド。 列を足す、型を変える、索引を張る
ロック「いま触っているから待って」という札。 掛かっている間、他の処理は待たされる

① 練習用のデータを作る

触るのは d_ で始まる表だけにする

本線で作った tasks にも、SELECT の深掘りで作った d_emp / d_dept にも、一切触りません。

このシリーズでは、練習用テーブルをすべて d_ で始めることにしています(d は deep のつもりです)。この記事で作るものは、この記事の最後で全部消します。

管理者で入る

表を作るので、appuser ではなく管理者で入ります。

bash
$ sudo mysql

appuser には CREATE TABLE の権限を渡していません(本線の回でわざと外しました)。appuser で入ると、次の行で止まります。

ERROR 1142 (42000): CREATE command denied to user 'appuser'@'localhost'

これは正常です。 権限が効いている証拠なので、appuser に CREATE を足したりしないでください。

USE appdb;

表を6つ作る

全部そのまま貼ってください。 途中まででも動きますが、後ろの節で足りなくなります。

DROP TABLE IF EXISTS d_json;
DROP TABLE IF EXISTS d_conv;
DROP TABLE IF EXISTS d_enum;
DROP TABLE IF EXISTS d_str;
DROP TABLE IF EXISTS d_seq;
DROP TABLE IF EXISTS d_type;

-- ① 数値の型を横に並べた表
CREATE TABLE d_type (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  memo      VARCHAR(30)  NOT NULL,
  t_tiny    TINYINT,
  t_tiny_u  TINYINT UNSIGNED,
  t_int     INT,
  t_big     BIGINT,
  t_float   FLOAT,
  t_double  DOUBLE,
  t_decimal DECIMAL(15,2),
  t_bool    BOOLEAN
);

-- ② AUTO_INCREMENT の穴を見る表
CREATE TABLE d_seq (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  memo VARCHAR(30)  NOT NULL
);

-- ③ 文字列の型を横に並べた表
CREATE TABLE d_str (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  s_char CHAR(10),
  s_var  VARCHAR(10),
  s_text TEXT,
  zip_i  INT,
  zip_s  CHAR(7)
);

-- ④ ENUM の罠を見る表
CREATE TABLE d_enum (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title  VARCHAR(30) NOT NULL,
  status ENUM('todo','doing','done') NOT NULL DEFAULT 'todo'
);

-- ⑤ 暗黙の型変換を EXPLAIN で見る表(あとで1000行入れます)
CREATE TABLE d_conv (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  num_code INT         NOT NULL,
  str_code VARCHAR(20) NOT NULL,
  KEY ix_num (num_code),
  KEY ix_str (str_code)
);

-- ⑥ JSON 型の表
CREATE TABLE d_json (
  id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payload JSON NOT NULL
);

DROP TABLE IF EXISTS を先に置いてあるのは、何度でもやり直せるようにするためです。打ち間違えたら、この塊をもう一度貼れば元に戻ります。

作れたか確認します。

SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| d_conv          |
| d_dept          |
| d_emp           |
| d_enum          |
| d_json          |
| d_seq           |
| d_str           |
| d_type          |
| tasks           |
+-----------------+

tasks が残っていることを、必ず目で確かめてください。

② 数値の型

整数は5種類あって、範囲が違うだけ

MySQL の整数型は5つです。違いは「何バイト使うか」だけで、それがそのまま入る範囲になります。

型バイト数符号あり(既定)UNSIGNED
TINYINT1-128 〜 1270 〜 255
SMALLINT2-32,768 〜 32,7670 〜 65,535
MEDIUMINT3-8,388,608 〜 8,388,6070 〜 16,777,215
INT4-2,147,483,648 〜 2,147,483,6470 〜 4,294,967,295
BIGINT8約 ±9.2×10^180 〜 約 1.8×10^19

覚えるのは INT の1行だけで十分です。

INT の上限は、約21億です。

この「21億」は、実務で本当に当たります。 1秒に1件のログを入れる表なら68年もちますが、アクセスログや配信履歴のように1日に数百万件入る表なら、数年で尽きます。 尽きた瞬間、INSERT が全部止まります。

UNSIGNED を付けると、上限がちょうど倍になります。 マイナス側を丸ごと捨てて、その分をプラス側に回すからです。IDや個数のように、負にならないと分かっているものには付けます。

わざと桁あふれさせる

言葉で読んでも身につかないので、その場で壊します。

INSERT INTO d_type (memo, t_tiny, t_tiny_u) VALUES ('境界ちょうど', 127, 255);
Query OK, 1 row affected (0.01 sec)

ここまでは入ります。1つだけ増やします。

INSERT INTO d_type (memo, t_tiny) VALUES ('1つ超える', 128);
ERROR 1264 (22003): Out of range value for column 't_tiny' at row 1

128 が入りませんでした。 TINYINT は1バイト、つまり256通りしか表せず、そのうち半分をマイナスに使っているので、プラス側は127で終わりです。

UNSIGNED のほうにマイナスを入れてみます。

INSERT INTO d_type (memo, t_tiny_u) VALUES ('マイナス', -1);
ERROR 1264 (22003): Out of range value for column 't_tiny_u' at row 1

同じエラーです。 UNSIGNED は「上限が倍になるおまけ」ではなく、「負の数はこの列に入れない」という宣言です。倍になるのは、その結果にすぎません。

[スクリーンショット: 01_out_of_range.png] INSERT INTO d_type (memo, t_tiny) VALUES ('1つ超える', 128); を打って ERROR 1264 が返っている画面を撮ってください。

ERROR 1264 で止まってくれるのは、MySQL 8.0 が既定で厳格モードだからです。 厳格モードを切ると、このエラーは警告に変わり、128 は 127 として保存されます。 つまり値が黙って変わります。 後ろの「厳格モード」の節で実際にやって見せますが、この設定は、ふだんは絶対に切らないでください。

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

INT(11) という書き方を、古いスキーマでよく見ます。 私は最初、「11桁まで入る」という意味だと思っていました。違います。

カッコの中は「表示するときに何桁ぶんの幅を取るか」というだけの数字で、入る範囲には1ミリも影響しません。 INT(1) でも21億まで入ります。

MySQL 8.0.17 以降、この表示幅は非推奨になり、SHOW CREATE TABLE でも表示されなくなりました。 新しく書くときは、カッコを付けません。

-- ❌ 意味がない
quantity INT(11) NOT NULL

-- ✅ こう書く
quantity INT UNSIGNED NOT NULL

唯一の例外が TINYINT(1) です。 これだけは意味を持ってしまっていて、後の節で出てきます。

FLOAT と DOUBLE で金額を扱わない

ここがこの記事でいちばん重要な節です。

まず、素直に打つと、正しく出ます。

SELECT 0.1 + 0.2 AS sum, 0.1 + 0.2 = 0.3 AS is_equal;
+------+----------+
| sum  | is_equal |
+------+----------+
| 0.3  |        1 |
+------+----------+

合っています。 これが厄介なところで、「MySQLは大丈夫らしい」と思ってしまいます。

合った理由は、MySQL が 0.1 という書き方を DECIMAL として読んでいるからです。浮動小数点数ではありません。

わざと浮動小数点数にして、同じことを打ちます。 末尾に E0 を付けると、MySQL はそれを DOUBLE として扱います。

SELECT 0.1E0 + 0.2E0 AS sum, 0.1E0 + 0.2E0 = 0.3E0 AS is_equal;
+---------------------+----------+
| sum                 | is_equal |
+---------------------+----------+
| 0.30000000000000004 |        0 |
+---------------------+----------+

0.1 + 0.2 が 0.3 になりませんでした。 そして = 0.3 が偽になりました。

バグではありません。 FLOAT と DOUBLE は、数を2進数の小数で持ちます。 ところが 0.1 は、2進数では割り切れません。

10進数で 1 ÷ 3 を書こうとすると 0.3333... が終わらないのと、まったく同じことです。どこかで打ち切るしかなく、打ち切った瞬間にわずかなズレが生まれます。 そのズレを2つ足したのが、上の ...004 です。

表の列でも同じことをやる

リテラルだけの話ではないことを確かめます。

INSERT INTO d_type (memo, t_float, t_double, t_decimal) VALUES
  ('0.1', 0.1, 0.1, 0.1),
  ('0.2', 0.2, 0.2, 0.2);

合計します。

SELECT SUM(t_double)  AS double_sum,
       SUM(t_decimal) AS decimal_sum,
       SUM(t_double)  = 0.3E0 AS double_eq,
       SUM(t_decimal) = 0.3   AS decimal_eq
FROM   d_type
WHERE  memo IN ('0.1', '0.2');
+---------------------+-------------+-----------+------------+
| double_sum          | decimal_sum | double_eq | decimal_eq |
+---------------------+-------------+-----------+------------+
| 0.30000000000000004 |        0.30 |         0 |          1 |
+---------------------+-------------+-----------+------------+

同じ表の、同じ行から取っています。 違うのは列の型だけです。DECIMAL の列だけが合っています。

[スクリーンショット: 02_float_sum.png] 上の SELECT を実行して、double_sum が 0.30000000000000004 になっている画面を撮ってください。

FLOAT は、表示すら信用できない

もう1つ、もっと気持ちの悪いものを見ます。

SELECT t_float,
       t_float  = 0.1E0 AS float_eq,
       t_double = 0.1E0 AS double_eq
FROM   d_type
WHERE  memo = '0.1';
+---------+----------+-----------+
| t_float | float_eq | double_eq |
+---------+----------+-----------+
|     0.1 |        0 |         1 |
+---------+----------+-----------+

画面には 0.1 と出ているのに、= 0.1 が偽です。

FLOAT は4バイトしかなく、DOUBLE(8バイト)よりさらに粗いです。入れた 0.1 は、FLOAT の中では 0.100000001490116... あたりの値になっています。表示のときだけ「まあ 0.1 でしょう」と丸めて見せてくれているので、目で見ても気づけません。

だから、こういう事故が起きます。

  • 合計が 1円だけ 合わない。何度数えても合わない
  • WHERE price = 1980 が、入っているはずなのに0行
  • 同じデータを2回集計したら、末尾の桁が違う

結論は1行です。

お金・数量・比率など「合わないと困る数」に、FLOAT と DOUBLE を使ってはいけません。 使うのは DECIMAL です。

DECIMAL の書き方

price DECIMAL(15, 2) NOT NULL
  • 1つ目の数字(15)が、全部あわせた桁数。 最大65
  • 2つ目の数字(2)が、小数点以下の桁数。 最大30

DECIMAL(15,2) なら、整数部が13桁(約10兆)まで、小数が2桁です。日本円の金額なら、これで足ります。

DECIMAL は「10進数のまま、正確に持つ」型です。そのぶん DOUBLE より計算が遅く、場所も食います。でも、金額の計算で1円ずれることに比べたら、些細な差です。

では DOUBLE はいつ使うのか。 「もともと近似でよいもの」です。センサーの温度、緯度経度、機械学習の重み、統計値。元のデータ自体に誤差があるものなら、DOUBLE が正解です。 判定の基準は「1円(1単位)ずれたら誰かが困るか」です。困るなら DECIMAL。

なお、金額を「1円単位の整数」で持つ設計(BIGINT で銭やセントを持つ)もあります。 誤差は出ませんが、税率のような割り算が入った瞬間に、自分で丸めルールを書く羽目になります。 個人開発なら DECIMAL から始めて構いません。

BOOLEAN は嘘の型

MySQL には、真か偽かだけを持つ型がありません。

INSERT INTO d_type (memo, t_bool) VALUES ('真', TRUE), ('偽', FALSE), ('2を入れる', 2);

2 が、エラーも警告もなく入ります。

SELECT memo, t_bool,
       t_bool = TRUE AS eq_true,
       t_bool IS TRUE AS is_true
FROM   d_type
WHERE  memo IN ('真', '偽', '2を入れる');
+-----------+--------+---------+---------+
| memo      | t_bool | eq_true | is_true |
+-----------+--------+---------+---------+
| 真        |      1 |       1 |       1 |
| 偽        |      0 |       0 |       0 |
| 2を入れる |      2 |       0 |       1 |
+-----------+--------+---------+---------+

2 の行を見てください。

  • t_bool = TRUE が偽(TRUE はただの 1 なので、2 = 1 は偽)
  • t_bool IS TRUE が真(IS TRUE は「0以外か」を見るので、2 は真)

同じ行が、書き方によって真にも偽にもなります。

正体を見ます。

SHOW CREATE TABLE d_type\G

t_bool の行は、こう返ってきます。

  `t_bool` tinyint(1) DEFAULT NULL,

BOOLEAN と書いたのに、tinyint(1) になっています。 MySQL では BOOLEAN は TINYINT(1) の完全な別名で、TRUE は 1、FALSE は 0 というだけの定数です。

さっき「INT(11) のカッコは意味がない」と書きましたが、TINYINT(1) だけは例外です。この (1) を見て、アプリ側のドライバが「これは真偽値だ」と判断して true / false に変換します(Node.js の mysql2 にもその挙動があります)。TINYINT(4) にすると数値のまま返ることがあり、ここで挙動が変わります。

実務での書き方はこれ1つです。

is_done TINYINT(1) NOT NULL DEFAULT 0
  • NOT NULL にする。 真でも偽でもない真偽値は、あとで必ず困ります(三値論理の話はSELECT の深掘りに書きました)
  • DEFAULT 0 を付ける。 「まだ完了していない」が既定
  • 比較は = 1 = 0 か、IS TRUE / IS FALSE に統一する。 混ぜない

AUTO_INCREMENT の番号は戻らない

IDを AUTO_INCREMENT にするのは正しいのですが、この番号を「件数」や「連番」として扱うと事故ります。

実際に穴を空けます。

INSERT INTO d_seq (memo) VALUES ('1件目'), ('2件目');

ここでトランザクションを開き、入れて、取り消します。

START TRANSACTION;
INSERT INTO d_seq (memo) VALUES ('これは取り消す');
ROLLBACK;

取り消しました。行は残っていません。 その状態で、もう1件入れます。

INSERT INTO d_seq (memo) VALUES ('3件目');
SELECT * FROM d_seq;
+----+-----------+
| id | memo      |
+----+-----------+
|  1 | 1件目     |
|  2 | 2件目     |
|  4 | 3件目     |
+----+-----------+

3 がありません。 行は取り消されたのに、番号だけ使われて捨てられました。

これは不具合ではありません。 AUTO_INCREMENT の番号を配る係は、トランザクションの外にいます。 そうしないと、同時に10人が INSERT したときに、全員が番号を待って詰まるからです。速さのために、番号は使い捨てにしています。

穴が空くのは、ロールバックのときだけではありません。

  • INSERT が制約で失敗したとき(番号は消費済み)
  • INSERT ... ON DUPLICATE KEY UPDATE で更新側に転んだとき
  • DELETE で最後の行を消したとき(MySQL 8.0 では、再起動しても番号は戻りません。5.7 までは戻っていました)

だから、こう決めます。

AUTO_INCREMENT の値は「行を1つに特定するためだけの番号」です。 意味を持たせてはいけません。

  • ❌ MAX(id) を件数として使う → SELECT COUNT(*) を使う
  • ❌ 請求書番号や注文番号にそのまま使う → 「番号が飛んでいる=データを消したのでは」と疑われます。 業務上の連番は、別の列で、別に管理する
  • ❌ 「IDが小さい=古い」と決めつける → だいたい合いますが、保証はありません。順序が要るなら created_at を持つ

型の選び方としては、これです。

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

INT UNSIGNED(上限43億)でも大半の個人開発では足ります。 ただし穴が空く前提の番号なので、行数より速く減ります。 迷ったら BIGINT UNSIGNED にしておくほうが、後で ALTER TABLE に苦しまずに済みます(その ALTER がどれだけ痛いかは、後の節でやります)。

③ 文字列の型

CHAR と VARCHAR は、後ろの空白の扱いが違う

同じ文字列を、両方に入れます。

INSERT INTO d_str (s_char, s_var) VALUES ('abc   ', 'abc   ');

abc のあとに空白が3つ入っています。読み出します。

SELECT CONCAT('[', s_char, ']') AS char_val,
       CONCAT('[', s_var,  ']') AS varchar_val,
       CHAR_LENGTH(s_char) AS char_len,
       CHAR_LENGTH(s_var)  AS varchar_len
FROM   d_str;
+----------+-------------+----------+-------------+
| char_val | varchar_val | char_len | varchar_len |
+----------+-------------+----------+-------------+
| [abc]    | [abc   ]    |        3 |           6 |
+----------+-------------+----------+-------------+

CHAR に入れた末尾の空白が、消えて返ってきました。

これが CHAR の仕様です。

CHAR(n)VARCHAR(n)
保存常に n 文字ぶんの場所を取る。足りない分は空白で埋める実際の長さ + 長さを記録する1〜2バイト
読み出し末尾の空白は取り除かれる末尾の空白もそのまま返る
向いているもの長さが本当に固定のコード(国コード JP、郵便番号7桁、性別コード)それ以外ほぼ全部

「CHAR のほうが速い」と昔から言われますが、いまはほとんど差がありません。 選ぶ基準は速さではなく、「末尾の空白が消えてもいいか」です。

CHAR を選ぶ場面は、実はかなり少ないです。 「郵便番号は7桁で固定」と思っても、ハイフン入りで入ってくる日が来ます。迷ったら VARCHAR です。

比較のときは、もっとややこしい

SELECT 'abc' = 'abc  ' AS eq;

MySQL 8.0 の既定(utf8mb4_0900_ai_ci)だと、こうなります。

+----+
| eq |
+----+
|  0 |
+----+

古い照合順序(utf8mb4_general_ci など)だと、同じ式が 1 を返します。 末尾の空白を無視して比べる決まり(PAD SPACE)だったからです。MySQL 8.0 の既定はそれをやめました(NO PAD)。

自分のサーバーがどちらか、確かめておきます。

SHOW VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+

「同じ文字列かどうか」の判定が、サーバーの設定で変わります。 移行のときに、いちばん静かに壊れる場所です。アプリ側で入力の前後の空白を落としてから保存するのが、いちばん確実な予防です。

照合順序そのものの読み方は文字コードとUTF-8の回に書いたので、ここでは繰り返しません。

数値に見える文字列を、数値型にしない

冒頭で書いた郵便番号を、実際にやります。 d_str には zip_i INT と zip_s CHAR(7) を用意してあります。

INSERT INTO d_str (zip_i, zip_s) VALUES ('0123456', '0123456');
SELECT zip_i, zip_s FROM d_str WHERE zip_s IS NOT NULL;
+--------+---------+
| zip_i  | zip_s   |
+--------+---------+
| 123456 | 0123456 |
+--------+---------+

エラーも警告も出ずに、先頭の 0 が消えました。

MySQL は何も間違っていません。 数として 0123456 を読めば 123456 です。「これは番号であって数ではない」と伝えていなかったのは、こちらです。

判定の基準は1つです。

その値で、足し算や引き算をするか。 しないなら、それは数ではありません。文字列にします。

値型理由
郵便番号・電話番号VARCHAR足さない。先頭の0が意味を持つ。ハイフンも入る
製品コード・注文番号VARCHAR足さない。あとで英字が混ざる日が来る
クレジットカード番号VARCHAR(というより持たない)足さない。BIGINT でも桁が危うい
年齢・個数・点数INT足す。比べる
金額DECIMAL足す。誤差が許されない

電話番号を BIGINT で持つと、先頭の0が消えるうえに、+81 や内線の - が入った瞬間に詰みます。

TEXT は VARCHAR の大きい版ではない

「長い文章だから TEXT」で済ませていました。 これも違います。

型入る大きさ索引
VARCHAR(n)行全体で 65,535 バイトまで。utf8mb4 なら実質16,000文字強そのまま張れる(長さ制限あり)
TEXT約 65KB前から何バイトか指定しないと張れない
MEDIUMTEXT約 16MB同上
LONGTEXT約 4GB同上

まず、VARCHAR は無限に大きくできません。

ALTER TABLE d_str MODIFY s_var VARCHAR(20000);
ERROR 1074 (42000): Column length too big for column 's_var' (max = 16383); use BLOB or TEXT instead

1行に入る合計バイト数に上限(65,535バイト)があり、VARCHAR はその中で場所を取ります。 utf8mb4 は1文字最大4バイトなので、VARCHAR(20000) は8万バイトを要求してしまい、1列だけで上限を超えます。 そのため MySQL は行の合計を数える前に、この列だけで「長すぎる(utf8mb4 なら最大 16,383 文字)」と止めます。複数の列の合計が 65,535 バイトを超えたときは ERROR 1118(Row size too large)になります。

次に、TEXT に索引を張ろうとします。

CREATE INDEX ix_text ON d_str (s_text);
ERROR 1170 (42000): BLOB/TEXT column 's_text' used in key specification without a key length

「長さを言え」と言われます。

TEXT は行の外に置かれることがあるので、丸ごと索引に入れられません。 張るなら前から何バイトかを指定します。

CREATE INDEX ix_text ON d_str (s_text(20));
Query OK, 0 rows affected (0.03 sec)

これは「前から20バイトだけの索引」です。前方一致には効きますが、20バイトより先で違う文字列は区別できません。 本文を本気で検索したいなら、この形では足りません。全文検索の回の話になります。

もう1つ、並べ替えの落とし穴があります。

SHOW VARIABLES LIKE 'max_sort_length';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_sort_length | 1024  |
+-----------------+-------+

ORDER BY で長い文字列を並べるとき、MySQL は先頭 1024 バイトしか見ません。 1024バイト目まで同じ2つの文書は、「同じ」として扱われます。

さらに、TEXT を含む結果を並べ替えたり集計したりすると、一時テーブルが作られます。 MySQL 8.0.13 より前は、TEXT が混ざるだけでディスク上の一時テーブルが強制されて、目に見えて遅くなりました。8.0.13 以降は改善されましたが、メモリを大きく食うことに変わりはありません。

最後に、TEXT には既定値が付けられません。

ALTER TABLE d_str MODIFY s_text TEXT DEFAULT '';
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 's_text' can't have a default value

まとめると、こうなります。

上限が業務的に決まるものは VARCHAR(n)。 本当に上限が決められない本文だけ TEXT。 「とりあえず TEXT」はやめる。

VARCHAR(255) と VARCHAR(191) の話

「とりあえず255」は、MySQL が255文字までなら長さを1バイトで記録できたという、昔の事情の名残です。いまは深い意味がありません。

VARCHAR(191) という半端な数字を見かけたら、それは索引のバイト数制限の話です。その由来は文字コードとUTF-8の回に書いたので、ここでは繰り返しません。

この記事で決めることは1つだけです。

長さは「業務上ありうる最大」で決める。 メールアドレスなら255、氏名なら100、都道府県名なら20。 VARCHAR は宣言を長くしても、実際に使う場所は増えません。 それでも短くするのは、「入っていい値の範囲」を宣言するためです。

ENUM の罠

「状態は3つしかないから ENUM」は、一見きれいです。

INSERT INTO d_enum (title, status) VALUES
  ('資料を作る',   'done'),
  ('レビュー依頼', 'todo'),
  ('本番反映',     'doing');

罠その1。並べ替えると、五十音順でもアルファベット順でもありません。

SELECT id, title, status, status + 0 AS status_num
FROM   d_enum
ORDER BY status;
+----+--------------+--------+------------+
| id | title        | status | status_num |
+----+--------------+--------+------------+
|  2 | レビュー依頼 | todo   |          1 |
|  3 | 本番反映     | doing  |          2 |
|  1 | 資料を作る   | done   |          3 |
+----+--------------+--------+------------+

ENUM の中身は、文字列ではなく番号です。 CREATE TABLE に書いた順に、1から番号が振られています。

ORDER BY status は、この番号で並べます。 たまたま今回は「todo → doing → done」という意味のある順になりましたが、それは並べた順がそうだったからで、偶然です。

罠その2。数値と比較すると、通ってしまいます。

SELECT * FROM d_enum WHERE status = 1;
+----+--------------+--------+
| id | title        | status |
+----+--------------+--------+
|  2 | レビュー依頼 | todo   |
+----+--------------+--------+

status = 1 が、status = 'todo' として通りました。

アプリ側が 1 を送ってきたら、エラーも警告もなく1番目の値として扱われます。 そして、CREATE TABLE の並びを変えた日に、意味が丸ごとズレます。

罠その3。選択肢を増やすのに ALTER TABLE が要ります。

末尾に足すだけなら、一瞬で終わります。

ALTER TABLE d_enum
  MODIFY status ENUM('todo','doing','done','canceled') NOT NULL DEFAULT 'todo',
  ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.02 sec)

ALGORITHM=INSTANT は「表を作り直さずにできるならやれ、できないなら断れ」という指定です。末尾に足すだけなら、既存の番号が動かないので通ります。

途中に挟もうとすると、断られます。

ALTER TABLE d_enum
  MODIFY status ENUM('todo','blocked','doing','done','canceled') NOT NULL DEFAULT 'todo',
  ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY.

blocked を2番目に挟むと、doing の番号が 2 から 3 にずれます。 だから、表を丸ごと作り直すしかありません。(ALGORITHM=COPY = 全行コピー)

行が数百万あれば、その間ずっと書き込みが止まります。

なので、ENUM を使う条件はかなり狭いです。

状況選ぶもの
選択肢が本当に増えない(性別コード、曜日、血液型)ENUM で構わない
増えるかもしれない(タスクの状態、注文の状態)VARCHAR(20) + CHECK か、参照テーブル + 外部キー
選択肢に名前・並び順・色など属性が付く迷わず参照テーブル

私は、状態を ENUM にして1回痛い目を見ました。 「保留」を足したくなったのは、リリースの3週間後でした。

④ 日付と時刻の型(一覧だけ)

日時の型は5つです。

型入るもの範囲タイムゾーン変換
DATE日付だけ1000-01-01 〜 9999-12-31しない
DATETIME日付+時刻1000-01-01 00:00:00 〜 9999-12-31 23:59:59しない
TIMESTAMP日付+時刻1970-01-01 〜 2038-01-19する(読み書きのたび)
TIME時間の長さ、または時刻-838:59:59 〜 838:59:59しない
YEAR年だけ1901 〜 2155しない

DATETIME と TIMESTAMP のどちらを選ぶか、TIMESTAMP の 2038年問題、「保存はUTC、表示はローカル」という原則は、日付と時刻の扱いの回で丸ごと1本使って扱いました。 この記事では繰り返しません。

この記事で言うことは1つだけです。

日付を VARCHAR で持たないでください。 '2026/9/2' と '2026-09-02' と '令和8年9月2日' が同じ列に並び、並べ替えも比較も引き算もできなくなります。

⑤ 暗黙の型変換

準備:1000行の表を作る

索引の話は、行が3行しかない表では何も見えません。

INSERT INTO d_conv (num_code, str_code)
WITH RECURSIVE seq (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT n, LPAD(n, 10, '0') FROM seq;
Query OK, 1000 rows affected (0.03 sec)

WITH RECURSIVE は「1から始めて、1000になるまで1ずつ足した表を作る」という書き方です(MySQL 8.0 から使えます)。LPAD(n, 10, '0') で、0000000001 のような先頭を0で埋めた10桁の文字列を作っています。

わざと紛らわしい行を3つ足します。

INSERT INTO d_conv (num_code, str_code) VALUES
  (500, '500'),
  (500, ' 500'),
  (500, '500円');

統計情報を更新しておきます(EXPLAIN の推定行数を実態に近づけるためです)。

ANALYZE TABLE d_conv;

数値列に文字列で問い合わせる → 索引は効く

EXPLAIN SELECT * FROM d_conv WHERE num_code = '500';
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | d_conv | ref  | ix_num        | ix_num | 4       | const |    4 | NULL  |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------+

type が ref、key が ix_num。索引が使われています。

'500' という文字列で聞いたのに効いた理由は、変換されたのが「聞いた側」だからです。 MySQL は '500' を数値の 500 に直してから、索引をそのまま引きにいきます。 列の値は1つも触られていません。

索引は「列の値を並べた早見表」です。 列の値を加工しない限り、早見表は使えます。

文字列列に数値で問い合わせる → 索引が丸ごと死ぬ

EXPLAIN SELECT * FROM d_conv WHERE str_code = 500;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | d_conv | ALL  | NULL          | NULL | NULL    | NULL | 1003 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

type が ALL。possible_keys すら NULL です。 索引が「使われなかった」のではなく、「候補にすら挙がらなかった」のです。

[スクリーンショット: 03_explain_type.png] 上の2つの EXPLAIN を続けて実行し、type が ref と ALL に分かれている画面を撮ってください。

なぜこうなるのか。 MySQL のルールでは、文字列と数値を比べるときは、両方を数値(浮動小数点数)に直します。 つまり、実際にはこう書いたのと同じです。

WHERE CAST(str_code AS DOUBLE) = 500

列に関数がかぶさりました。 早見表には '0000000500' という文字列が並んでいるだけで、「数値に直したらいくつか」は書かれていません。 だから、1003行を全部読んで、1行ずつ数値に直して比べるしかなくなります。

しかも、返ってくる中身まで変わります。

SELECT id, str_code FROM d_conv WHERE str_code = 500;
+------+------------+
| id   | str_code   |
+------+------------+
|  500 | 0000000500 |
| 1001 | 500        |
| 1002 |  500       |
| 1003 | 500円      |
+------+------------+

4行返りました。 '0000000500' も ' 500'(先頭に空白)も '500円' も、数値に直せば全部 500 だからです。

SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '500円' |
+---------+------+------------------------------------------+

警告は出ています。でも、SQLは成功しています。 アプリからは、「なんか多く返ってくるな」としか見えません。

正しく書くと、こうです。

EXPLAIN SELECT * FROM d_conv WHERE str_code = '500';
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | d_conv | ref  | ix_str        | ix_str | 82      | const |    1 | NULL  |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------+

索引が効き、1行だけ返ります。 違いは、500 をクォートで囲んだかどうかだけです。

WHERE 文字列列 = 数値 は書かない。 これはSQLの書き方の問題ではなく、「アプリが数値を送っている」ことの症状です。JavaScript の id が数値のまま渡っているなどが典型で、直すのはアプリ側です。

EXPLAIN の読み方そのものはパフォーマンス改善とEXPLAINの回に書きました。 ここで覚えるのは、type が ALL なら全件読んでいる、それだけで十分です。

'abc' = 0 が真になる

もっと極端な例を打ちます。

SELECT 'abc' = 0 AS a, '12abc' = 12 AS b, '' = 0 AS c;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
+---+---+---+

3つとも真です。

理屈は同じです。 文字列と数値を比べるので、文字列のほうを数値に直します。 そのとき MySQL は、

  • 先頭から数字として読める分だけ読み、残りを捨てます('12abc' → 12)
  • 1文字も読めなければ 0 にします('abc' → 0、'' → 0)
SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+---------------------------------------+

これがなぜ危ないか。

-- 想定:ステータスコード 0 の行を探したい
SELECT * FROM some_table WHERE status_text = 0;

status_text が 'active' の行も、'pending' の行も、全部ヒットします。 どちらも数値に直すと 0 だからです。

厳格モードは、これを止めてくれない

「厳格モードにしていれば安全」と思っていました。違います。

SELECT @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

STRICT_TRANS_TABLES が入っています。厳格モードはONです。 それでも、さっきの 'abc' = 0 は真を返しました。

厳格モードが効くのは、INSERT と UPDATE、つまり「値を書き込むとき」だけです。 SELECT の比較には効きません。

書き込みのほうで確かめます。

INSERT INTO d_type (memo, t_int) VALUES ('文字を入れる', 'abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 't_int' at row 1

止まりました。 厳格モードを切ると、どうなるか見ます。セッション限りの変更なので、この接続を閉じれば元に戻ります。

SET SESSION sql_mode = '';
INSERT INTO d_type (memo, t_int) VALUES ('文字を入れる', 'abc');
Query OK, 1 row affected, 1 warning (0.01 sec)
SELECT memo, t_int FROM d_type WHERE memo = '文字を入れる';
+--------------+-------+
| memo         | t_int |
+--------------+-------+
| 文字を入れる |     0 |
+--------------+-------+

'abc' が 0 として保存されました。 警告1つで、INSERT は成功しています。

すぐ戻します。

SET SESSION sql_mode = DEFAULT;
SELECT @@session.sql_mode;

STRICT_TRANS_TABLES が戻っていることを、必ず目で確かめてください。

まとめると、こうなります。

どこで起きるか厳格モードは止めてくれるか
INSERT / UPDATE で、入らない値を書く止める(エラーになる)
WHERE で、型の違うもの同士を比べる止めない(黙って変換して比較する)

WHERE の側は、自分で気をつけるしかありません。 だからアプリ側で型を揃えて送ることが、最後の砦になります。TypeScript の回で「型は実行時には消える」と書きましたが、消えた先で待っているのがこれです。

⑥ JSON型

入れてみる

INSERT INTO d_json (payload) VALUES
  ('{"plan": "pro",  "seats": 5, "tags": ["a", "b"]}'),
  ('{"plan": "free", "seats": 1, "tags": []}'),
  ('{"plan": "pro",  "seats": 3, "tags": ["c"]}');

JSON 型は、ただの文字列置き場ではありません。 壊れたJSONは弾かれます。

INSERT INTO d_json (payload) VALUES ('{oops}');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'd_json.payload'.

取り出す

SELECT id,
       JSON_EXTRACT(payload, '$.plan') AS f1,
       payload -> '$.plan'             AS f2,
       payload ->> '$.plan'            AS f3,
       payload ->> '$.seats'           AS seats
FROM   d_json;
+----+--------+--------+------+-------+
| id | f1     | f2     | f3   | seats |
+----+--------+--------+------+-------+
|  1 | "pro"  | "pro"  | pro  | 5     |
|  2 | "free" | "free" | free | 1     |
|  3 | "pro"  | "pro"  | pro  | 3     |
+----+--------+--------+------+-------+

3つの書き方の関係は、こうです。

書き方意味返るもの
JSON_EXTRACT(payload, '$.plan')取り出すJSONのまま("pro" とクォート付き)
payload -> '$.plan'上の短縮形。まったく同じJSONのまま
payload ->> '$.plan'取り出してクォートを外す文字列(pro)

矢印が1本か2本かで、"pro" と pro が変わります。 アプリに渡すときは、ほぼ常に ->>(2本)です。 1本だと、JavaScript 側で "\"pro\"" のような二重クォートに悩むことになります。

$. から始まる書き方はパス式と呼ばれます。$.tags[0] で配列の1つ目、$.a.b で入れ子、といった具合です。

索引は、そのままでは張れない

CREATE INDEX ix_plan ON d_json (payload);
ERROR 3152 (42000): JSON column 'payload' supports indexing only via generated columns on a specified JSON path.

エラーメッセージが答えを言っています。「生成列を経由しろ」です。

生成列とは、他の列から計算して作られる列で、自分では書き込めません。 そこに索引を張ります。

ALTER TABLE d_json
  ADD COLUMN plan VARCHAR(20)
    GENERATED ALWAYS AS (payload ->> '$.plan') VIRTUAL,
  ADD INDEX ix_plan (plan);
Query OK, 0 rows affected (0.06 sec)

確かめます。

SELECT id, plan FROM d_json;
+----+------+
| id | plan |
+----+------+
|  1 | pro  |
|  2 | free |
|  3 | pro  |
+----+------+

普通の列のように見えますが、INSERT で値を入れることはできません。 payload を書き換えれば、自動で追随します。

EXPLAIN SELECT * FROM d_json WHERE plan = 'pro';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | d_json | ref  | ix_plan       | ix_plan | 83      | const |    2 | NULL  |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

索引が効きました。

VIRTUAL は「保存しない。読むたびに計算する」、STORED は「実際に保存する」という違いです。 索引を張るなら VIRTUAL で十分です(索引のほうには値が入るため)。STORED は表を作り直すので、既存の大きな表では重くなります。

どこで使い、どこで使わないか

JSON型は「スキーマから逃げるための道具」ではありません。 逃げると、制約もチェックも既定値も効かない列が1本できます。

使ってよい使わないほうがよい
外部から来た生のデータをそのまま残す(Webhookのペイロード、APIの応答)検索や集計の主役になる項目
利用者ごとに項目が違う設定・アンケートの回答必須項目(NOT NULL が効かせられない)
形が固まる前の、実験中のデータ他のテーブルと JOIN する鍵
監査ログの「変更前後」数値として合計したい値

判定の基準は1つです。

その項目で WHERE を書くなら、普通の列にする。 JSONの中に入れたまま WHERE を書きたくなった時点で、設計が間違っています。

「スキーマは消えずにアプリへ移る」という話はNoSQLとMongoDBの回に書きました。 JSON型も同じで、決まりが消えるのではなく、決まりを守る担当がデータベースからアプリに移るだけです。

⑦ NULL を許すかどうか

型を決めたら、必ずもう1つ決めることがあります。 その列は空でよいか。

そして、既定はこれです。

NOT NULL を既定にする。NULL を許すのは、「不明」を表す必要があるときだけ。

理由は3つです。

  1. NULL は「不明」であって「0」や「空文字」ではありません。 混ぜた瞬間に、SUM が NULL になり、NOT IN が0行になります(SELECT の深掘りで丸ごと1節使いました)
  2. 後から NOT NULL にするのは高い。 既に NULL が入っていたら、先に全部埋めてからでないと ALTER が通りません
  3. NULL を許すと、アプリ側のコードが1本増えます。 すべての参照箇所で「無いかもしれない」を考えることになります

既定値との関係

NOT NULL にすると、INSERT でその列を省けなくなります。 ただし、DEFAULT があれば省けます。

INSERT INTO d_enum (title) VALUES ('既定値のテスト');
SELECT id, title, status FROM d_enum WHERE title = '既定値のテスト';
+----+----------------+--------+
| id | title          | status |
+----+----------------+--------+
|  4 | 既定値のテスト | todo   |
+----+----------------+--------+

status を書いていないのに todo が入りました。 DEFAULT 'todo' を宣言してあるからです。

DEFAULT が無いとどうなるか、d_conv で試します。

INSERT INTO d_conv (num_code) VALUES (1);
ERROR 1364 (HY000): Field 'str_code' doesn't have a default value

厳格モードが止めてくれました。 厳格モードを切っていたら、空文字 '' が黙って入ります。

組み合わせの意味を整理します。

宣言INSERT で省くと意味
NOT NULL + DEFAULT あり既定値が入る「たいていこれ」がある項目
NOT NULL + DEFAULT なしエラー(厳格モード)必ず指定させたい項目。いちばん安全
NULL 可 + DEFAULT なしNULL が入る本当に「不明」がありうる項目だけ
NULL 可 + DEFAULT あり既定値が入る紛らわしい。避ける

NOT NULL に DEFAULT '' や DEFAULT 0 を付けて「とりあえず埋める」のは、いちばんやってはいけないことです。 「未入力」と「0円」が区別できなくなり、しかもエラーが出ません。

⑧ 型を変える

変える前に、必ず SELECT で確かめる

型を変えるコマンドは1行です。

ALTER TABLE d_conv MODIFY str_code VARCHAR(30) NOT NULL;

問題は、狭くするときや、種類を変えるときです。 入らない値があると、厳格モードなら止まり、切っていれば黙って切り捨てられます。

だから、ALTER の前に必ず「入るかどうか」を SELECT で数えます。

① 文字列を短くする前に:いちばん長い行を数える

SELECT MAX(CHAR_LENGTH(str_code)) AS max_chars,
       MAX(LENGTH(str_code))      AS max_bytes
FROM   d_conv;
+-----------+-----------+
| max_chars | max_bytes |
+-----------+-----------+
|        10 |        10 |
+-----------+-----------+

CHAR_LENGTH が文字数、LENGTH がバイト数です。VARCHAR(n) の n は文字数ですが、索引の上限はバイト数なので、両方見ます(この区別は文字コードの回の話です)。

② 文字列を数値型にする前に:数値に直せない行を数える

SELECT COUNT(*) AS not_numeric
FROM   d_conv
WHERE  str_code NOT REGEXP '^-?[0-9]+$';
+-------------+
| not_numeric |
+-------------+
|           2 |
+-------------+

2行あります。 さっき入れた ' 500' と '500円' です。このまま INT にしたら、この2行の値は変わります。

③ NOT NULL にする前に:NULL の行を数える

SELECT COUNT(*) AS null_rows FROM d_type WHERE t_int IS NULL;

④ 整数型を狭くする前に:最大と最小を見る

SELECT MIN(num_code) AS min_v, MAX(num_code) AS max_v FROM d_conv;
+-------+-------+
| min_v | max_v |
+-------+-------+
|     1 |  1000 |
+-------+-------+

SMALLINT(上限32,767)には入ります。TINYINT には入りません。

これが「壊す操作の前に、壊さない操作で確かめる」という型です。 本線の回で「UPDATE の前に同じ WHERE で SELECT する」と書いたのと、まったく同じ考え方です。

大きな表では、テーブルがロックされる

ここがいちばん実務に効きます。

MySQL 8.0 では、ALTER TABLE の後ろに「どうやって変えるか」を指定できます。

ALTER TABLE d_conv MODIFY num_code BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY.

列の型を変えるには、表を丸ごとコピーするしかありません。

ALGORITHM の3つを整理します。

指定何をするか書き込みは
INSTANT表の定義だけ書き換える。 列の追加、ENUM の末尾への追加など止まらない(一瞬)
INPLACE表の中身をその場で直す。 索引の追加などたいてい止まらない
COPY新しい表を作り、全行をコピーし、入れ替える止まる

d_conv は1003行なので、ALGORITHM=COPY でも一瞬です。

ALTER TABLE d_conv MODIFY num_code BIGINT NOT NULL, ALGORITHM=COPY;
Query OK, 1003 rows affected (0.09 sec)
Records: 1003  Duplicates: 0  Warnings: 0

これが1000万行だったら、数分から数十分かかります。 その間、その表への書き込みは全部待たされます。 アプリから見れば障害です。

だから、実務ではこうします。

本番の ALTER TABLE は、必ず ALGORITHM= を明示して打つ。 INSTANT か INPLACE を指定しておけば、コピーになる変更は「実行される前に」エラーで止まります。 何も書かずに打つと、MySQL は黙って COPY を選び、走り始めてしまいます。

どうしてもコピーが必要なときの選択肢は3つです。

  1. アクセスの少ない時間にやり、事前に所要時間を測る(同じ行数のコピーをテスト環境で1回やる)
  2. pt-online-schema-change や gh-ost のような、無停止で入れ替える道具を使う
  3. そもそも ALTER しなくて済むように、最初から広めの型にしておく

3番目がいちばん安上がりです。 IDを最初から BIGINT UNSIGNED にする、というのはそういう話でした。

手で ALTER TABLE を打ち続けない仕組みは、ORMとマイグレーションの回に書きました。 ただし、マイグレーションの道具を使っても、走るのは同じ ALTER TABLE です。 ロックの話は消えません。

⑨ 選び方の表

この記事の結論を、1枚にまとめます。

入れたいもの型理由
主キーの連番IDBIGINT UNSIGNED AUTO_INCREMENT番号は穴が空くので行数より速く減る。 後から広げるのが高い
金額DECIMAL(15,2)FLOAT/DOUBLE は1円ずれる
個数・回数・点数INT UNSIGNED負にならない。43億まで
在庫・順位のような小さい数SMALLINT UNSIGNED など場所を節約したいときだけ。迷ったら INT
比率・座標・測定値DOUBLE元から近似の値。誤差が許される
真偽TINYINT(1) NOT NULL DEFAULT 0本物の論理型は無い。 2 が入らないようにアプリ側で守る
氏名・件名・メールVARCHAR(n)n は業務上の最大。255は根拠ではない
郵便番号・電話番号・製品コードVARCHAR足さない値は数ではない。 先頭の0が消える
国コード・通貨コードのような真の固定長CHAR(n)末尾の空白が消えても困らないときだけ
本文・説明・ログTEXT索引は前方nバイトか、全文検索へ
状態(本当に増えない)ENUM途中に挟むと表を作り直す
状態(増えるかもしれない)VARCHAR(20) + 参照テーブル選択肢を行として足せる
日付だけDATE誕生日にタイムゾーンは無い
絶対時刻DATETIME(UTCで保存)TIMESTAMP は2038年で止まる
形の決まらない付随データJSON検索する項目は生成列で外に出す
UUIDBINARY(16) + UUID_TO_BIN(uuid, 1)CHAR(36) は2倍以上の場所を食う。 第2引数の 1 で並びが良くなる
画像・PDFの中身持たないファイルはオブジェクトストレージへ。 パスだけ VARCHAR で持つ

そして、選ぶ順番はこうです。

  1. その値で計算するか。 しないなら文字列
  2. 計算するなら、1単位ずれて困るか。 困るなら DECIMAL、困らないなら DOUBLE
  3. 入りうる最大は。 そこから整数型と長さを決める
  4. 空でよいか。 よくないなら NOT NULL。既定値は要るか
  5. WHERE で使うか。 使うなら索引が効く形になっているか

確かめ方の型

この記事のどの節でも、やっていたことは同じです。

入れた値と、返ってきた値を、必ず見比べる。

打つのはこの3本だけです。

-- ① 入れる
INSERT INTO 表 (列) VALUES (値);
-- ② 返ってくるものが同じか見る
SELECT 列 FROM 表 WHERE ...;
-- ③ 何か言われていないか見る
SHOW WARNINGS;

SHOW WARNINGS; を打つ癖が、この記事でいちばん役に立ちます。

MySQL は、値を黙って変えるときも、警告だけは残しています。 mysql クライアントは 1 warning と1行だけ出して、中身は聞かれるまで言いません。 聞けば教えてくれます。

アプリからは、この警告はほぼ見えません。 だから、新しい列を作った直後に、必ず1回は手で INSERT して SHOW WARNINGS を見るようにしています。

つまずいたところ

症状原因と直し方
ERROR 1264 Out of range value型の範囲を超えた。 INT → BIGINT、または UNSIGNED を付ける
先頭の 0 が消えた番号を数値型に入れた。 VARCHAR か CHAR に変える
金額の合計が1円合わないFLOAT / DOUBLE を使っている。 DECIMAL に変える
WHERE price = 1980 が0行同上。浮動小数点数は = で比べられない
画面には 0.1 と出るのに = 0.1 が偽FLOAT は表示のときだけ丸めて見せている
AUTO_INCREMENT の番号が飛んでいる正常。 ロールバックや失敗で番号は消費される。連番として使わない
CHAR に入れた末尾の空白が消えたCHAR の仕様。 空白を残したいなら VARCHAR
'abc' = 'abc ' の結果がサーバーで違う照合順序が NO PAD か PAD SPACE か。 アプリ側で前後の空白を落としてから保存する
ERROR 1170 ... used in key specification without a key lengthTEXT に索引を張ろうとした。 列(20) のように長さを付ける
ERROR 1118 Row size too largeVARCHAR の合計が1行の上限(65,535バイト)を超えた。 長さを見直すか TEXT へ
ERROR 1101 ... can't have a default valueTEXT / JSON に既定値は付けられない
ORDER BY で長い本文の順序がおかしいmax_sort_length(既定1024バイト)までしか見ていない
WHERE status = 1 が通ってしまうENUM は中身が番号。 必ず文字列で比較する
ERROR 1846 ... Try ALGORITHM=COPY表を作り直さないとできない変更。 行数が多いなら時間帯とロックを考える
EXPLAIN の type が ALL になったWHERE 文字列列 = 数値 を書いていないか。 クォートを付ける
WHERE が想定より多く返る同上。数値に直すと同じになる文字列が全部ヒットしている
ERROR 1364 Field ... doesn't have a default valueNOT NULL で DEFAULT の無い列を省いた。 値を渡すか、既定値を付ける
ERROR 3152 ... only via generated columnsJSON 列に直接索引を張ろうとした。 生成列を1本出す
JSONから取った値に " が付いている->(1本)を使っている。 ->>(2本)にする

私がいちばん長く詰まったのは、EXPLAIN の type が ALL になる件です。

エラーが出ないので、SQLを疑いませんでした。 結果も正しく返ってきます(むしろ多めに返ってきます)。「行が増えたから遅くなったんだろう」と思って、索引を張り直したり、ANALYZE TABLE を打ったりして、2時間ほど溶かしました。

気づいたきっかけは、EXPLAIN の possible_keys が NULL だったことです。「使われなかった」なら分かりますが、「候補にすら挙がっていない」のは異常です。そこで初めて、列の側が変換されていることに思い至りました。

直したのはSQLではなく、アプリ側の1行でした。 JavaScript から code を数値のまま渡していただけです。

EXPLAIN で possible_keys が NULL なら、それは「索引が無い」ではありません。 「列を加工してしまっているから、索引が使えない」です。関数か、暗黙の型変換か、そのどちらかです。

もう1つ、地味に効いたのが SHOW WARNINGS です。

Query OK, 1 row affected, 1 warning の 1 warning を、ずっと読み飛ばしていました。 そこに「値を切り詰めた」と毎回書いてあったのに、です。MySQL はちゃんと言っていました。私が聞いていませんでした。

後片づけ

この記事で作った6つの表は、ここで消します。

SELECT の深掘りで作った d_emp / d_dept は、以降の回でも使うので残します。 この記事の表は、この記事だけのものです。

DROP TABLE IF EXISTS d_json;
DROP TABLE IF EXISTS d_conv;
DROP TABLE IF EXISTS d_enum;
DROP TABLE IF EXISTS d_str;
DROP TABLE IF EXISTS d_seq;
DROP TABLE IF EXISTS d_type;

残っているものを確かめます。

SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| d_dept          |
| d_emp           |
| tasks           |
+-----------------+
SELECT COUNT(*) FROM tasks;

DROP DATABASE appdb; は打たないでください。 本線のアプリのデータが全部消えます。 消すのはこの記事で作った6つの表だけです。

設定も戻っているか、最後に確かめます。

SELECT @@session.sql_mode;

STRICT_TRANS_TABLES が入っていれば、戻っています。 入っていなければ、この接続を一度閉じてください(SET SESSION はセッション限りなので、閉じれば必ず戻ります)。

EXIT;

まとめ

  • 型は「入るか入らないか」の話ではない。 間違えると、エラーではなく値のほうが黙って変わる
  • 整数は5種類あって、違いはバイト数だけ。 覚えるのは「INT の上限は約21億」の1行
  • UNSIGNED は「上限が倍になるおまけ」ではなく「負の数を入れない」という宣言。 倍になるのはその結果
  • INT(11) の 11 は桁数ではなく表示幅で、意味は無い。 例外は TINYINT(1) だけ
  • FLOAT と DOUBLE で金額を扱わない。 0.1E0 + 0.2E0 は 0.30000000000000004 になる。2進数で 0.1 が割り切れないから
  • FLOAT は表示すら信用できない。 画面に 0.1 と出ていても = 0.1 が偽になる
  • お金は DECIMAL(15,2)。 判定の基準は「1単位ずれたら誰かが困るか」。困らないなら DOUBLE でよい
  • MySQL に本物の論理型は無い。 BOOLEAN は TINYINT(1) の別名で、2 が平気で入る。 = TRUE と IS TRUE で答えが変わる
  • AUTO_INCREMENT の番号は、ロールバックしても戻らない。 番号を配る係はトランザクションの外にいる。件数にも連番にも使わない
  • CHAR は末尾の空白を落として返す。VARCHAR は残す。 迷ったら VARCHAR
  • 'abc' = 'abc ' の答えは、照合順序で変わる。 MySQL 8.0 の既定は「違う」。移行でいちばん静かに壊れる場所
  • 足し算をしない値は数ではない。 郵便番号を INT にすると先頭の0が消える
  • TEXT は VARCHAR の大きい版ではない。 索引は前から何バイトかしか張れず、既定値も付けられず、ORDER BY は先頭1024バイトしか見ない
  • ENUM は文字列に見えて、中身は1から振られた番号。 WHERE status = 1 が通る。 途中に選択肢を挟むと表を丸ごと作り直す
  • WHERE 文字列列 = 数値 を書くと、索引が候補にすら挙がらない。 EXPLAIN の type が ALL になる。逆(数値列 = '文字列')は効く
  • 'abc' = 0 は真になる。 文字列を数値に直すとき、読めない分は捨てて0にするから
  • 厳格モードが効くのは INSERT / UPDATE だけ。 WHERE の比較には効かない
  • JSON 列には直接索引を張れない。 生成列を1本出して、そこに張る。 ->>(矢印2本)でクォートを外す
  • WHERE を書きたくなる項目は、JSONの中に入れない。 普通の列にする
  • 既定は NOT NULL。 NULL を許すのは「不明」を表したいときだけ。NOT NULL DEFAULT '' で埋めるのがいちばん悪い
  • 型の変更は表を丸ごとコピーする。 本番では ALGORITHM= を明示して、コピーになるなら走る前に止める
  • ALTER の前に SELECT で数える。 最大長・NULL の数・最小最大・数値に直せない行
  • 確かめ方の型:入れて、読み出して、SHOW WARNINGS を打つ。 MySQL は黙って変えるときも、警告だけは残している

参考リンク


次に読む

この記事は 【深掘り】SQL の 2 / 17 本目です。

記事
← 前の記事SELECTの実行順序とNULL — WHEREで別名が使えない
→ 次の記事DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Data Types
  • 数値・文字列・日時・JSON の選び方
  • 暗黙の型変換と索引が効かなくなる話

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次