TRUNCATE と DROP
打った瞬間に、戻す手段が無くなる
DELETE はトランザクションの中で実行すれば ROLLBACK で取り消せます。件数を間違えても、COMMIT する前なら引き返せます。
TRUNCATE と DROP にはその救いがありません。多くの DBMS でこの 2 つは暗黙にコミットされ、ROLLBACK を打っても何も戻りません。バックアップが無ければそこで終わりです。同じ「消す」でも、DELETE と残りの 2 つの間には、引き返せるかどうかという断絶があります。
| 命令 | 消えるもの | 打った後に戻せるか |
|---|---|---|
DELETE | WHERE に合った行だけ | COMMIT 前なら ROLLBACK で戻せる |
TRUNCATE | そのテーブルの全行 | 戻せない。バックアップから入れ直す |
DROP TABLE | 行に加えてテーブル定義そのもの | 戻せない。定義を書き直して作り直す |
TRUNCATE に WHERE は付きません。全行専用です。1 行ずつ消さずにテーブルを作り直すのに近い動きをするので DELETE よりずっと速く、検証用のデータを毎晩まっさらにする用途に向いています。
SQL クエリ
-- 検証用データを毎回同じ状態に戻す
TRUNCATE TABLE staging_prices;戻せないのは行だけではありません。TRUNCATE は自動採番のカウンタも初期値に戻すことがあります。つまり次に入る行の id が 1 からやり直しになり、消したはずの過去の行と同じ番号が振られます。外部のログや別システムがその番号を控えていると、別のデータを指すようになります。
DROP TABLE はテーブルの定義ごと消します。実行後は SELECT も INSERT も通りません。同じ名前で作り直しても中身は空です。
消す前にワンクッションを置く
もう使っていないはずのテーブルを片付けるとき、いきなり DROP せず、まず名前を変えて様子を見る手があります。
SQL クエリ
RENAME TABLE daily_report TO zz_old_daily_report;名前が変われば、まだ参照している処理があればその時点で落ちます。数日から数週間なにも起きないことを確かめてから消せば、判断を間違えても名前を戻すだけで復旧できます。
同じスクリプトを 2 回流すと落ちる
セットアップ用の SQL に DROP TABLE をそのまま書くと、2 回目の実行でテーブルが無くてエラーになります。
SQL クエリ
DROP TABLE IF EXISTS zz_old_daily_report;IF EXISTS を付けると、対象が無くても静かに通ります。何度流しても同じ結果になるので、環境構築や後片付けの SQL では標準的な書き方です。
本番で
TRUNCATEやDROPを打つ前に、必ず 2 つ言葉にします。バックアップは取ったか、戻す手順は書いてあるか。この 2 つが埋まらないうちは実行しない、を癖にしてください。
テーブル構造
CREATE TABLE tmp_import (
id INT PRIMARY KEY,
payload VARCHAR(100) NOT NULL
);
INSERT INTO tmp_import (id, payload) VALUES
(1, 'old-1'),
(2, 'old-2'),
(3, 'old-3');
CREATE TABLE archive_log (
id INT PRIMARY KEY,
message VARCHAR(100) NOT NULL
);
INSERT INTO archive_log (id, message) VALUES
(1, 'kept');期待される出力
| table_name |
|---|
| archive_log |