版本号是: | Variable_name | Value | +---------------+---------------------+ | version | 5.1.22-rc-community | +---------------+---------------------+
SELECT CONCAT("DROP TABLE ", TABLE_NAME, ";") FROM information_schema.TABLES WHERE TABLE_SCHEMA = "MyDatabase" AND TABLE_NAME LIKE "perf%";然后用cursor一个一个删除。
DELIMITER $$ drop procedure if exists zz.rrsp$$ CREATE PROCEDURE `zz`.`rrsp`()
BEGIN DECLARE done INT DEFAULT 0; declare vtable_name varchar(255); DECLARE cur1 CURSOR FOR SELECT CONCAT("DROP TABLE test.", TABLE_NAME, ";") FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test" AND TABLE_NAME LIKE "qq%"; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; open cur1; fetch cur1 into vtable_name; while done=0 do set @sTempSQL = vtable_name; PREPARE stmt1 FROM @sTempSQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; fetch cur1 into vtable_name; end while; close cur1;
| Variable_name | Value |
+---------------+---------------------+
| version | 5.1.22-rc-community |
+---------------+---------------------+
drop procedure if exists zz.rrsp$$
CREATE PROCEDURE `zz`.`rrsp`()
BEGIN
DECLARE done INT DEFAULT 0;
declare vtable_name varchar(255);
DECLARE cur1 CURSOR FOR SELECT CONCAT("DROP TABLE test.", TABLE_NAME, ";") FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test" AND TABLE_NAME LIKE "qq%";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur1;
fetch cur1 into vtable_name;
while done=0 do
set @sTempSQL = vtable_name;
PREPARE stmt1 FROM @sTempSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
fetch cur1 into vtable_name;
end while;
close cur1;
END$$DELIMITER ;