MySQLでテーブルを一括削除したいことないですか？
例えばテスト用に何百・何千ものテーブルを作った時とか、、、
そういうとき、 phpMyAdmin の「ある機能」を使えば、
どれだけテーブルが多くてもスマート＆超簡単に削除できます。
ここではそのテーブル一括削除のやり方について、便利だったのでまとめました。
このページの目次
愚直に１つずつテーブルを DROP することもできるが...
MySQLでのテーブル削除と言えば DROP TABLE を普通は使うはず
その DROP TABLE にコンマ（ , ）で区切って渡せば、一応複数テーブルの削除もできます。
▼ 例えばこういう感じで
|
1 |
DROP TABLE `table_001`, `table_002`, `table_003`; |
もちろんこれでもOK、テーブル数が少ないときはこれでも問題ないと思います。
でも何百とか何千、もし何万とかのテーブルがあったなら・・・
１つずつテーブル名を書いてくなんて、"地獄そのもの" ですよね。(；一_一)
なので僕はそういう場面に遭遇したら、もっと効率のいい方法を使ってます。
phpMyAdminを使ったテーブル一括削除のテクニック
そのやり方は phpMyAdmin の ある機能 を使うこと
まあ ある機能 というか、あるクエリを実行するというのが正しいですね。
ただし前提条件として、
テーブル名が共通の接頭辞（プレフィックス）を持っている必要があります。
例えば次みたいに、データベース名が my_app 、そして pref_ という接頭辞（プレフィックス）のテーブルが大量にあるとしましょう。
- pref_12345
- pref_13245
- pref_24351
- pref_31524
- pref_35124
- pref_43251
- 以下延々と続く、、、
そしてこれらのテーブルを削除したい場合、まず phpMyAdmin で次のSQLを実行
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- GROUP_CONCATの最大長さを指定 SET GLOBAL group_concat_max_len=10*65535; SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT( CONCAT('`',table_name,'`') ) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'my_app' AND table_name LIKE 'pref_%'; |
これは少し分かりにくいんですが・・・
DROP TABLE のあとにDB名が my_app 、テーブル名の接頭辞が pref_ から始まるテーブル名を 'pref_12345' みたいにクォートで囲んで連結したレコードを取得してます。
この説明だと超分かりにくいんで、実際のレコード例を見せます。
それがコチラ！
|
1 |
DROP TABLE `pref_12345`, `pref_13245`, `pref_24351`, `pref_31524`, `pref_35124`, `pref_43251`, `pref_51324`; |
つまり動的に DROP TABLE クエリを取得するわけです。
あとは phpMyAdmin からこのクエリを実行すれば削除完了
ただし、あまりにクエリがでかすぎると実行できないこともあります。
その場合は、次みたいにパケットサイズを拡張してみてください。
|
1 2 3 4 |
-- MySQLの最大長さを設定 SET max_allowed_packet = 1*1024*1024; DROP TABLE `pref_12345`, `pref_13245`, `pref_24351`, ...; |
上の例はパケサイズを1MB まで拡張してる例
あとPHPで myslqi を使ってるなら、PHPコード側から設定することもできます。
▼ やり方を解説した記事
大規模なクエリはエラーが出やすいので、工夫が必要です。
テーブルの データ を一括削除（TRUNCATE）するには
こっからはテーブルそのものではなく、各テーブルのデータを全削除したい場合
つまり複数テーブルを一気に TRUNCATE TABLE したいときのテクニックです。
その場合 phpMyAdmin で次のSQLを設定（プレフィックスが pref_ の場合）
|
1 2 3 4 5 6 7 8 9 10 11 |
-- GROUP_CONCATの最大長さを指定 SET GLOBAL group_concat_max_len=10*65535; SELECT GROUP_CONCAT( CONCAT('TRUNCATE TABLE ', table_name, ';') SEPARATOR ' ' ) AS statement FROM information_schema.tables WHERE table_schema = 'my_app' AND table_name LIKE 'pref_%' AND table_rows != 0; |
さっきと違い、 TRUNCATE TABLE をテーブル数だけ連結してます。
ただし、既に空になってるテーブルは table_rows != 0 で除外してあります。
これを実行すると、次みたいな statement レコードが生成されるはず
|
1 |
TRUNCATE TABLE pref_001; TRUNCATE TABLE pref_002; TRUNCATE TABLE pref_003; TRUNCATE TABLE pref_004; TRUNCATE TABLE pref_005; |
あとはこれを実行してやれば、全テーブルのデータだけを削除できます。
テーブル削除と違い、 TRUNCATE TABLE はコンマでテーブル名が区切れません。
なので手動でSQL書くみたいな地獄の作業をするより、
こういう phpMyAdmin からクエリを自動生成してもらうのが賢いはずです。
ここまでのまとめ
何気にこのテクニック、かなり役に立ちます。
特にテスト用に大量のテーブルが必要なときとか、動的にテーブル作成するときとか
もし困ったなら、このテクニックを試してみてください。ではではﾊﾞｲﾊﾞｲ($・・)/~~~
