批量删除某时段以前的表
2012-09-04 17:01:46 来源:WEB开发网这里以2012年9月份为例
LOG表有很多,如
cin_t1_201201....cin_t1_201209
cin_t2_201201....cin_t2_201209
……
cin_t10_201201....cin_t10_201209
存储过程如下:
DELIMITER $$
DROP PROCEDURE IF EXISTS `drop201209_before`$$
CREATE PROCEDURE `drop201209_before`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE l_t2012 VARCHAR(100);
DECLARE drop201209_before_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA='report' AND TABLE_NAME
REGEXP 'cin_.*20120[1-8]';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN drop201209_before_cursor;
drop_loop: LOOP
FETCH drop201209_before_cursor INTO l_t2012;
IF done=1 THEN
LEAVE drop_loop;
END IF;
SET @v_droptablesql=CONCAT('DROP TABLE ',l_t2012);
PREPARE droptable FROM @v_droptablesql;
EXECUTE droptable;
END LOOP drop_loop;
CLOSE drop201209_before_cursor;
END$$
DELIMITER ;
赞助商链接