【mysql】sqlでtableを全てdropしたい

Question

やりたい事

• 不規則な名前で作られた複数のtableを
• 毎回同じSQLで全て削除したい

条件

sqlのみで行う必要があります。
linuxコマンド等は挟めません。

sqlは複数に分けても大丈夫。
ただし都度sql文を作成はせず、コピペで行えるレベルで同じもの。

調べた事

ネット上には似たような疑問もありましたが、どれもlinuxコマンドを経由してやってました。

サブクエリ使えば出来るのかなーと思いましたが、「show tables」はサブクエリに使えない？
sqlにあまり詳しくないので申し訳ないです。

Answer 1

すべてのテーブルをテーブル定義ごと削除して良いということですよね？
であれば、drop database でデータベースごと削除しても良い気がしますが。。。

もし、「毎回」というのが、不規則でも毎回同じテーブルを削除するだけなら、drop table aaa, bbb, ccc, ddd, ... みたいな SQL を一度書いておいて、毎回コピペで実行することはできますね。

Comments

[yambejp]

「不規則な名前」がネックですね。
information_schemaが使えると思います

[katosei]

権限的にdrop databaseが使えない、というのが痛いです。
createの方も出来ないので。
なおDB名も毎回バラバラです。

Answer 2

databaseを削除するのが手っ取り早いような気がしますが
procedureをつくってcallすればいいでしょう

まず、任意のDBに入り

use 任意のdb

• procedure作成

drop procedure if exists droptables;
delimiter //
create procedure droptables()
begin 
declare a varchar(255);
declare done int default 0;
declare cur cursor for
select TABLE_NAME from `information_schema`.tables where table_schema=database() and table_type = 'BASE TABLE';
declare continue handler for sqlstate '02000' set done=1;
open cur;
repeat
fetch cur into a;
if not done then
set @sql=CONCAT('DROP TABLE IF EXISTS  ' , a );
prepare stmt from @sql;
execute stmt;
end if;
until done end repeat;
close cur;
end
//
delimiter ;

• 削除実行

call droptables;

Comments

[yambejp]

> sqlにあまり詳しくないので申し訳ないです。

とのことなので、procedureの仕組み自体理解していないかもしれません。
とにかくコピペでテストしてみてください
（消しちゃまずいデータベースでは絶対利用しないこと）

[katosei]

ありがとうございます。
無事消すことが出来ました！

一応ストアドプロシージャーの概念だけは理解しているつもりです。
(何をやってるかはかなりおぼろげですが)
最初のdropの部分まで書いてくれた気遣いがありがたいです。
ぱぱっと数行で行けるのかと思ったら、思いの外大掛かりになってしまい、お手数おかけしました。

複数のDBにコピペで実行できる事が確認出来たので、要件としては完璧に満たして頂きました。

[yambejp]

おお、柔軟な思考ができる方でよかったです
わたしはprocedureを触った最初の頃は、理解できない息苦しさで
絶対自分の感性にあわないと、投げ出したものです。
（その後どうしても必要なので勉強してなんとかなりましたが）

プロシージャ自体構文は煩雑で融通が効かないですが、やっていることは実は
大したことではないので、理解すればさほどハードルはたかくありません。
これを機会にプロシージャやファンクションの学習をしてみるとよいかもしれません。

[Orlofsky]

repeat ～ until done end repeat;
if ～ end if;
の中をそれぞれ字下げすると、ちょっと理解し易くなるかと。

Answer 3

「show tables」はサブクエリに使えない？

のではなくDROP TABLE文にはテーブル名を渡すしかなかったと思います。
https://dev.mysql.com/doc/refman/5.6/ja/drop-table.html

drop tableというキーワードでリファレンスを検索した結果がこちらです。
DROP TABLE構文はそれ以外の拡張的な構文は用意されていないと思われます。

以下余談

CREATE TABLEなら、CREATE TABLE...SELECTとかっていうのがあるんですけどね。

Comments

[katosei]

あー、もう仕様として出来ないんですね。
わからないなりに試行錯誤してみてたんですが、ちゃんとドキュメントを見るべきでした。
ありがとうございます。