需求删除以*2011*日志表。
| DELIMITER $$ USE test$$ DROP PROCEDURE IF EXISTS drop2011$$ CREATE PROCEDURE drop2011() BEGIN DECLARE done INT DEFAULT 0; DECLARE l_t2011 VARCHAR(30); DECLARE drop_2011_cursor CURSOR FOR select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME like '%2011%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN drop_2011_cursor; drop_loop: LOOP FETCH drop_2011_cursor INTO l_t2011; IF done=1 THEN LEAVE drop_loop; END IF; SET @v_droptablesql=CONCAT('DROP TABLE ',l_t2011); PREPARE droptable FROM @v_droptablesql; EXECUTE droptable; END LOOP drop_loop; CLOSE drop_2011_cursor; END$$ DELIMITER ; |
CALL drop2011();
-- 执行即可。
本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/811222
