MySQLで学ぶSQL【INSERT / UPDATE / DELETE】

はじめに

DML（データ操作言語）のうち、データを書き込む INSERT / UPDATE / DELETE を整理します_φ(･_･

SELECT 文やテーブル定義（DDL）については、以下の記事にまとめています。

この記事でわかること

• INSERT / UPDATE / DELETE の書き方
• 同じデータを重複して挿入したときの挙動
• WHERE を省略したときに起こること
• DELETE と TRUNCATE の違い
• 更新・削除で事故を起こさないための書き方

本記事では MySQL 8.0 のリファレンスマニュアルを参照しています。ストレージエンジンは、MySQL 8.0 の既定である InnoDB を前提としています。

サンプルテーブル

本記事では、メンバーとタスクを管理する2つのテーブルを使用します。

task テーブルの member_id は member テーブルの id に対応していますが、外部キー制約はつけていません。そのため、member に存在しない値も登録できます。

以降のSQLは、いずれもこのサンプルデータの状態から実行したものとして書いています。上から順に実行した結果ではありません。

テーブルの定義は以下のとおりです。

CREATE TABLE member (
    id         INT          NOT NULL AUTO_INCREMENT,
    name       VARCHAR(50)  NOT NULL,
    email      VARCHAR(255) NOT NULL,
    point      INT          NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (email)
);
 
CREATE TABLE task (
    id         INT          NOT NULL AUTO_INCREMENT,
    title      VARCHAR(100) NOT NULL,
    status     VARCHAR(20)  NOT NULL DEFAULT 'todo',
    member_id  INT          NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

今回のサンプルでは、以下の3つの指定を使っています。

• UNIQUE (email)：同じメールアドレスを登録できないようにする制約
• DEFAULT 'todo' / DEFAULT 0：値を指定しなかったときに入るデフォルト値
• DEFAULT CURRENT_TIMESTAMP：行を挿入した時刻が自動で入るデフォルト値

INSERT

INSERT はテーブルに新しい行を追加します。

基本構文

INSERT INTO member (name, email)
VALUES ('伊藤', '[e-mail]');

INSERT INTO テーブル名 (カラム名1, カラム名2, ...)
VALUES (値1, 値2, ...);

INSERT INTO のあとにテーブル名、括弧の中に値を入れるカラム名、VALUES のあとにその値を並べます。カラム名のリストと VALUES のリストは、書いた順番どおりに対応し、個数が合わないとエラーになります。

id・point・created_at は指定していませんが、それぞれ AUTO_INCREMENT と DEFAULT によって自動で値が入ります。

カラム名のリストは省略しない

カラム名のリストを省略すると、テーブル定義の順番どおりにすべてのカラムの値を指定する必要があります。

INSERT INTO member VALUES (4, '伊藤', '[e-mail]', 0, NOW());

この書き方は、テーブル定義が変わると壊れます。

ALTER TABLE でカラムが1つ増えると、値の個数が合わずにエラーになります。カラムの並び順が変わった場合はエラーになりません。

たとえば name と email の順番が入れ替わると、どちらも文字列型なのでエラーにならず、name にメールアドレスが、email に名前が入ります。

カラム名を書いておけば、どちらの場合も影響を受けません。

複数行をまとめて挿入する

VALUES のあとの括弧をカンマで区切って並べると、1文で複数行を挿入できます。

INSERT INTO member (name, email)
VALUES
    ('渡部', '[e-mail]'),
    ('山本', '[e-mail]'),
    ('中村', '[e-mail]');

1行ずつ実行するより高速で、公式ドキュメントでも多くの行を挿入する場合の方法として挙げられています。

複数行を挿入すると、結果が以下の形式で返ります。

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

項目	意味
Records	処理された行数
Duplicates	UNIQUE 制約の値が重複したため挿入されなかった行数
Warnings	警告の件数

実際に挿入された行数は、Records から Duplicates を引いた数です。

INSERT ... SELECT

VALUES の代わりに SELECT を書くと、検索結果をそのまま挿入できます。

INSERT INTO task (title, member_id)
SELECT '週報を書く', id
FROM member;

member の全員に「週報を書く」タスクが1件ずつ追加されます。

SELECT の1つ目は member の列ではなく固定の文字列で、member の行数ぶん同じ値が返ります。

SELECT '週報を書く', id FROM member;

SELECT が返した3行が、そのまま task に挿入されます。

SELECT の列とカラム名のリストは、VALUES と同じく書いた順番どおりに対応します。

重複したときの挙動

member の email には UNIQUE 制約があるため、同じメールアドレスは登録できません。重複した行をどう扱うかは書き方によって決まり、3通りあります。

以下では、同じメールアドレスの行を1文で2行挿入したときの違いを比べます。

通常の INSERT：エラーになる

INSERT INTO member (name, email)
VALUES
    ('渡部', '[e-mail]'),
    ('渡部', '[e-mail]');

ERROR 1062 (23000): Duplicate entry '[e-mail]' for key 'member.email'

文全体がエラーになるため、何も挿入されません。

INSERT IGNORE：重複した行だけスキップする

INSERT IGNORE INTO member (name, email)
VALUES
    ('渡部', '[e-mail]'),
    ('渡部', '[e-mail]');

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

1行目だけが挿入され、2行目は警告付きでスキップされます。Records は 2 ですが、実際に挿入されたのは1行です。

ただし IGNORE が警告に変えるのは重複だけではありません。カラムの型に合わない値もエラーにならず、調整された値が挿入されます。

ON DUPLICATE KEY UPDATE：既存の行を更新する

重複した場合に、エラーにする代わりに既存の行を更新します。

INSERT INTO member (name, email)
VALUES
    ('渡部', '[e-mail]'),
    ('渡部（更新）', '[e-mail]') AS new
ON DUPLICATE KEY UPDATE name = new.name;

1行目が挿入されたあと、2行目はメールアドレスが重複するため、1行目で登録した行の name が 渡部（更新） に更新されます。テーブルに残るのは1行です。

name = new.name の右辺は、挿入しようとした行の name です。AS new は挿入しようとした行につける別名で（MySQL 8.0.19 以降）、右辺でその値を使いたいときに書きます。name = name と書くと両辺とも既存の行のカラムを指してしまうため、別名で区別する必要があります。

ON DUPLICATE KEY UPDATE name = VALUES(name) という書き方も見かけますが、VALUES() 関数は MySQL 8.0.20 で非推奨になっています。

UPDATE

UPDATE は既存の行の値を変更します。

基本構文

UPDATE task
SET status = 'done'
WHERE id = 3;

UPDATE テーブル名
SET カラム名 = 値
WHERE 条件;

SET 句で変更するカラムと値を指定し、WHERE 句で変更対象の行を絞り込みます。

複数のカラムを同時に変更する場合はカンマで区切ります。

UPDATE task
SET status = 'done', title = '発表資料を作る'
WHERE id = 3;

現在の値を使って更新する

SET の右辺には式が書けます。カラム名を書くと、そのカラムの現在の値が使われます。

UPDATE member
SET point = point + 10
WHERE id = 2;

鈴木の point は 50 から 60 になります。

WHEREを省略すると全行が更新される

WHERE を書かなかった場合、テーブルのすべての行が更新されます。

UPDATE task SET status = 'done';

書き忘れても構文としては正しいため、エラーにならずそのまま実行されます。

返ってくる行数の意味

ターミナルから接続するmysql クライアントで実行した場合、UPDATE が返すのは実際に値が変わった行数です。すでに同じ値が入っていた行は、対象になっていてもカウントされません。

たとえば直前の WHERE なしの UPDATE では、5行すべてが対象になりますが、もともと done だった2行は値が変わりません。そのため表示は以下のようになります。

Query OK, 3 rows affected (0.00 sec)
Rows matched: 5  Changed: 3  Warnings: 0

項目	意味
Rows matched	WHERE の条件に一致した行数
Changed	実際に値が変わった行数

条件は合っているのに Changed が 0 の場合は、すでに同じ値が入っている状態です。

JOINを使って更新する

UPDATE の後ろに JOIN を書くと、別のテーブルの値を条件にして更新できます。

UPDATE task AS t
JOIN member AS m ON t.member_id = m.id
SET t.status = 'done'
WHERE m.name = '鈴木';

JOIN ... ON で、task の member_id と member の id が一致する行どうしを結びつけます。更新されるのは SET に書いた task の行だけです。

鈴木が担当しているタスクがすべて done になります。

複数のテーブルを使う UPDATE では、ORDER BY と LIMIT は使えません。

DELETE

DELETE はテーブルから行を削除します。

基本構文

DELETE FROM task WHERE id = 5;

DELETE FROM テーブル名 WHERE 条件;

UPDATE と同じく、WHERE を省略するとすべての行が削除されます。

DELETE FROM task;

DELETE は削除した行数を返します。

JOINを使って削除する

DELETE でも JOIN が使えます。

以下は、member_id に対応する member がいないタスクを削除する例です。

DELETE t
FROM task AS t
LEFT JOIN member AS m ON t.member_id = m.id
WHERE m.id IS NULL;

DELETE の直後に書いた t が削除対象のテーブルです。LEFT JOIN で対応する行がない場合、m.id は NULL になるため、id = 4 のタスクだけが削除されます。

同じ条件で SELECT すれば、削除対象を事前に確認することができます。

SELECT t.*
FROM task AS t
LEFT JOIN member AS m ON t.member_id = m.id
WHERE m.id IS NULL;

t.* は t（task）のすべての列という意味です。

DELETEとTRUNCATEの使い分け

「テーブルを空にしたい」場面では TRUNCATE TABLE も使えます。似ていますが、公式ドキュメントでは TRUNCATE TABLE は、行を操作する命令（DML）ではなく、テーブルそのものを定義する命令（DDL）に分類されると明記されています。

DELETE FROM task;    -- 行を1つずつ削除する
TRUNCATE TABLE task; -- テーブルを作り直して空にする

項目	DELETE	TRUNCATE
分類	DML	DDL
削除する行の指定	WHERE で指定できる	できない（常に全行）
取り消し	ROLLBACK できる	できない（暗黙コミット）
戻り値	削除した行数	返さない（0 rows と表示）

TRUNCATE TABLE は行を1件ずつ消すのではなく、テーブルを削除して作り直します。そのため大きなテーブルでは DELETE より大幅に高速です。

一方で、暗黙コミットが発生するためロールバックできません。トランザクションで囲んでも取り消せない点が DELETE との決定的な違いです。

また、InnoDB では他のテーブルから外部キーで参照されているテーブルを TRUNCATE することができません。

事故を防ぐ書き方

SELECT は間違えても結果が変わるだけですが、UPDATE と DELETE は間違えるとデータそのものが変わります。実行前にできることを3つ挙げます。

1. 同じWHEREでSELECTしてから実行する

これから更新・削除しようとしている行を、先に SELECT で確認します。

-- まず確認する
SELECT * FROM task WHERE status = 'done';
 
-- 件数と中身が想定どおりなら実行する
DELETE FROM task WHERE status = 'done';

WHERE をコピーして使い回せるので、条件の書き間違いにも気づくことができます。

2. safe-updates モードを有効にする

sql_safe_updates とは、WHERE の書き忘れによる全行の更新・削除を防ぐ設定です。有効にすると、キー列（主キーなどインデックスのある列）を使った WHERE も LIMIT もない UPDATE / DELETE が拒否されます。

SET sql_safe_updates = 1;  -- 1 で有効、0 で無効

UPDATE task SET status = 'done';
-- ERROR 1175（WHERE がない）

UPDATE task SET status = 'done' WHERE status = 'todo';
-- ERROR 1175（status はキー列ではない）

UPDATE task SET status = 'done' WHERE id = 3;
-- 実行できる（id は主キー）

エラーになった文は実行されないため、データは変わりません。ただし防げるのは書き忘れだけで、キー列を使った条件そのものが間違っている場合は実行されます。

現在の設定は以下で確認することができます。

SELECT @@sql_safe_updates;

SET で変更した設定は現在のセッションにだけ有効で、接続し直すと元に戻ります。

mysql クライアントを --safe-updates オプション付きで起動すると、接続した時点から有効になります。

3. トランザクションで囲む

BEGIN を先に実行すると、結果を確認してから COMMIT するか ROLLBACK するかを選べます。

BEGIN;
UPDATE task SET status = 'done' WHERE status = 'todo';
SELECT * FROM task;  -- 結果を確認する
 
-- 以下のどちらかを実行する
COMMIT;    -- 想定どおりなら確定する
ROLLBACK;  -- 想定と違えば取り消す

なお、前述のとおり TRUNCATE TABLE は暗黙コミットが発生するため、この方法では取り消せません。

まとめ

• INSERT はカラム名のリストを書く。省略するとテーブル定義の変更で壊れる
• 重複時の挙動は、通常の INSERT（エラー）、INSERT IGNORE（スキップ）、ON DUPLICATE KEY UPDATE（更新）で異なる
• UPDATE / DELETE は WHERE を省略すると全行が対象になる
• TRUNCATE は DDL で、暗黙コミットが発生するためロールバックできない
• 更新・削除の前に SELECT で確認する、sql_safe_updates を有効にする、トランザクションで囲む

参考