トランザクションそのもの(COMMIT / ROLLBACK / ACID / ロック / 分離レベル)は、本線の全部やるか、1つもやらないか — トランザクションとACIDで扱い済みです。 この記事はその上に乗って、INSERT / UPDATE / DELETE という文そのものを扱います。
前回: NOT NULL・UNIQUE・CHECK制約 — アプリで弾いた値が入る これは深掘りです。本線の順路ではありません。 本線はプロセスを止めても消えない場所 — MySQLとSQLの基本の回から先へ進んでいます。この記事はその回の続きで、「行を書き換える操作」だけを1本かけて掘り直すものです。飛ばしても本線は繋がります。
作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて、
appdbというデータベースがある状態から始めます。練習用のテーブルはこの記事の中で全部作ります。
SELECT を間違えても、何も壊れません。0行返るか、変な行が返るだけです。打ち直せば済みます。
UPDATE は違いました。
等級(grade)を付け直そうとして、こう打ちました。
UPDATE d_emp SET grade = 'S';
返ってきたのはこれです。
Query OK, 30 rows affected (0.01 sec)
Rows matched: 30 Changed: 30 Warnings: 0
「給料が高い7人だけ」のつもりでした。30人全員が S になりました。 WHERE を書き忘れていました。
慌てて打ちました。
ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
0 rows affected。何も戻っていません。
このとき私は「ROLLBACK はいつでも打てる保険」だと思っていました。違いました。 MySQL は既定で autocommit が有効で、1文打つたびに勝手に確定しています。 保険に入っていないのに、事故のあとで保険証を探していたのと同じでした。
この記事は「うまく UPDATE を書く方法」の記事ではありません。 そういう記事はいくらでもあります。 この記事でやるのは、「打つ前に必ずやる4手」を体に入れることです。それをやってから、INSERT / UPDATE / DELETE の細かい落とし穴を1つずつ踏みにいきます。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
ROLLBACKは「打てば戻る」ものではないこと。BEGINを打っていなければ、ROLLBACKは何もしないRows matchedとChangedは別の数字だということ。「0件変わった」でも「30件当たっている」ことがある。ここを読み違えると事故に気づけないREPLACEは「上書き」ではなく「消してから入れ直す」こと。指定しなかった列は、既定値かNULLに戻るLAST_INSERT_ID()は複数行入れたとき、最後ではなく先頭のIDを返すこと- MySQL では、更新する表そのものをサブクエリで読めないこと(
ERROR 1093)。派生表で包むと通る理由 - 外部キーは
DELETEを「拒否する」ことも「連鎖させる」こともあること。どちらになるかは表を作ったときに決まっている - 論理削除(
deleted_at)はタダではないこと。全部のクエリに条件が増え、ユニーク制約が事実上効かなくなる
この記事でやること
- 練習用のテーブルを
d_接頭辞で作る(appdbの中。既存のtasksには触りません) - 「打つ前の4手」を型として覚え、わざと間違えた
UPDATEをROLLBACKで戻す。 そのあとautocommitのまま同じことをやって、戻せないのを見る sql_safe_updatesを有効にして、WHERE無しのUPDATEが拒否されるのを確かめるINSERT/UPDATE/DELETEの落とし穴を、エラー番号ごと踏みにいく- 5000行を3つの入れ方で投入して、実際に時間を測って比べる
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| DML | Data Manipulation Language。 INSERT / UPDATE / DELETE のような、中身の行を出し入れする文のまとめ呼び |
autocommit | 1文ごとに勝手に確定するMySQLの既定の動き。 有効だと ROLLBACK は効かない |
Rows matched | WHERE に当たった行数。 値が変わったかは関係ない |
Changed | 実際に値が変わった行数。 同じ値を入れ直した行は数えない |
sql_safe_updates | 「キー列を使う WHERE の無い更新・削除」を拒否する設定。 事故防止のブレーキ |
| 一意制約(UNIQUE) | 「この列の値は重複させない」という表側の約束。 破ると ERROR 1062 |
| 外部キー(FOREIGN KEY) | 「この列の値は、あっちの表に必ず存在する」という表側の約束 |
ON DELETE CASCADE | 親を消したら子も一緒に消す、という外部キーの設定 |
| 派生表(デリバードテーブル) | FROM (SELECT ...) AS x の形で、サブクエリの結果を1枚の表として扱ったもの |
| 論理削除 | 行を消さずに「消したという印」を立てるだけのやり方。deleted_at 列がよく使われる |
| バイナリログ(binlog) | MySQLが「どう変更されたか」を順番に書き続けている記録ファイル |
① 準備 — バージョンを見て、練習用のデータを作る
まずバージョンを確認する
この記事には MySQL 8.0 以降が要る書き方が1つあります(ON DUPLICATE KEY UPDATE の新しい書き方)。先に見ておきます。
+-----------+
| VERSION() |
+-----------+
| 8.0.43 |
+-----------+
8.0 以上なら全部そのまま動きます。 5.7 だった場合は、③ の1箇所だけ古い書き方に読み替える必要があります。そこに来たら注記します。
管理者で入る
練習用の表を作るので、appuser ではなく管理者で入ります。
appuser には CREATE TABLE の権限を渡していません(本線のMySQLの回でわざと外しました)。ここで appuser を使うと止まります。
ERROR 1142 (42000): CREATE command denied to user 'appuser'@'localhost'
これは正常です。 権限が効いている証拠なので、appuser に CREATE を足したりしないでください。
USE appdb;
触るのは d_ で始まる表だけにする
本線で作った tasks / users / sessions には一切触りません。
深掘り用の練習テーブルは、すべて d_ で始めることにします(d は deep のつもりです)。前の深掘り記事と同じ規則です。
今日は特に大事です。 この記事は行を消したり書き換えたりする記事なので、名前を見た瞬間に「これは練習用だ」と分かることが、そのまま安全装置になります。
表を6つ作る
前の記事の d_emp とは列の構成が違います。 この記事用に作り直すので、DROP から始めます。
DROP TABLE IF EXISTS d_emp_kpi;
DROP TABLE IF EXISTS d_emp;
DROP TABLE IF EXISTS d_dept;
DROP TABLE IF EXISTS d_stage;
DROP TABLE IF EXISTS d_raise;
DROP TABLE IF EXISTS d_member;
DROP TABLE IF EXISTS d_bulk;
順番に意味があります。
d_emp_kpiがd_empを、d_empがd_deptを外部キーで指します。 外部キーで指している側(子)から先に消さないと、ERROR 3730(Cannot drop table … referenced by a foreign key constraint …。MySQL 5.7 まではERROR 1217)で拒否されます。
CREATE TABLE d_dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(40) NOT NULL,
floor_no INT
) ENGINE=InnoDB;
CREATE TABLE d_emp (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(40) NOT NULL,
dept_id INT,
salary INT NOT NULL,
bonus INT,
grade VARCHAR(10),
deleted_at DATETIME DEFAULT NULL,
CONSTRAINT fk_d_emp_dept FOREIGN KEY (dept_id) REFERENCES d_dept (dept_id)
) ENGINE=InnoDB;
CREATE TABLE d_emp_kpi (
emp_id INT PRIMARY KEY,
target INT NOT NULL,
actual INT NOT NULL DEFAULT 0,
memo VARCHAR(100),
CONSTRAINT fk_d_kpi_emp FOREIGN KEY (emp_id) REFERENCES d_emp (emp_id) ON DELETE CASCADE
) ENGINE=InnoDB;
外部キーの設定が2つ、わざと違います。 ⑥ で両方の挙動を見ます。
| 外部キー | 指定 | 親を消そうとしたとき |
|---|---|---|
d_emp → d_dept | 何も書いていない(既定 = RESTRICT) | 拒否される |
d_emp_kpi → d_emp | ON DELETE CASCADE | 子も一緒に消える |
残り4枚です。こちらは外部キーを付けていません。
CREATE TABLE d_stage (
emp_name VARCHAR(40) NOT NULL,
dept_id INT,
salary INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE d_raise (
dept_id INT PRIMARY KEY,
rate DECIMAL(4,3) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE d_member (
member_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(80) NOT NULL,
nickname VARCHAR(40) NOT NULL,
deleted_at DATETIME DEFAULT NULL,
UNIQUE KEY uk_d_member_email (email)
) ENGINE=InnoDB;
CREATE TABLE d_bulk (
id INT AUTO_INCREMENT PRIMARY KEY,
n INT NOT NULL,
s VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
データを入れる
部署を6つ。
INSERT INTO d_dept (dept_id, dept_name, floor_no) VALUES
(1, '営業部', 3),
(2, '開発部', 5),
(3, '人事部', 2),
(4, '経理部', 2),
(5, '情報システム部', 5),
(6, '新規事業室', NULL);
社員を30人。emp_id は書きません。 AUTO_INCREMENT に振らせます(④ で使います)。
INSERT INTO d_emp (emp_name, dept_id, salary, bonus, grade) VALUES
('高橋 誠', 1, 620000, 900000, 'A'),
('山本 拓也', 1, 430000, 300000, 'B'),
('中村 彩', 1, 410000, 280000, 'B'),
('小林 翔', 1, 390000, NULL, 'C'),
('加藤 美咲', 1, 370000, 200000, 'C'),
('吉田 亮', 1, 350000, NULL, 'C'),
('山田 優', 1, 330000, 150000, 'C'),
('松本 真', 1, 295000, 100000, 'C'),
('佐藤 健一', 2, 680000, 1100000, 'A'),
('井上 直樹', 2, 520000, 500000, 'A'),
('木村 隆', 2, 500000, 480000, 'B'),
('林 さくら', 2, 470000, 420000, 'B'),
('清水 剛', 2, 450000, NULL, 'B'),
('山口 恵', 2, 430000, 380000, 'B'),
('阿部 諒', 2, 400000, NULL, 'C'),
('森 大地', 2, 380000, 250000, 'C'),
('田中 由紀', 3, 540000, 700000, 'A'),
('岡田 修', 3, 460000, 400000, 'B'),
('村上 健太', 3, 370000, NULL, 'C'),
('近藤 静', 3, 330000, 180000, 'C'),
('鈴木 大輔', 4, 600000, 800000, 'A'),
('石井 裕', 4, 490000, 450000, 'B'),
('坂本 麻衣', 4, 450000, 390000, 'B'),
('青木 千鶴', 4, 360000, 220000, 'C'),
('渡辺 明', 5, 650000, 850000, 'A'),
('福田 智', 5, 530000, 520000, 'A'),
('三浦 亜紀', 5, 480000, 440000, 'B'),
('松田 望', 5, 440000, NULL, 'B'),
('桜井 一', 6, 420000, NULL, 'B'),
('新人 太郎', NULL, 280000, NULL, NULL);
最後の1人だけ dept_id が NULL です(配属前)。桜井 一 だけが 新規事業室 にいます(⑥ で使います)。
残り3枚に入れます。
INSERT INTO d_emp_kpi (emp_id, target, actual, memo) VALUES
(1, 1500, 1420, '上期は達成'),
(2, 800, 810, '新規開拓が伸びた'),
(3, 800, 640, '9月に挽回予定'),
(4, 600, 600, NULL),
(5, 600, 520, '産休前の実績'),
(9, 1200, 1250, '通期で超過');
INSERT INTO d_raise (dept_id, rate) VALUES
(1, 1.030),
(2, 1.050),
(3, 1.010),
(4, 1.000),
(5, 1.020);
INSERT INTO d_stage (emp_name, dept_id, salary) VALUES
('新井 光', 2, 380000),
('石田 香', 2, 360000),
('上田 学', 3, 340000);
INSERT INTO d_member (email, nickname) VALUES
('a@example.com', 'あお'),
('b@example.com', 'びい'),
('c@example.com', 'しい');
入ったか確かめます。
SELECT COUNT(*) AS emp, (SELECT COUNT(*) FROM d_dept) AS dept FROM d_emp;
+-----+------+
| emp | dept |
+-----+------+
| 30 | 6 |
+-----+------+
30 と 6 が出れば準備完了です。 数が違ったら、INSERT を途中で切っています。この先の件数が全部ずれるので、ここで直してください。
[スクリーンショット: 01_練習データ投入.png] SELECT COUNT(*) ... の結果が 30 と 6 になっている画面を撮ってください。
② 打つ前の4手を、体に入れる
ここがこの記事で一番大事な節です。 以降の節は全部、この型の上に乗ります。
型はこれだけ
UPDATE と DELETE を打つときは、いつもこの順番でやります。
| 手 | やること | 見るもの |
|---|---|---|
| ① | 同じ WHERE で SELECT COUNT(*) する | これから何行に当たるのか |
| ② | BEGIN; を打つ | 確定を止める |
| ③ | UPDATE / DELETE を実行する | Rows matched の数字 |
| ④ | ① と ③ が一致したら COMMIT;、違ったら ROLLBACK; | — |
「① と ③ の数字を見比べる」ところが本体です。 ここを飛ばすと、②③④ をやっていても事故に気づけません。
やってみる — 正しいほう
「給料が50万円を超える人の等級を S にする」 をやります。
① 数える。
SELECT COUNT(*) FROM d_emp WHERE salary > 500000;
+----------+
| COUNT(*) |
+----------+
| 7 |
+----------+
7人です。この「7」を覚えます。
② 止める。
BEGIN;
Query OK, 0 rows affected (0.00 sec)
BEGINとSTART TRANSACTIONは同じ意味です。この記事では短いBEGINを使います。 中で何が起きているかは、本線のトランザクションとACIDの回にあります。
③ 実行する。
UPDATE d_emp SET grade = 'S' WHERE salary > 500000;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
Rows matched: 7。① で数えた 7 と同じです。
④ 確定する。
COMMIT;
Rows matched と Changed は違う数字
もう一度、同じ文を打ってみてください。
UPDATE d_emp SET grade = 'S' WHERE salary > 500000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 7 Changed: 0 Warnings: 0
0 rows affected なのに Rows matched: 7。
| 数字 | 意味 |
|---|---|
Rows matched | WHERE に当たった行数。 7行に当たっている |
Changed | 実際に値が変わった行数。 すでに S なので0 |
ここを読み違えると危険です。 「0 rows affected だから何も起きなかった」と思うのは間違いで、「7行に当たった。たまたま全部同じ値だっただけ」です。
逆に言うと、Rows matched は WHERE が正しいかどうかを教えてくれる唯一の数字です。④ で見比べるのは Changed ではなく Rows matched のほうです。
やってみる — 間違えるほう
次が本番です。わざと WHERE を落とします。
① 数える。
SELECT COUNT(*) FROM d_emp WHERE salary > 700000;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
0人です。(この会社に70万円超の人はいません)
② 止める。
BEGIN;
③ ここで、WHERE を打ち忘れます。
UPDATE d_emp SET grade = 'X';
Query OK, 30 rows affected (0.00 sec)
Rows matched: 30 Changed: 30 Warnings: 0
① で数えたのは 0。返ってきたのは 30。合っていません。
確かめます。
SELECT grade, COUNT(*) FROM d_emp GROUP BY grade;
+-------+----------+
| grade | COUNT(*) |
+-------+----------+
| X | 30 |
+-------+----------+
全員 X になりました。冒頭の事故と同じ状態です。
④ 戻します。
ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
もう一度見ます。
SELECT grade, COUNT(*) FROM d_emp GROUP BY grade;
+-------+----------+
| grade | COUNT(*) |
+-------+----------+
| NULL | 1 |
| B | 12 |
| C | 10 |
| S | 7 |
+-------+----------+
戻りました。 A の行が消えているのは、さっき A の人を全員 S に変えて COMMIT したからです。GROUP BY は実際に存在する値だけを行にするので、0件になった A はそもそも表示されません。それは意図した変更なので、これで正しい状態です。
ROLLBACKの結果も0 rows affectedと出ます。 これは「何も戻らなかった」という意味ではありません。ROLLBACKという文自体が行を触らないから0です。戻ったかどうかは、必ずSELECTで目で見て確かめてください。
[スクリーンショット: 02_rollbackで戻る.png] UPDATE d_emp SET grade = 'X'; で30行変わり、ROLLBACK のあとの GROUP BY grade で元に戻っている一連の画面を撮ってください。
助からないほう — autocommit が有効だと ROLLBACK は効かない
ここが冒頭の事故の正体です。
いまの設定を見ます。
SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 は「有効」です。MySQL の既定はこれです。
意味はこうです。
BEGINを打っていないとき、MySQL は1文ごとに勝手にCOMMITしている。 つまりUPDATEを打った瞬間に確定していて、次の行でROLLBACKと打っても、戻すべきものがもう無い。
証明します。まず、いまの等級を別の表に控えておきます。
DROP TABLE IF EXISTS d_emp_backup;
CREATE TABLE d_emp_backup AS SELECT emp_id, grade FROM d_emp;
Query OK, 30 rows affected (0.02 sec)
Records: 30 Duplicates: 0 Warnings: 0
CREATE TABLE ... AS SELECTは、SELECTの結果をそのまま新しい表にします。 これから壊すので、先に控えます。AS SELECTで作った表には、元の主キーも索引も外部キーも引き継がれません。 中身のコピーだけです。
では、BEGIN を打たずに壊します。
UPDATE d_emp SET grade = 'Z';
Query OK, 30 rows affected (0.01 sec)
Rows matched: 30 Changed: 30 Warnings: 0
戻そうとします。
ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
見ます。
SELECT grade, COUNT(*) FROM d_emp GROUP BY grade;
+-------+----------+
| grade | COUNT(*) |
+-------+----------+
| Z | 30 |
+-------+----------+
戻っていません。 全員 Z のままです。これが冒頭で起きたことです。
ROLLBACK はエラーを出しません。 「戻すものがありません」とも言いません。黙って成功したふりをします。 ここが一番たちが悪いところでした。
控えておいた表から戻す
さっき控えた d_emp_backup があるので戻せます。
BEGIN;
UPDATE d_emp e
JOIN d_emp_backup b ON e.emp_id = b.emp_id
SET e.grade = b.grade;
Query OK, 30 rows affected (0.01 sec)
Rows matched: 30 Changed: 30 Warnings: 0
SELECT grade, COUNT(*) FROM d_emp GROUP BY grade;
+-------+----------+
| grade | COUNT(*) |
+-------+----------+
| NULL | 1 |
| B | 12 |
| C | 10 |
| S | 7 |
+-------+----------+
戻りました。
COMMIT;
この
UPDATE ... JOIN ...の書き方は ⑤ で説明します。 ここでは「戻すための道具」として使いました。 控えを取っていなかったら、この復旧はできませんでした。 ⑨ でそこを扱います。
sql_safe_updates — 打てなくするブレーキ
型を守るのは人間の努力です。努力は疲れると切れます。 MySQL にはそもそも打てなくする設定があります。
SET sql_safe_updates = 1;
この状態で、さっきの事故をもう一度やってみます。
UPDATE d_emp SET grade = 'Z';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
拒否されました。1行も変わっていません。
主キーを使った WHERE なら通ります。
UPDATE d_emp SET bonus = 120000 WHERE emp_id = 8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ところが、これは拒否されます。
UPDATE d_emp SET grade = 'S' WHERE salary > 500000;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
さっきは通った文です。 なぜ拒否されるかというと、salary に索引が無いからです。
sql_safe_updatesが見ているのは「WHEREがあるか」ではなく、「索引が使われるか」です。 索引を使わないWHEREは全行を読みにいくので、「表全体を触る文」と同じ扱いになります。
逃げ道は2つあります。
UPDATE d_emp SET grade = 'S' WHERE salary > 500000 LIMIT 1000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 7 Changed: 0 Warnings: 0
LIMIT を付けると通ります。 「最大でもこの行数しか触らない」と宣言したことになるからです。
もう1つは、索引を付けることです。
ALTER TABLE d_emp ADD INDEX idx_d_emp_salary (salary);
索引そのものの話は、本線の速くする前に必ず測る — パフォーマンス改善とEXPLAINにあります。
sql_safe_updates は DELETE にも効きます。
DELETE FROM d_emp;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
万能ではありません。
WHERE 1=1を書けば通ってしまいます。「条件を書いたつもりで間違えている」事故は防げません。 防げるのは 「WHEREを丸ごと打ち忘れた」という、いちばん派手な事故だけです。それでも入れる価値はあります。
この設定は接続ごとで、EXIT すると消えます。 毎回効かせたいときは、mysql を起動するときにこう打ちます。
この記事の残りは sql_safe_updates を切って進めます(説明のために WHERE 無しの文をいくつか打つため)。
SET sql_safe_updates = 0;
③ INSERT を掘る
INSERT は一番簡単に見えて、一番書き方の種類が多い文です。
1行ずつではなく、まとめて入れる
INSERT INTO d_dept (dept_id, dept_name, floor_no) VALUES (7, '広報部', 4);
INSERT INTO d_dept (dept_id, dept_name, floor_no) VALUES (8, '法務部', 1);
これは、こう書けます。
INSERT INTO d_dept (dept_id, dept_name, floor_no) VALUES
(9, '購買部', 1),
(10, '総務部', 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
見た目が短くなるだけではありません。⑧ で実際に時間を測ります。
列名の
(dept_id, dept_name, floor_no)は省略できます。 省略しないでください。 省略すると「表の列の並び順」に依存します。 あとで列を1本足した瞬間、エラーも出さずに違う列へ入り始めます。
外部キーは INSERT のときにも効く
存在しない部署を指す社員を入れてみます。
INSERT INTO d_emp (emp_name, dept_id, salary) VALUES ('架空 太郎', 99, 300000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`appdb`.`d_emp`, CONSTRAINT `fk_d_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `d_dept` (`dept_id`))
入りませんでした。 外部キーは「消すとき」だけの仕組みではなく、「入れるとき」にも存在を確かめています。
エラーの読み方そのものは、本線のエラーは下から読む — メッセージの読み方と調べ方にあります。
SELECT の結果をそのまま入れる
別の表にたまっているデータを流し込むときの書き方です。 VALUES を SELECT に置き換えるだけです。
まず、入る前に数えます(打つ前の①手目です)。
SELECT COUNT(*) FROM d_stage;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
BEGIN;
INSERT INTO d_emp (emp_name, dept_id, salary, grade)
SELECT emp_name, dept_id, salary, 'C' FROM d_stage;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
3件。合っています。
COMMIT;
ポイントは3つです。
VALUESは書きません。INSERT INTO ... SELECT ...と続けます'C'のように、SELECT側に定数を混ぜられます。 表に無い値を足せます- 列の対応は「名前」ではなく「並び順」です。
SELECTの1列目がemp_nameに入ります。ここは名前で照合してくれません
INSERT IGNORE は使う前に止まる
重複したら黙って飛ばす、という書き方があります。
INSERT IGNORE INTO d_dept (dept_id, dept_name, floor_no) VALUES (9, '購買部(新)', 1);
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1062 | Duplicate entry '9' for key 'd_dept.PRIMARY' |
+---------+------+---------------------------------------------+
飛ばされました。名前は 購買部 のままです。
INSERT IGNOREが握りつぶすのは、重複だけではありません。 型が合わない・NOT NULLにNULLを入れた・値が長すぎて切られた、そういう本物の異常も警告に格下げして通します。 「重複だけ飛ばしたい」つもりで使うと、壊れたデータが黙って入ります。 重複を飛ばしたいなら、次のON DUPLICATE KEY UPDATEを使うほうが安全です。
ON DUPLICATE KEY UPDATE — 無ければ入れる、あれば直す
「あるかどうか調べてから、INSERT か UPDATE を選ぶ」を1文でやる書き方です。
いまの状態を見ます。
SELECT * FROM d_emp_kpi WHERE emp_id IN (3, 6);
+--------+--------+--------+--------------------+
| emp_id | target | actual | memo |
+--------+--------+--------+--------------------+
| 3 | 800 | 640 | 9月に挽回予定 |
+--------+--------+--------+--------------------+
emp_id = 3 はあり、6 はありません。両方に同じ文を打ちます。
INSERT INTO d_emp_kpi (emp_id, target, actual) VALUES (3, 800, 700) AS new
ON DUPLICATE KEY UPDATE actual = new.actual;
Query OK, 2 rows affected (0.01 sec)
INSERT INTO d_emp_kpi (emp_id, target, actual) VALUES (6, 500, 480) AS new
ON DUPLICATE KEY UPDATE actual = new.actual;
Query OK, 1 row affected (0.00 sec)
結果を見ます。
SELECT * FROM d_emp_kpi WHERE emp_id IN (3, 6);
+--------+--------+--------+--------------------+
| emp_id | target | actual | memo |
+--------+--------+--------+--------------------+
| 3 | 800 | 700 | 9月に挽回予定 |
| 6 | 500 | 480 | NULL |
+--------+--------+--------+--------------------+
3 は actual だけ更新されて、memo は残っています。6 は新しく入りました。
返ってきた行数に注目してください。
| 何が起きたか | rows affected |
|---|---|
| 新しく入った | 1 |
| 既存が更新された | 2(更新したことを示す決まり。行は消していない) |
| 既存があったが値が同じだった | 0 |
2 rows affectedを見て「2行入った」と読むと間違えます。 更新1件です。 アプリ側で「入ったか更新したか」を件数で判断するコードは、この仕様を知らないと壊れます。
VALUES (...) AS newという書き方は MySQL 8.0.19 以降です。 それより古い場合は、AS newを消してON DUPLICATE KEY UPDATE actual = VALUES(actual)と書きます。VALUES()関数のほうは非推奨になっているので、8.0 なら新しい書き方を使ってください。
REPLACE — 上書きではなく、消してから入れ直す
ここが今日いちばん怖いところです。
さっきの emp_id = 3 には memo が入っています。
SELECT * FROM d_emp_kpi WHERE emp_id = 3;
+--------+--------+--------+--------------------+
| emp_id | target | actual | memo |
+--------+--------+--------+--------------------+
| 3 | 800 | 700 | 9月に挽回予定 |
+--------+--------+--------+--------------------+
REPLACE で actual を直します。memo は触っていません。
REPLACE INTO d_emp_kpi (emp_id, target, actual) VALUES (3, 800, 750);
Query OK, 2 rows affected (0.01 sec)
SELECT * FROM d_emp_kpi WHERE emp_id = 3;
+--------+--------+--------+------+
| emp_id | target | actual | memo |
+--------+--------+--------+------+
| 3 | 800 | 750 | NULL |
+--------+--------+--------+------+
memo が消えました。
理由は名前のとおりです。
REPLACEは「該当行をDELETEしてからINSERTする」という動きです。 「書かなかった列」は、更新されないのではなく、行ごと消えて作り直されているので、既定値かNULLに戻ります。
ON DUPLICATE KEY UPDATE との違いを並べます。
ON DUPLICATE KEY UPDATE | REPLACE | |
|---|---|---|
| やっていること | 既存行を UPDATE | DELETE してから INSERT |
| 書かなかった列 | そのまま残る | 既定値か NULL に戻る |
AUTO_INCREMENT | そのまま | 新しい番号が振られることがある |
ON DELETE CASCADE の子 | 残る | 一緒に消える |
3つ目が特に危険です。 REPLACE の内部の DELETE は本物の DELETE なので、その行を親として ON DELETE CASCADE でぶら下がっている子の行が、まとめて消えます。 UPDATE のつもりで打った1文が、別の表を空にします。
この記事の d_emp は d_emp_kpi の親です。 d_emp に REPLACE を打つと、その社員の KPI が消えます。
結論はこれです。
REPLACEは「全部の列を必ず書く」ときにだけ使う。 一部の列だけ直したいなら、ON DUPLICATE KEY UPDATEを使う。
消えた memo を戻しておきます。
UPDATE d_emp_kpi SET memo = '9月に挽回予定' WHERE emp_id = 3;
④ LAST_INSERT_ID() — 採番された番号を受け取る
AUTO_INCREMENT の番号は、入れてみるまで分かりません。 「入れた行のIDを使って、次の表にも入れる」ときに要ります。
1行入れたとき
INSERT INTO d_emp (emp_name, dept_id, salary, grade) VALUES ('渋谷 蒼', 5, 390000, 'C');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 34 |
+------------------+
そのまま使えます。(番号そのものは、手元と記事で違っていてかまいません。③の「架空 太郎」のようにエラーで入らなかった INSERT でも番号は消費されて戻らないため、ここから先の番号が記事とずれることがあります)
INSERT INTO d_emp_kpi (emp_id, target, actual) VALUES (LAST_INSERT_ID(), 400, 0);
SELECT MAX(emp_id) FROM d_emp;で代用してはいけません。 他の人が同時に入れていたら、他人の番号を拾います。LAST_INSERT_ID()は自分の接続の中だけを見るので、同時に何人使っていても安全です。
複数行入れたとき — 返るのは先頭
ここが引っかかるところです。
INSERT INTO d_emp (emp_name, dept_id, salary, grade) VALUES
('世田谷 涼', 1, 350000, 'C'),
('大田 望', 1, 340000, 'C'),
('目黒 陽', 3, 330000, 'C');
SELECT LAST_INSERT_ID(), ROW_COUNT();
+------------------+-------------+
| LAST_INSERT_ID() | ROW_COUNT() |
+------------------+-------------+
| 35 | 3 |
+------------------+-------------+
入ったのは 35・36・37 です。返ってきたのは 35。最後ではなく先頭です。
確かめます。
SELECT emp_id, emp_name FROM d_emp WHERE emp_id >= 35 ORDER BY emp_id;
+--------+-----------------+
| emp_id | emp_name |
+--------+-----------------+
| 35 | 世田谷 涼 |
| 36 | 大田 望 |
| 37 | 目黒 陽 |
+--------+-----------------+
入れた3件のIDを全部知りたいなら、LAST_INSERT_ID() と ROW_COUNT() の両方が要ります。
| 関数 | 返すもの |
|---|---|
LAST_INSERT_ID() | 直前の INSERT で採番された最初のID |
ROW_COUNT() | 直前の文が触った行数 |
「先頭から連番」が成り立つのは、
VALUESに行を並べた普通のINSERTのときだけです。INSERT ... SELECTは入る行数が事前に分からないので、番号が飛ぶことがあります。 ここに連番を仮定したコードを書かないでください。
ON DUPLICATE KEY UPDATEが「更新」に転んだとき、LAST_INSERT_ID()は当てになりません。 更新された行のIDが返るとは限りません。IDが要るなら、そのあとでSELECTして取り直してください。
入れた練習用の行を戻しておきます。
DELETE FROM d_emp WHERE emp_id >= 34;
Query OK, 4 rows affected (0.01 sec)
d_emp_kpiに入れたemp_id = 34の行も一緒に消えています。ON DELETE CASCADEが効きました。⑥ で確かめます。
⑤ UPDATE の細かいところ
同じ文の中で、他の列を参照するとどうなるか
「給料を1万円上げて、賞与を新しい給料の2倍にする」 を1文で書きます。
対象は 松本 真(emp_id = 8、給料 295000、賞与 120000)です。
BEGIN;
UPDATE d_emp
SET salary = salary + 10000,
bonus = salary * 2
WHERE emp_id = 8;
SELECT emp_id, emp_name, salary, bonus FROM d_emp WHERE emp_id = 8;
+--------+-----------+--------+--------+
| emp_id | emp_name | salary | bonus |
+--------+-----------+--------+--------+
| 8 | 松本 真 | 305000 | 610000 |
+--------+-----------+--------+--------+
bonus は 610000。305000 × 2 です。つまり bonus の計算に使われた salary は、更新後の値でした。
ROLLBACK;
順番を入れ替えて、同じことをやります。
BEGIN;
UPDATE d_emp
SET bonus = salary * 2,
salary = salary + 10000
WHERE emp_id = 8;
SELECT emp_id, emp_name, salary, bonus FROM d_emp WHERE emp_id = 8;
+--------+-----------+--------+--------+
| emp_id | emp_name | salary | bonus |
+--------+-----------+--------+--------+
| 8 | 松本 真 | 305000 | 590000 |
+--------+-----------+--------+--------+
590000 になりました。295000 × 2 です。今度は更新前の値が使われました。
ROLLBACK;
同じ2つの代入を、書く順番だけ変えたら答えが変わりました。
MySQL は
SETの代入を、書いた順(左から右)に評価します。 後ろの式は、前の代入の結果を見ます。
これは MySQL のやり方で、SQL の共通の決まりではありません。 標準SQL(PostgreSQL など)は、SET の右辺を全部「更新前の値」で評価します。 つまり PostgreSQL では、上の2つはどちらも 590000 になります。
移植したときに黙って数字が変わる、という形で出ます。 2つ目のデータベースを触る話は本線のMySQLとPostgreSQLの違いは1割 — 移して確かめるにあります。
対策は単純です。
1つの
UPDATEの中で、同じ文が更新している列を参照しない。 どうしても要るなら、bonus = (salary + 10000) * 2のように、参照ではなく式を書き下す。
JOIN しながら更新する
「部署ごとに決まった昇給率をかける」 をやります。率は d_raise にあります。
SELECT * FROM d_raise;
+---------+-------+
| dept_id | rate |
+---------+-------+
| 1 | 1.030 |
| 2 | 1.050 |
| 3 | 1.010 |
| 4 | 1.000 |
| 5 | 1.020 |
+---------+-------+
① 何行に当たるか数えます。 同じ JOIN を SELECT で書きます。
SELECT COUNT(*)
FROM d_emp e
JOIN d_raise r ON e.dept_id = r.dept_id;
+----------+
| COUNT(*) |
+----------+
| 31 |
+----------+
いま社員は33人います(③ で3人増えました)。当たるのは31人です。 新規事業室(d_raise に行が無い)の1人と、dept_id が NULL の1人が外れています。
JOINで更新すると、対応する行が無いものは静かに更新されません。 エラーは出ません。UPDATEの前に同じJOINをSELECTで打つのは、この「外れる行」を見つけるためでもあります。
② ③ 実行します。
BEGIN;
UPDATE d_emp e
JOIN d_raise r ON e.dept_id = r.dept_id
SET e.salary = FLOOR(e.salary * r.rate);
Query OK, 27 rows affected (0.01 sec)
Rows matched: 31 Changed: 27 Warnings: 0
Rows matched: 31。① と一致しました。 Changed: 27 と4件少ないのは、経理部 の率が 1.000 で、かけても値が変わらなかったからです。当たってはいます。 ここで見るのは Rows matched のほうです。
書き方の要点です。
| 位置 | 書くもの |
|---|---|
UPDATE の直後 | 表と JOIN(SELECT の FROM に相当する場所) |
SET | JOIN の後ろ。 どの表の列かを e.salary のように必ず修飾する |
WHERE | SET の後ろ(今回は無し) |
COMMIT;
JOIN した UPDATE には ORDER BY と LIMIT を付けられません。
UPDATE d_emp e JOIN d_raise r ON e.dept_id = r.dept_id
SET e.salary = e.salary + 1
ORDER BY e.emp_id LIMIT 5;
ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
これは次の節の「少しずつ更新する」と衝突します。 分割したいなら、WHERE で範囲を区切るしかありません。
更新する表を、サブクエリで読めない
「平均給与より低い人の等級を C にする」 を素直に書きます。
UPDATE d_emp
SET grade = 'C'
WHERE salary < (SELECT AVG(salary) FROM d_emp);
ERROR 1093 (HY000): You can't specify target table 'd_emp' for update in FROM clause
「更新しようとしている表を、FROM の中で読むことはできない」と言われています。
理由は、更新しながら同じ表を読むと、読んでいる途中で中身が変わってしまうからです。MySQL はそれを許していません。
回避は「サブクエリを、もう1枚のかっこで包む」だけです。
BEGIN;
UPDATE d_emp
SET grade = 'C'
WHERE salary < (SELECT a FROM (SELECT AVG(salary) AS a FROM d_emp) AS x);
Query OK, 6 rows affected (0.01 sec)
Rows matched: 19 Changed: 6 Warnings: 0
通りました。 平均より低いのは19人で、そのうち13人はもともと C だったので Changed は6です。
なぜ通るのかというと、(SELECT ... ) AS x の形にした瞬間、MySQL はその結果を先に計算して一時的な表にするからです。そこから先は d_emp を読んでいないので、制限に引っかかりません。
AS xの別名は省略できません。 書かないとERROR 1248 (Every derived table must have its own alias)になります。
同じことを JOIN で書くこともできます。こちらのほうが読みやすいことが多いです。
ROLLBACK;
BEGIN;
UPDATE d_emp e
JOIN (SELECT AVG(salary) AS a FROM d_emp) x
SET e.grade = 'C'
WHERE e.salary < x.a;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 19 Changed: 6 Warnings: 0
ROLLBACK;
これは MySQL の制限です。 PostgreSQL では最初の素直な書き方がそのまま通ります。「SQLの決まり」ではなく「MySQL の都合」だと知っておいてください。
ORDER BY と LIMIT で、少しずつ更新する
100万行の表に UPDATE を1発打つと、何が起きるか。
- 全部が1つのトランザクションになるので、その間ずっとロックを持ち続けます
- 他のアプリが待たされます。長いと詰まります
- 失敗したら100万行分を巻き戻すので、失敗の処理のほうが長くかかることがあります
定石は「区切って何度も打つ」です。
BEGIN;
UPDATE d_emp
SET grade = 'C'
WHERE grade IS NULL
ORDER BY emp_id
LIMIT 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
もう一度打ちます。
UPDATE d_emp
SET grade = 'C'
WHERE grade IS NULL
ORDER BY emp_id
LIMIT 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
0 rows になりました。ここで終わりです。
この形が使えるための条件は2つです。
WHEREの条件が、更新したら成り立たなくなること。grade IS NULLを'C'で埋めれば、その行は次からは当たりません。
salary = salary + 1 のような「何度でも当たる」条件でやると、永久に終わりません
ORDER BYを必ず付けること。 付けないとどの10行が選ばれるか決まりません。飛ばされる行が出ます
本番では、1回ごとに
COMMITします。 全部を1つのBEGINで囲むと、分割した意味がありません(結局ずっとロックを持ちます)。0 rowsが返るまで繰り返す。 これが大きな表を触るときの型です。
ROLLBACK;
⑥ DELETE の細かいところ
JOIN しながら削除する
新規事業室 の社員を消します。 部署名しか手元に無い、という想定です。
① 数えます。
SELECT COUNT(*)
FROM d_emp e
JOIN d_dept d ON e.dept_id = d.dept_id
WHERE d.dept_name = '新規事業室';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
② ③ 実行します。
BEGIN;
DELETE e
FROM d_emp e
JOIN d_dept d ON e.dept_id = d.dept_id
WHERE d.dept_name = '新規事業室';
Query OK, 1 row affected (0.01 sec)
④ 一致したので確定します。
COMMIT;
DELETEとFROMの間のeを書き忘れないでください。DELETE FROM d_emp e JOIN ...と書くとERROR 1064(構文エラー)になります。eは「どの表から消すのか」の指定です。JOINで2枚並んでいるので、MySQL は指定が無いと決められません。DELETE e, d FROM ...と両方書けば、2枚から同時に消せます。 つまり 書き忘れではなく、書き間違えると2枚消えます。
外部キーに拒否される
空になった 新規事業室 を消します。
DELETE FROM d_dept WHERE dept_id = 6;
Query OK, 1 row affected (0.01 sec)
消えました。社員がいなくなっていたからです。
では、社員がいる 営業部 を消してみます。
DELETE FROM d_dept WHERE dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`appdb`.`d_emp`, CONSTRAINT `fk_d_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `d_dept` (`dept_id`))
拒否されました。
これは邪魔をされているのではなく、守られています。 消してしまったら、営業部 の8人の dept_id がどこも指していない番号になります。外部キーは、それを起こさせないためにあります。
エラーメッセージの読み方はこうです。
| 断片 | 意味 |
|---|---|
Cannot delete or update a parent row | 親(指されている側)を消そうとした |
`appdb`.`d_emp` | 文句を言っているのは d_emp(子のほう) |
CONSTRAINT `fk_d_emp_dept` | 引っかかった外部キーの名前 |
外部キーに名前を付けておくと、この行で原因がすぐ分かります。 CONSTRAINT fk_d_emp_dept と書いたのはそのためです。
連鎖して消える
d_emp_kpi のほうは ON DELETE CASCADE を付けました。
いま何件あるか見ます。
SELECT COUNT(*) FROM d_emp_kpi;
+----------+
| COUNT(*) |
+----------+
| 7 |
+----------+
emp_id = 5 の社員を消します。KPI にもこの人の行があります。
BEGIN;
DELETE FROM d_emp WHERE emp_id = 5;
Query OK, 1 row affected (0.01 sec)
1 row affected です。KPI を数えます。
SELECT COUNT(*) FROM d_emp_kpi;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
7件が6件になりました。KPI も消えています。
1 row affectedは嘘ではありませんが、全部でもありません。 連鎖で消えた行は、この数字に入りません。 「1行消した」つもりで、実際には別の表からも消えています。 子の表が3枚あって、そのまた子が2枚あったら、全部たどって消えます。rows affectedは最後まで1のままです。
戻します。
ROLLBACK;
SELECT COUNT(*) FROM d_emp_kpi;
+----------+
| COUNT(*) |
+----------+
| 7 |
+----------+
連鎖した削除も、トランザクションの中なら一緒に戻ります。 ここが BEGIN を打っておく価値です。
設計としてどちらを選ぶかは、こう考えます。
| 設定 | 向いている関係 |
|---|---|
既定(RESTRICT) | 子が独立した意味を持つとき。 社員を消すのに部署が消えては困る |
ON DELETE CASCADE | 子が親の一部でしかないとき。 注文を消したら明細も消える |
ON DELETE SET NULL | 子は残したいが、繋がりだけ切りたいとき |
LIMIT で分割して消す
⑧ で d_bulk に5000行入れるので、その節のあとでやります。(先に読みたい場合は ⑧ の最後を見てください)
TRUNCATE は DELETE の速い版ではない
「全部消す」ときに TRUNCATE TABLE を使うことがあります。速いです。 ただし、DELETE とは別物です。
DELETE FROM t | TRUNCATE TABLE t | |
|---|---|---|
WHERE | 書ける | 書けない。必ず全部 |
ROLLBACK | 戻せる | 戻せない(DDL扱いで、打った瞬間に確定する) |
AUTO_INCREMENT | そのまま続く | 1に戻る |
| 外部キーで指されている表 | 拒否される場合がある | 拒否される |
| 速さ | 行数なりに遅い | 一瞬 |
TRUNCATEはBEGINの中で打っても戻りません。 それどころか、打った時点で、それまでのBEGINの内容まで確定します(暗黙のコミット)。 「戻せるつもりで打つTRUNCATE」は、この記事で一番危ない操作です。
⑦ 論理削除という選択
消さずに「消した印」を立てる
d_emp と d_member に deleted_at という列を作ってあります。
DELETE の代わりに、こう打ちます。
BEGIN;
UPDATE d_member SET deleted_at = NOW() WHERE email = 'b@example.com';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM d_member;
+-----------+---------------+----------+---------------------+
| member_id | email | nickname | deleted_at |
+-----------+---------------+----------+---------------------+
| 1 | a@example.com | あお | NULL |
| 2 | b@example.com | びい | 2026-09-18 10:12:44 |
| 3 | c@example.com | しい | NULL |
+-----------+---------------+----------+---------------------+
行は残っています。「消した日時」が入っただけです。
NOW()が返す時刻がどのタイムゾーンなのかは、必ず一度確かめてください。 ここでずれる話は本線の9時間ずれるのは全部同じ理由 — 日付と時刻の扱いにあります。
利点は本物です。
- 間違えて消しても、
UPDATE ... SET deleted_at = NULLで戻る - その人が過去に作ったデータとの繋がりが切れない(注文履歴の「誰が」が消えない)
- 「いつ消したか」が記録として残る
必ず増える手間 — その1: 全部のクエリに条件が要る
論理削除を入れた瞬間、SELECT の意味が変わります。
SELECT COUNT(*) FROM d_member;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
「会員は3人」ではありません。生きているのは2人です。 正しくはこうです。
SELECT COUNT(*) FROM d_member WHERE deleted_at IS NULL;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
これは「1箇所直せば済む」話ではありません。 一覧・検索・件数・集計・
JOINの相手側、アプリの中の全部の問い合わせにAND deleted_at IS NULLが要ります。 1つ忘れると、消したはずの人が画面に出ます。 そしてエラーは出ません。
現実的な対策は3つで、どれも完全ではありません。
| 対策 | できること | できないこと |
|---|---|---|
ビューを作る(CREATE VIEW v_member AS SELECT ... WHERE deleted_at IS NULL) | v_member を使う限り安全 | 元の表を直接読むコードは防げない |
| ORM の既定条件に入れる | アプリからは自動で付く | 手書きSQL・管理画面・集計バッチは別 |
| 人が気をつける | — | 忘れます |
ORM でこれをやる話は、本線のALTER TABLE を手で打たない — ORMとマイグレーションの続きになります。
必ず増える手間 — その2: ユニーク制約が効かなくなる
ここが一番きついところです。
d_member には email にユニーク制約があります。 b@example.com は論理削除済みです。同じメールで入り直せるはずです。
INSERT INTO d_member (email, nickname) VALUES ('b@example.com', 'びい2');
ERROR 1062 (23000): Duplicate entry 'b@example.com' for key 'd_member.uk_d_member_email'
入れません。 行が残っているので、制約から見れば「まだ使われているメール」です。
思いつく直し方はこれです。「メールと削除日時の組で一意にする」。
ALTER TABLE d_member DROP INDEX uk_d_member_email;
ALTER TABLE d_member ADD UNIQUE KEY uk_d_member_email2 (email, deleted_at);
さっきの INSERT を打ちます。
INSERT INTO d_member (email, nickname) VALUES ('b@example.com', 'びい2');
Query OK, 1 row affected (0.00 sec)
通りました。……が、ここで喜ぶと事故ります。もう一度打ちます。
INSERT INTO d_member (email, nickname) VALUES ('b@example.com', 'びい3');
Query OK, 1 row affected (0.00 sec)
通ってしまいました。
SELECT member_id, email, nickname, deleted_at FROM d_member WHERE email = 'b@example.com';
+-----------+---------------+----------+---------------------+
| member_id | email | nickname | deleted_at |
+-----------+---------------+----------+---------------------+
| 2 | b@example.com | びい | 2026-09-18 10:12:44 |
| 4 | b@example.com | びい2 | NULL |
| 5 | b@example.com | びい3 | NULL |
+-----------+---------------+----------+---------------------+
生きている会員が、同じメールで2人います。制約が守るべきだったものが、守られていません。
理由は NULL です。
ユニーク制約は
NULLを「重複」と見なしません。NULLは「値が無い」ので、「等しい」と判定されないからです。(email, deleted_at)の組にした瞬間、生きている行はどれもdeleted_atがNULLなので、いくつでも並べられます。
NULL のこの性質は、この深掘りシリーズのWHEREで別名が使えない理由 — SQL深掘りで扱ったものと同じです。 ここでも同じ形で刺さります。
本当の直し方は「NULL を使わない」です。 削除済みでないことを、NULL ではない値で表します。
DELETE FROM d_member WHERE member_id IN (4, 5);
ALTER TABLE d_member DROP INDEX uk_d_member_email2;
ALTER TABLE d_member
MODIFY deleted_at DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00';
NULL だった行を、その既定値に揃えます。
UPDATE d_member SET deleted_at = '1000-01-01 00:00:00' WHERE deleted_at IS NULL;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
ALTER TABLE d_member ADD UNIQUE KEY uk_d_member_email3 (email, deleted_at);
もう一度、生きている会員を2人作ろうとします。
INSERT INTO d_member (email, nickname) VALUES ('b@example.com', 'びい2');
Query OK, 1 row affected (0.00 sec)
INSERT INTO d_member (email, nickname) VALUES ('b@example.com', 'びい3');
ERROR 1062 (23000): Duplicate entry 'b@example.com-1000-01-01 00:00:00' for key 'd_member.uk_d_member_email3'
今度は止まりました。
ただし、代償が付いています。
- 「未削除」を
1000-01-01という嘘の日付で表している。 意味を知らない人が読むと混乱します WHERE deleted_at IS NULLが全部使えなくなった。WHERE deleted_at = '1000-01-01 00:00:00'に書き換えです- 同じ人を2回消して2回入り直すと、2回目の削除でまた重複します(削除日時が同じになる可能性は低いですが、ゼロではありません)
ROLLBACK;
上の
ALTER TABLEはROLLBACKで戻りません。TRUNCATEと同じで、DDL は打った瞬間に確定します。d_memberは変更されたままです。 後片づけで丸ごと消すので、この記事では問題ありません。 本番の表でALTER TABLEを打つ前に「戻せない」ことを思い出してください。
結論 — 入れるかどうか
論理削除は「安全な削除」ではありません。「削除をやめて、状態を1つ増やすこと」です。
| 入れる価値がある | 入れないほうがいい |
|---|---|
| 消えたら困る(会員・注文・請求) | ログ・キャッシュ・一時データ |
| 監査で「いつ消したか」を聞かれる | 一意制約が仕事の中心にある表 |
| 消したあとで復元する運用が実在する | 「念のため」しか理由が無いとき |
「念のため全部の表に
deleted_atを付ける」は、たいてい失敗します。 条件の書き忘れが積み上がって、どのクエリが正しいのか誰も分からなくなるからです。 本当に消したくないものだけに付けて、他は普通にDELETEして、消したものはバックアップから戻す。 そのほうが単純です。
⑧ 一括投入を速くする — 実際に測る
5000行を3つの方法で入れて、時間を比べます。
測るためのファイルを作る
mysql から一度抜けます。
EXIT;
Ubuntu のシェルに戻りました。 sudo のパスワードを先に通しておきます(測定中に聞かれないようにするためです)。
3つのSQLファイルを作ります。
できたか確かめます。
5000 /home/ubuntu/bulk_one.sql
5002 /home/ubuntu/bulk_tx.sql
5001 /home/ubuntu/bulk_multi.sql
行数が合っていれば大丈夫です。
測る
1つずつ、間に空にしながら流します。
real 0m28.412s
user 0m0.271s
sys 0m0.402s
real 0m0.844s
user 0m0.238s
sys 0m0.311s
real 0m0.166s
user 0m0.022s
sys 0m0.019s
[スクリーンショット: 03_一括投入の実測.png] 3つの time の結果が縦に並んでいる画面を撮ってください。
この実機での結果です。
| 入れ方 | 時間 | 1行目との比 |
|---|---|---|
1行ずつ INSERT(autocommit) | 28.4秒 | — |
1行ずつ INSERT(BEGIN で囲む) | 0.84秒 | 約34倍速い |
1文にまとめる(VALUES を並べる) | 0.17秒 | 約170倍速い |
数字は環境で変わります。順序は変わりません。
なぜこれだけ違うのか
理由は2つで、どちらも「SQLの書き方」ではありません。
| 何が遅いのか | どれが効いたか |
|---|---|
COMMIT のたびにディスクへ書いて、書けたことを確かめている | BEGIN で囲むと、5000回が1回になる |
| 1文ごとにサーバーと往復して、解析して、実行計画を立てている | 1文にまとめると、5000回が1回になる |
28秒のうち、ほとんどは INSERT そのものではありませんでした。
「1文にまとめる」は、いくらでも大きくできるわけではありません。 1つの文の大きさには上限があります(
max_allowed_packet)。数千行ずつに区切って何回かに分けるのが普通です。 区切りが大きすぎると、失敗したときに巻き戻す量も増えます。
LOAD DATA という道具がある
CSVのようなテキストファイルを、直接読み込ませる文です。
LOAD DATA INFILE '/var/lib/mysql-files/emp.csv'
INTO TABLE d_bulk
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
INSERT をまとめるよりさらに速いことが多く、何十万行を入れるときの本命です。
この記事では名前を出すだけにします。 実際に使うには、
- サーバーが読んでよい場所が制限されている(
secure_file_priv) - 手元のファイルを送る
LOAD DATA LOCALは、サーバーとクライアントの両方で許可が要る(local_infile) - 許可の設定は、悪用されうるので既定で閉じている
という設定の話が先に来ます。「速くしたい」より先に「なぜ閉じているのか」を理解する必要があるので、別の回に回します。
約束していた DELETE の分割
いま d_bulk に5000行あります。 少しずつ消します。
SELECT COUNT(*) FROM d_bulk WHERE n <= 1000;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1000行を、200行ずつ消します。
DELETE FROM d_bulk WHERE n <= 1000 ORDER BY id LIMIT 200;
Query OK, 200 rows affected (0.01 sec)
0 rows affected が返るまで、同じ文を繰り返します。 5回で終わります。
SELECT COUNT(*) FROM d_bulk WHERE n <= 1000;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
UPDATE のときと条件は同じです。
- 消せば条件から外れること(消えた行は次から当たらない。
DELETEは必ずこれを満たします) ORDER BYを付けること- 1回ごとに
COMMITすること
DELETEはUPDATEより分割の価値が高いです。 消した行は、消えて終わりではなく「巻き戻せるように取っておく情報」として残ります(ROLLBACKできる必要があるため)。 100万行を1発で消すと、その情報が全部たまります。 分割すると、COMMITのたびに片づきます。
⑨ 消してしまったときに戻す道
②で見たとおり、autocommit のまま打った DELETE は ROLLBACK で戻りません。 では、確定してしまったあとに戻す道はあるのか。 あります。ただし、事前に用意していた場合だけです。
道は3つ、全部「先に用意するもの」
| 道 | どこまで戻せるか | 先に要る準備 |
|---|---|---|
バックアップ(mysqldump など) | バックアップを取った時点まで | 定期的に取っていること |
| バイナリログ(binlog) | 事故の直前の瞬間まで | 有効になっていること+バックアップがあること |
控えの表(②でやった CREATE TABLE ... AS SELECT) | 控えた時点まで | 危ない操作の前に自分で作ること |
3つ目は、この記事で実際に助かった道です。 大きな UPDATE を打つ前の30秒で作れます。一番手軽で、一番使う機会が多いと思います。
バックアップ
名前と、1行だけ。
中身はただのテキストで、CREATE TABLE と INSERT が並んでいるだけです。開いて読めます。
バイナリログ
MySQL は、行が変わるたびに「どう変わったか」を順番に記録しています。
SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
ON なら記録されています(MySQL 8.0 の既定は ON です)。
これがあると、「バックアップを戻したあと、そこから事故の1秒前までを再生する」ことができます。 この復旧のやり方(ポイントインタイムリカバリ)は、それだけで1本になります。
壊して直す練習は、本線の障害対応は切り分けの順番がすべて — わざと壊して直すでやっています。 データベースの復旧はそこで回収します。
今日覚えて帰るのはここだけです。 「戻す道は、事故のあとに作れない」。
mysqldumpも binlog も控えの表も、全部、事故の前にしか用意できません。
つまずいたところ
1. BEGIN を打ったつもりで、別の窓に打っていた
ターミナルを2枚開いて分離レベルの実験をしたあと(本線のトランザクションの回でやりました)、片方に BEGIN、もう片方に UPDATE を打っていました。
ROLLBACK が効かなくて10分悩みました。 効かないのは当然で、UPDATE を打ったほうの接続は autocommit のままだったからです。
いまは、BEGIN のあとに必ずこれを打つようにしています。
SELECT @@in_transaction AS x;
……と打とうとして、そんな変数は無いことに気づきました。 正しくはこちらです。
SELECT @@autocommit, CONNECTION_ID();
CONNECTION_ID() が、BEGIN を打ったときと同じ番号かを見ます。 番号が違えば別の接続です。
2. 0 rows affected を「何も起きなかった」と読んだ
②で書いたとおりです。 Rows matched: 30 Changed: 0 を見て「変わっていないから大丈夫」と流したことがあります。
変わっていないのは、たまたま同じ値だっただけでした。WHERE は30行に当たっていました。 次に違う値で同じ文を打っていたら、30行が書き換わっていました。
見るのは Changed ではなく Rows matched です。
3. DELETE FROM d_emp e JOIN ... で構文エラー
ERROR 1064 (42000): You have an error in your SQL syntax; ...
SELECT の癖で FROM から書いていました。 DELETE はどの表から消すかを FROM の前に書く必要があります。
DELETE e FROM d_emp e JOIN d_dept d ON ...
さらに怖いのは、DELETE e, d FROM ... と書けてしまうことです。カンマを1つ足すと、部署のほうも消えます。 打つ前に、DELETE と FROM の間を必ず目で読むようにしました。
4. sql_safe_updates を入れたら、動いていた文が全部落ちた
WHERE は書いてあるのに ERROR 1175 が出ました。 索引が無い列を条件にしていたからです。
最初は「使い物にならない」と思って切りました。 そのあとで気づいたのですが、 「索引が無い列で WHERE している」というのは、その文が表を全部読んでいるということでもあります。
つまり ERROR 1175 は、事故の警告であると同時に、遅いクエリの警告でもありました。 切るのではなく、索引を足すほうが正解でした。
5. UPDATE ... JOIN に LIMIT を付けて ERROR 1221
大きい表を分割して更新しようとして、JOIN した UPDATE に ORDER BY ... LIMIT を付けて怒られました。
ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
JOIN した UPDATE では使えません。 分割したいときは、WHERE e.emp_id BETWEEN 1 AND 1000 のように主キーの範囲で区切るしかありませんでした。
6. ON DUPLICATE KEY UPDATE actual = VALUES(actual) が警告を出した
Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release.
動きます。動きますが、非推奨です。 8.0.19 以降は VALUES (...) AS new と別名を付けて、new.actual と書くのが新しい形でした。
7. ALTER TABLE を BEGIN の中で打って、戻せなかった
⑦の一意制約の実験で、ALTER TABLE を BEGIN の中に入れていました。 ROLLBACK しても索引は戻りませんでした。
ALTER や TRUNCATE や DROP は、トランザクションの外にいます。 それどころか、打った瞬間に、それまでの BEGIN の内容も一緒に確定させます。 「BEGIN の中だから安全」と思えるのは INSERT / UPDATE / DELETE だけでした。
後片づけ
この記事で作ったのは、appdb の中の d_ で始まる表だけです。 全部消します。
USE appdb;
まず、何があるか目で見ます。
SHOW TABLES LIKE 'd\_%';
+---------------------+
| Tables_in_appdb (d_%) |
+---------------------+
| d_bulk |
| d_dept |
| d_emp |
| d_emp_backup |
| d_emp_kpi |
| d_member |
| d_raise |
| d_stage |
+---------------------+
d\_%の\は「_を1文字として扱う」ための印です。LIKEでは_が「任意の1文字」の意味になるので、これが無いとdb_usersのような表も引っかかります。
外部キーがあるので、子から順に消します。
DROP TABLE d_emp_kpi;
DROP TABLE d_emp;
DROP TABLE d_dept;
DROP TABLE d_emp_backup;
DROP TABLE d_stage;
DROP TABLE d_raise;
DROP TABLE d_member;
DROP TABLE d_bulk;
消えたことと、本線の表が無事なことを、同時に確かめます。
SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| sessions |
| tasks |
| users |
+-----------------+
d_ で始まる表が1つも無く、tasks / users / sessions が残っていれば完了です。
EXIT;
測定に使ったファイルも消します。
残っていないか見ます。
ls: cannot access '/home/ubuntu/bulk_*.sql': No such file or directory
この表示が出れば消えています。
sql_safe_updatesは接続ごとの設定なので、EXITした時点で消えています。 戻す作業はありません。--safe-updatesを毎回付けたいなら、それは設定として別に入れる話になります。
まとめ — 理解できたこと
この記事を書く前、私は「UPDATE は SELECT に SET を足したもの」だと思っていました。 違いました。SELECT と UPDATE の間には、「戻せるか」という一線があります。
ROLLBACKは打てば戻るものではない。BEGINを打っていなければ、エラーも出さずに何もしないRows matchedとChangedは別の数字。WHEREが正しいかを教えてくれるのはRows matchedのほう- 打つ前の4手 — ①同じ条件で数える ②
BEGIN③実行して件数を見比べる ④合えばCOMMIT、違えばROLLBACK。 これだけで、この記事の事故は全部防げました sql_safe_updatesは「WHEREを打ち忘れた」事故を止めてくれる。 ただしWHERE 1=1は素通りするので、型の代わりにはならないREPLACEは上書きではなく「消して入れ直す」。 書かなかった列はNULLに戻り、ON DELETE CASCADEの子は消えるLAST_INSERT_ID()が返すのは、複数行入れたときは先頭のID。 全部知るにはROW_COUNT()と組む- MySQL は
SETの代入を左から右に評価する。 PostgreSQL は全部更新前の値で評価する。 移すと答えが変わる - 更新する表そのものをサブクエリで読めない(
ERROR 1093)。 派生表で包むと、先に計算されるので通る - 大きな表は
ORDER BY+LIMITで区切り、1回ずつCOMMITする。JOINしたUPDATEにはLIMITを付けられない - 外部キーは「拒否する」か「連鎖する」かのどちらか。 連鎖して消えた行は
rows affectedに入らない TRUNCATEとALTERはROLLBACKで戻らない。 それどころか、それまでのBEGINまで確定させる- 論理削除は安全な削除ではなく、状態が1つ増えること。 全クエリに条件が要り、ユニーク制約は
NULLのせいで効かなくなる - まとめて
INSERTすると、この実機で約170倍速かった。 速さの正体はSQLではなく、COMMITの回数と往復の回数だった - 戻す道は、事故のあとには作れない
一番効いたのは、②で BEGIN を打った場合と打たなかった場合を、同じ UPDATE で2回やったところでした。 同じ文なのに、片方は戻り、片方は戻りませんでした。 そこから先は、「打つ前に BEGIN を打ったかどうか」だけを気にすればよくなりました。
この記事の SQL のうち、REPLACE と ON DUPLICATE KEY UPDATE と UPDATE ... JOIN は MySQL 寄りの書き方です。 BEGIN / COMMIT / ROLLBACK と、INSERT / UPDATE / DELETE の骨格は、どのデータベースでも同じです。
参考リンク
- roadmap.sh — SQL
- roadmap.sh — Backend
- MySQL 公式ドキュメント(Reference Manual の Data Manipulation Statements の章に
INSERT/UPDATE/DELETE/REPLACEが並んでいます) - PostgreSQL — UPDATE(
SETの右辺が更新前の値で評価されると明記されています) - PostgreSQL — INSERT(
ON CONFLICTが MySQL のON DUPLICATE KEY UPDATEにあたります) - PostgreSQL — DELETE
- SQLite — UPSERT
- SQLite — REPLACE
- 本線: プロセスを止めても消えない場所 — MySQLとSQLの基本
- 本線: 全部やるか、1つもやらないか — トランザクションとACID
- 本線: 1つの表に全部入れると壊れる — テーブル設計と正規化
- 本線: ALTER TABLE を手で打たない — ORMとマイグレーション
- 本線: 障害対応は切り分けの順番がすべて — わざと壊して直す
- 深掘り: WHEREで別名が使えない理由 — SQL深掘り
- 深掘り: JOINは表を繋いでいない — SQL深掘り
- 深掘り: GROUP BYを付けたら行が減った — SQL深掘り
次に読む
この記事は 【深掘り】SQL の 5 / 17 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | NOT NULL・UNIQUE・CHECK制約 — アプリで弾いた値が入る |
| → 次の記事 | JOINの仕組み — 表を繋いでいない |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- INSERT / UPDATE / DELETE と打つ前の型
- 論理削除と一括投入
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント