mySQL 仅使用 SQL 语句删除带有通配符的表? [英] mySQL drop tables with Wildcard using only SQL statement?
本文介绍了mySQL 仅使用 SQL 语句删除带有通配符的表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
看到很多使用通配符删除表而不是直接 SQL 语句,除了这个:
Seen a lot for dropping tables using a wildcard but not a direct SQL statement except this one:
http://azimyasin.wordpress.com/2007/08/11/mysql-dropping-multiple-tables/
它说:
SHOW TABLES LIKE ‘phpbb_%’;
然后是 DROP TABLES,有没有一种巧妙的方法将所有这些组合成一个 SQL 语句?
then DROP TABLES, is there a neat way to combine this all into one SQL Statement?
推荐答案
您可以在存储过程中使用动态 SQL 来执行此操作.它看起来像这样(未经测试):
You could use dynamic SQL to do it, inside a stored procedure. It'd look something like this (untested):
CREATE PROCEDURE drop_like (IN pattern VARCHAR(64))
BEGIN
DECLARE q tinytext;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT CONCAT('DROP TABLE "', table_schema, '"."', table_name, '"')
FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name LIKE pattern;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
drop_loop: LOOP
FETCH cur INTO q;
IF done THEN
LEAVE drop_loop;
END IF;
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END;
这篇关于mySQL 仅使用 SQL 语句删除带有通配符的表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文