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にすること- 型の変更は、大きな表ではテーブルを丸ごと作り直すこと。その間、書き込みが止まること
この記事でやること
- 練習用のテーブルを6つ作る(
d_typed_seqd_strd_enumd_convd_json)。本線のtasksにも、深掘りのd_empにも触らない TINYINTに128を入れてOut of rangeを出す。UNSIGNEDに-1を入れて、もう一度出すDOUBLEの列に0.1と0.2を入れて合計し、0.3にならないのを見る。 同じことをDECIMALでやって、合うのを見るCHARとVARCHARに同じ文字列を入れて、返ってくる長さが違うのを見る。TEXTに索引を張ろうとして弾かれるENUMを数値と比較して、通ってしまうのを見る。 選択肢を途中に挟むALTERが弾かれるのも見る- 1000行の表を作り、
EXPLAINで「索引が使われている行」と「使われていない行」を見比べる JSONから生成列を1本出して索引を張り、EXPLAINが変わるのを確かめる- 練習用テーブルを片づける
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| データ型 | 列に「これは何か」を宣言するもの。 数なのか、文字なのか、日付なのか |
| 桁あふれ(オーバーフロー) | その型に入る範囲を超えること。 入れ物より中身が大きい状態 |
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 ではなく管理者で入ります。
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 |
|---|---|---|---|
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 | -2,147,483,648 〜 2,147,483,647 | 0 〜 4,294,967,295 |
BIGINT | 8 | 約 ±9.2×10^18 | 0 〜 約 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つです。
NULLは「不明」であって「0」や「空文字」ではありません。 混ぜた瞬間に、SUMがNULLになり、NOT INが0行になります(SELECTの深掘りで丸ごと1節使いました)- 後から
NOT NULLにするのは高い。 既にNULLが入っていたら、先に全部埋めてからでないとALTERが通りません 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回やる)
pt-online-schema-changeやgh-ostのような、無停止で入れ替える道具を使う- そもそも
ALTERしなくて済むように、最初から広めの型にしておく
3番目がいちばん安上がりです。 IDを最初から BIGINT UNSIGNED にする、というのはそういう話でした。
手で ALTER TABLE を打ち続けない仕組みは、ORMとマイグレーションの回に書きました。 ただし、マイグレーションの道具を使っても、走るのは同じ ALTER TABLE です。 ロックの話は消えません。
⑨ 選び方の表
この記事の結論を、1枚にまとめます。
| 入れたいもの | 型 | 理由 |
|---|---|---|
| 主キーの連番ID | BIGINT 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 | 検索する項目は生成列で外に出す |
| UUID | BINARY(16) + UUID_TO_BIN(uuid, 1) | CHAR(36) は2倍以上の場所を食う。 第2引数の 1 で並びが良くなる |
| 画像・PDFの中身 | 持たない | ファイルはオブジェクトストレージへ。 パスだけ VARCHAR で持つ |
そして、選ぶ順番はこうです。
- その値で計算するか。 しないなら文字列
- 計算するなら、1単位ずれて困るか。 困るなら
DECIMAL、困らないならDOUBLE - 入りうる最大は。 そこから整数型と長さを決める
- 空でよいか。 よくないなら
NOT NULL。既定値は要るか 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 length | TEXT に索引を張ろうとした。 列(20) のように長さを付ける |
ERROR 1118 Row size too large | VARCHAR の合計が1行の上限(65,535バイト)を超えた。 長さを見直すか TEXT へ |
ERROR 1101 ... can't have a default value | TEXT / 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 value | NOT NULL で DEFAULT の無い列を省いた。 値を渡すか、既定値を付ける |
ERROR 3152 ... only via generated columns | JSON 列に直接索引を張ろうとした。 生成列を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 は黙って変えるときも、警告だけは残している
参考リンク
- roadmap.sh — SQL
- MySQL 8.0 リファレンスマニュアル(日本語)
- MySQL — データ型
- MySQL — 整数型
- MySQL — 浮動小数点数の問題
- MySQL — 固定小数点型(DECIMAL)
- MySQL — CHAR と VARCHAR
- MySQL — BLOB と TEXT
- MySQL — ENUM 型
- MySQL — 日付と時刻の型
- MySQL — 比較における型変換
- MySQL — SQLモード(厳格モード)
- MySQL — JSON 型
- MySQL — 生成列
- MySQL — オンラインDDLの操作一覧
- MySQL — AUTO_INCREMENT の使い方
次に読む
この記事は 【深掘り】SQL の 2 / 17 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 数値・文字列・日時・JSON の選び方
- 暗黙の型変換と索引が効かなくなる話
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント