LoginSignup
3
4

More than 5 years have passed since last update.

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

Last updated at Posted at 2016-10-11

テーブル数が増えてくると、データを一気に消したいときが結構手間です。
ストアドプロシージャをつかうと、バッチ処理な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図ツールでおこないました。すばらしいです。

3
4
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
4