Edited at

ストアドプロシージャで複数テーブルのデータを全削除

More than 3 years have passed since last update.

テーブル数が増えてくると、データを一気に消したいときが結構手間です。

ストアドプロシージャをつかうと、バッチ処理なSQLが発行できて捗ります。

O/Rマッパーではなくストアドプロシージャで! - Qiita

を参考にしました。ありがとうございます。

追記:コメントにて削除時のパフォーマンスがTRUNCATE TABLE(テーブルの削除、再構築)のほうが良いと教えていただきましたので追記しました。ありがとうございます。


データを全削除(TRUNCATE TABLE版)


DROP PROCEDURE IF EXISTS TABLES_RESET;

DELIMITER $$
/* TABLES_RESET
* テーブルのデータをすべて削除する
*/

CREATE PROCEDURE TABLES_RESET
()
BEGIN
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE Address;
TRUNCATE TABLE Phone;
TRUNCATE TABLE Email;
TRUNCATE TABLE Note;
TRUNCATE TABLE Contact;
TRUNCATE TABLE User;
SET FOREIGN_KEY_CHECKS = 1;
SELECT 0 AS errid, 1 AS row_count, '正常に削除されました。' AS msg;
End
$$
DELIMITER ;

User(code),Contact(id)等の外部キー制約があるテーブルに対してTRUNCATE TABLEは失敗を返すため、SET FOREIGN_KEY_CHECKSによって外部キー制約のチェックを無効化したうえで削除を実行しています。(無効化するのは真っ当な方法なのだろうか?)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.33 TRUNCATE TABLE 構文


データを全削除(DELETE FROM版)


DROP PROCEDURE IF EXISTS TABLES_RESET;

DELIMITER $$

CREATE PROCEDURE TABLES_RESET()
BEGIN
DELETE FROM Address;
DELETE FROM Phone;
DELETE FROM Email;
DELETE FROM Note;
DELETE FROM Contact;
DELETE FROM User;
SELECT 0 AS errid, 1 AS row_count, '正常に削除されました。' AS msg;
End
$$
DELIMITER ;


削除の実行


確認なしで全削除されるので気をつけて!

CALL TABLES_RESET();



テーブルデータの削除順について

今回の例のリレーションシップはこんな感じになっています。

(DB初級者のためリレーションの貼り方とか、名前とか間違っている可能性があります。コメント、ご指摘等、歓迎です!)

image

Contactは、User(code)にリレーションが貼られているので、いきなりUserテーブルは削除できません。

それと同様に、Addressも、Contact(id)にリレーションが貼られています。

ストアドプロシージャでのデータの削除順も子→親となるように削除しています。


ER図作成ツール

ER図の作成はA5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツールでおこないました。すばらしいです。