MySQLで全テーブルを一括削除する方法を紹介！
MySQLでデータベースの中の全てのテーブルを一括で削除したい場合があります。DROP TABLEで複数テーブルを指定することでも削除することはできますが、数が多くなれば大変です。この記事ではMySQLで全テーブルを一括削除する方法を紹介していきます。
MySQLで全テーブルを一括削除したい
MySQLでデータベースの中の全てのテーブルを一括で削除したい場合があります。
単純にDROP TABLEでカンマ区切りで複数テーブルを指定することでも削除することはできますが、数が多くなればなるほで大変でそれが万単位になると気が遠くなりますね。
この記事ではMySQLで全テーブルを一括削除する方法を紹介していきます。
MySQLで全テーブルを一括削除する方法
サンプルデータ
まずは下記のようなサンプルデータを準備しました。
mysql> SHOW tables;
+------------------+
| Tables_in_testdb |
+------------------+
| product |
| users |
+------------------+
2 rows in set (0.00 sec)
mysql> DESC product;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| color | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> DESC users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| score | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> SELECT * FROM product;
+------+--------+
| name | color |
+------+--------+
| pen | red |
| book | black |
| pen | blue |
| pen | red |
| pen | orange |
| book | white |
| book | black |
+------+--------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM users;
+----+---------+---------------------+------+-------+
| id | name | email | age | score |
+----+---------+---------------------+------+-------+
| 1 | taro | taro@example.com | 24 | 90 |
| 2 | hanako | hanako@example.com | 20 | 75 |
| 3 | jiro | jiro@example.com | 18 | 55 |
| 4 | sachiko | sachiko@example.com | 32 | 80 |
+----+---------+---------------------+------+-------+
4 rows in set (0.00 sec)それでは早速全テーブル削除していきましょう。
全テーブル削除
全テーブル削除すると言っても、結局はDROP TABLEを使用しないとテーブルを削除することはできません。
では、全テーブル削除する方法というのは、あるSQLを実行することで全テーブル削除するDROP TABLEクエリを自動生成して、その自動生成したSQL文を実行することで全テーブル削除を実現します。
全テーブル削除するDROP TABLEクエリを自動生成するSQL文は下記の通りです。
SELECT CONCAT(
'DROP TABLE ', GROUP_CONCAT(
CONCAT('`',table_name,'`')
),';'
) AS statement FROM information_schema.tables
WHERE table_schema = 'testdb' AND table_name LIKE '%';information_schemaとはデータベースやテーブル等に関するメタデータを格納しているデータベースです。
そこからtable_schemaでデータベース名を指定して、table_nameでテーブル名を指定します。この時テーブル名はLIKE句とワイルドカードを用いることで、指定したデータベース内の全てのテーブルを指定することが出来ます。
その後、GROUP_CONCAT関数で全テーブル名を連結した文字列として返すことが出来ます。
これでstatementとして全テーブル削除するSQL文を自動生成することが出来ます。
それでは、全テーブル削除するSQL文を自動生成し、自動生成したSQL文を実行してみましょう。
mysql> SELECT CONCAT(
-> 'DROP TABLE ', GROUP_CONCAT(
-> CONCAT('`',table_name,'`')
-> ),';'
-> ) AS statement FROM information_schema.tables
-> WHERE table_schema = 'testdb' AND table_name LIKE '%';
+-------------------------------+
| statement |
+-------------------------------+
| DROP TABLE `product`,`users`; |
+-------------------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE `product`,`users`;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW tables;
Empty set (0.00 sec)
ちゃんと全テーブルを削除できていることが確認できますね。
全テーブルの全データ削除
次はテーブルを削除するのではなく、全テーブルの全データを削除する方法です。
先ほどと同様に全テーブルの全データを削除するクエリを自動生成するSQL文をまずは実行します。
SELECT GROUP_CONCAT(
CONCAT('TRUNCATE TABLE ',table_name,';') SEPARATOR ' '
) AS statement FROM information_schema.tables
WHERE table_schema = 'testdb' AND table_name LIKE '%' AND table_rows != 0;table_rows != 0でデータが既に存在しないテーブルについては対象外とすることが出来ます。
それでは実行してみましょう。
mysql> SELECT GROUP_CONCAT(
-> CONCAT('TRUNCATE TABLE ',table_name,';') SEPARATOR ' '
-> ) AS statement FROM information_schema.tables
-> WHERE table_schema = 'testdb' AND table_name LIKE '%' AND table_rows != 0;
+-----------------------------------------------+
| statement |
+-----------------------------------------------+
| TRUNCATE TABLE product; TRUNCATE TABLE users; |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> TRUNCATE TABLE product; TRUNCATE TABLE users;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM product;
Empty set (0.00 sec)
mysql> SELECT * FROM users;
Empty set (0.00 sec)しっかりと全テーブルの全データを削除することが出来ました。
まとめ
MySQLで全テーブルを削除する方法に加えて、全テーブルの全データを削除する方法を紹介してきました。使う機会は稀かもしれませんが、いざというときに使えると便利ですので、ぜひ覚えておいてくださいね。
