こんにちは！
オズビジョン開発グループのShoです。
2019年9月~29歳で未経験から営業→エンジニアになった私が、
脱未経験エンジニアを目指して、業務の中で学んだこと、会社の業務について書いています。
私の現状については最初の投稿をご参照ください。
[最初の投稿]
今回はMySQLのDELETEとTRUNCATEの違いについてです。
どちらもテーブルのデータの削除をしますが、どういった違いがあるのでしょうか。
読んで欲しい人
- 経験1年未満のエンジニアの人
- SQLを勉強中の人
今回の背景
チームの人と[本番環境でやらかしちゃった人]のアドベントカレンダー話題で盛り上がり、
「前本番環境のDBで間違ってテーブルをTRUNCATEしちゃった人がいてさ〜〜」
「うわ...考えたくもない...」
というような会話がありました。
私はTRUNCATEについて詳しく知らなかったので、話の深刻さがわかりませんでした。
テーブル消してしまったのか、まぁでも戻せるでしょ！と思いながら、
テーブル削除のSQL文について調べました。
基本的にDELETEしか知らなかったので、その違いを知り、チームの人たちの会話の真意を知りました。
DELETEとTRUNCATEの違い
「テーブルデータの削除」という意味では同じ処理です。
SQL文にするとこんな感じです。
DELETE TABLE hoge;
TRUNCATE TABLE hoge;
２つの処理を詳しく見てみましょう。
DELETE
- テーブルのデータを一つずつ削除していく
- データのみが削除されるだけ
- AUTO_INCREMENTの値は初期化されず保持される
- トランザクション制御でROLLBACKできる
DELETEは単純にテーブルデータのみを削除します。
ロールバックによって戻すこともできます。
※AUTO_INCREMENTはカラムに値を指定しなかった場合に
MySQLが自動的に値を割り当ててくれるオプションです。
値は整数で１ずつ増加して連番となります。
例えばid1,id2がすでにあり、続いてidを指定しないで２つのデータを追加すると
id3,id4とそれぞれに自動的にidを割り当ててくれます。
TRUNCATE
- まずDROP TABLEを実行し、その後再度同じテーブルを作成する
- AUTO_INCREMENTの値も初期化される
- トランザクション制御してもROLLBACKできない
ログの取得もされず、インデックス処理もされないため、データの復元ができません
TRUNCATEは一旦、根こそぎテーブルごと削除して、全てなかったことにしてからテーブルを再度作り直すことで、テーブルのデータを削除します。作り直しといった感じです。
DELETEはテーブルの「データのみ」を一つずつ削除するだけです。
なので処理はTRNCATEの方が高速です。
そう、TRUNCATEは間違えて消した！！！が許されません。
消した本番データは戻ってこないのです。。。
ちなみにAUTO_INCREMENTのみを初期化したければ、下記のようなSQL文で可能です。
ALTER TABLE hoge auto_increment = 1;
処理が速いのはいいけど、危ないしDELETEでいいのでは？
と思うかもしれませんが、実際役立つ場面がありました。
TRUNCATEがあってよかった例
デバッグをした機能のdev環境でのテストが終わり、
コア機能のため入念なチェックをするために
本番環境のデータをdumpしてdev環境にリストアして、再度テストをすることになり
ました。
下記のSQL文で本番環境のデータをdumpし、dev環境にリストアしようとしました。
もちろんdev環境も元に戻すためdev環境のデータもdumpしました。
※具体的な名称、数値は避け{}の変数で表現してます。
// 本番データをダンプ
mysqldump -u {user} -p{password} -h {host} -t {database} {table} --quote-names --skip-lock-tables --single-transaction | gzip > {table}.{env}.{YYYYMMDD}-bk.sql.gz
// dev環境データもダンプ
mysqldump -u {user} -p{password} -h {host} -t {database} {table} --quote-names --skip-lock-tables --single-transaction | gzip > {table}.{env}.{YYYYMMDD}-bk.sql.gz
// 本番データをdev環境にリストア
gunzip < {table}.{env}.{YYYYMMDD}-bk.sql.gz | mysql -u {user} -p{password} -h {host} -D {database}
上記のリストアの結果
ERROR:Duplicate entry '1' for key 'PRIMARY'
keyの重複によりリストアできませんでした。
そこで、TRUNCATEの登場です。
一旦既存のdev環境テーブルを消して、それから本番環境のデータをリストアします。
dev環境テーブルのデータもdumpしてるのであとでリストアすれば問題ない。
//既存dev環境テーブルをTRUNCATEで削除
TRUNCATE TABLE {table};
// 本番データをdev環境にリストア
gunzip < {table}.{env}.{YYYYMMDD}-bk.sql.gz | mysql -u {user} -p{password} -h {host} -D {database}
見事に成功しました！！！
ここでDELETEを使うと、処理が遅いのはもちろん、
AUTO_INCREMENTが初期化されないため、同様のエラーとなってしまいます。
どうやら、上記のSQL文ではテーブルをDROP TABLEせず,
INSERTのみを行う処理だったようで、一旦DROPする必要があり、
dev環境を元にリストアするデータもあったのでTRUNCATEが最適でした。
(DROPも組み込んでSQL書けるみたいですが、その時は気づかず...)
このように入れ直して、戻すといった作業の時には最適だと思いました。
処理は本当に速かったです。
それぞれの処理をしっかり理解すれば適材適所で使用することができ、
効率的に作業が進められるので、なんとなくの理解は禁物ですね。
とはいえ、データを戻せないのでTRUNCATEを使用する際は十分に注意しましょう。
どなたかの参考になれば幸いです。