SQL Lab — MySQL を大容量で叩いて学ぶ

この章で答える問い

行を足す・変える・消すにはどう書くか
WHERE を忘れると何が起きるか
間違った変更から、DB は何を守ってくれるか

この章はデータを変える。壊したら npm run seed:basics で元に戻る。

実物: 変えると「影響した行数」が返る

注文 44 を配達済みにする

期待: 影響した行数 1。結果の表はない

⌘/Ctrl + Enter で実行

UPDATE orders SET status = 'delivered', shipped_at = NOW() WHERE id = 44

表は返らず、計器に「影響した行数 1」と出る。UPDATE テーブル SET 列 = 値, … WHERE 条件 で、条件に合う行の列を書き換える。この「影響した行数」が、これからずっと最初に見る数字になる。

確認

期待: 1 行。status が delivered

⌘/Ctrl + Enter で実行

SELECT * FROM orders WHERE id = 44

今の値を使って更新する

期待: 影響した行数 1

⌘/Ctrl + Enter で実行

UPDATE stocks SET quantity = quantity - 1 WHERE product_id = 1

quantity = quantity - 1 のように、右辺に今の値を使える。「在庫を 1 減らす」はこう書く。

驚き: WHERE のない UPDATE

WHERE を書き忘れて実行するとどうなるか。エラーになるだろうか。影響した行数を予測してから実行する。 50 行の小さな表なので、ここで一度起こしておく。

WHERE を忘れた UPDATE

期待: 予測してから実行

⌘/Ctrl + Enter で実行

実行する前に予測する返る行数

UPDATE orders SET status = 'delivered'

影響した行数 50 のはずだ。エラーにはならない。全行が配達済みになった。 UPDATE と DELETE は、WHERE がなければ全行に効く。これは仕様であって、DB は「本当に全行ですか」とは聞かない。本番で「全注文が配達済みになった」「全顧客が退会した」は、この一行で起きる。

防ぐ習慣は 3 つ。

UPDATE / DELETE を書く前に、同じ WHERE で SELECT COUNT(*) して行数を見る。
本番の手作業は必ずトランザクションの中で行い、影響した行数を見てから確定する(第 7 章)。
手作業を減らす。データの修正はスクリプトにしてレビューを通す。

壊したので戻す。ターミナルで npm run seed:basics を実行し、上のパネルの「更新」を押してから続ける。

習慣 1 を実際にやる

先に数える

期待: 1 行。7 月より前の shipped(発送済み・未配達)の件数

⌘/Ctrl + Enter で実行

SELECT COUNT(*) FROM orders WHERE status = 'shipped' AND ordered_at < '2026-07-01'

同じ WHERE で更新する

期待: 影響した行数が上と一致する

⌘/Ctrl + Enter で実行

UPDATE orders SET status = 'delivered'
WHERE status = 'shipped' AND ordered_at < '2026-07-01'

上の COUNT と影響した行数が一致すれば、意図したとおりだ。一致しなければ WHERE を疑う。数えた 4 行と書き換わった 4 行が同じ行だということを、表の上で見る。

WHERE が選ぶ行は動詞に関係なく同じ。数えた行数と影響した行数は一致し、WHERE がなければ全 50 行に効く。

INSERT: 足す

注文を 1 件足す

期待: 影響した行数 1

⌘/Ctrl + Enter で実行

INSERT INTO orders (customer_id, status, ordered_at)
VALUES (8, 'paid', NOW())

採番された id

期待: 51

⌘/Ctrl + Enter で実行

SELECT LAST_INSERT_ID()

LAST_INSERT_ID() は、同じ接続で直前に採番された id。アプリは「今作った注文の id」をこれで得て、明細を入れるときに使う。

SELECT の結果をそのまま入れる

期待: 影響した行数 = 在庫 30 未満の商品数。ただし、すでにセール対象の商品が含まれていれば Duplicate entry のエラー

⌘/Ctrl + Enter で実行

INSERT INTO product_categories (product_id, category_id)
SELECT s.product_id, 6
FROM stocks s
WHERE s.quantity < 30

INSERT INTO … SELECT … は、SELECT の結果を行として入れる。カテゴリ 6 は「セール対象」。ただし、もともとセール対象だった商品が含まれていると、主キー(product_id, category_id)の重複でエラーになる。エラーになったなら、次の UPSERT で解決する。

UPSERT: あれば何もしない、なければ入れる

ユニーク制約に当たる

期待: エラー。Duplicate entry

⌘/Ctrl + Enter で実行

INSERT INTO categories (name) VALUES ('文具')

categories.name にはユニーク制約があり、同じ名前は 2 つ入らない。この制約を利用して「なければ入れる」を 1 文で書ける。

重複したら何もしない

期待: 影響した行数 0(もうある)

⌘/Ctrl + Enter で実行

INSERT INTO categories (name) VALUES ('文具')
ON DUPLICATE KEY UPDATE name = name

セール対象を、重複を無視して入れる

期待: 影響した行数 = 新しく入った数(重複した行は黙って飛ばされる)

⌘/Ctrl + Enter で実行

INSERT IGNORE INTO product_categories (product_id, category_id)
SELECT s.product_id, 6 FROM stocks s WHERE s.quantity < 30

INSERT IGNORE は、主キーやユニーク制約に当たった行を飛ばして残りを入れる。「あれば更新したい」なら ON DUPLICATE KEY UPDATE、「あれば何もしない」なら INSERT IGNORE、と使い分ける。「先に SELECT で存在確認 → なければ INSERT」と 2 文に分けると、2 つのリクエストが同時に来たとき両方が INSERT して失敗する(第 7 章)。ユニーク制約 + ON DUPLICATE KEY UPDATE なら DB が 1 文で片付ける。

DELETE: 消す

さっき足した注文を消す

期待: 影響した行数 1

⌘/Ctrl + Enter で実行

DELETE FROM orders WHERE id = 51

参照されている行は消せない

期待: エラー。a foreign key constraint fails

⌘/Ctrl + Enter で実行

DELETE FROM products WHERE id = 1

商品 1 は order_items から指されている。消すと、明細が存在しない商品を指す(孤児になる)。第 4 章の外部キー制約がこれを止める。消したいなら先に明細を消すか、宣言時に ON DELETE CASCADE(親を消したら子も消す)を付ける。指されている行を消そうとしたとき何が起きるかを、矢印で見る。

外部キーは矢印の先を守る。指されている商品 1 を消すと 7 行の明細が孤児になるので、DB が DELETE を拒む。

実務では、消さずに deleted_at 列に日時を入れて「消えたことにする」(論理削除)ことも多い。履歴が残り、取り消せ、参照整合性で悩まない。代わりに全クエリに WHERE deleted_at IS NULL が付き、忘れると消したはずのものが表示される。

変更の文は「影響した行数」を返す。WHERE を忘れれば全行に効き、DB は聞き返さない。制約(NOT NULL、外部キー、ユニーク)は間違った変更を止める。

確認

WHERE を忘れた UPDATE が全行を変えてしまうのはなぜか。

WHERE のない UPDATE と DELETE は全行に効くのが仕様だから。エラーにはならず、DB は「本当に全行か」と聞き返さない。

変更の文を打つ前に、同じ WHERE で数えておくのはなぜか。

WHERE が選ぶ行は、SELECT でも UPDATE でも同じだから。数えた行数と影響した行数が一致すれば意図どおりで、一致しなければ WHERE を疑える。

先に存在を確かめてから INSERT する 2 文より、ユニーク制約と UPSERT のほうが安全なのはなぜか。

2 文の間に別の接続が同じ行を入れると、確認をすり抜けて両方が INSERT してしまう。ユニーク制約があれば DB が 1 文の中で重複を弾き、あれば更新するのか何もしないのかを書き分けられる。

次の章へ

ここまでの SELECT は 1 つの表(または JOIN した表)から取り出すものだった。次の章では、SELECT の結果をもう一つの SELECT の中で使う。「平均より高い明細」「gold 会員の注文」のように、条件そのものが別の問い合わせで決まるときの書き方だ。