MySQL 数据库清理MyISAM Innodb表(支持MySQL5.1.6以上的版本)

数据中心有大量的 MyISAM log表需要定时清理,定为每周末,10G左右,5-10分钟完成.

event 调用这里就不写了,直接上存储过程....

DROP PROCEDURE IF EXISTS proc_optable;tudou@gyyx2012-02-22CREATE PROCEDURE proc_optable()BEGINDECLARE dono INT DEFAULT 0;DECLARE o VARCHAR(2000);DECLARE optable CURSOR FOR SELECT CONCAT('OPTIMIZE LOCAL TABLE `',TABLE_SCHEMA,'`.`',TABLE_NAME,'`;') from information_schema.`TABLES` WHERE TABLE_TYPE='BASE TABLE' AND ENGINE IN ('MyISAM','InnoDB','BDB');DECLARE CONTINUE HANDLER FOR NOT FOUND SET dono=1;SET dono=0;OPEN optable;REPEATFETCH optable INTO o;set @o=o;PREPARE MSQL FROM @o;EXECUTE MSQL;UNTIL dono END REPEAT;CLOSE optable;END