bitsCN.com
mysql中对数据库的每个表执行优化的存储过程
对数据库的每个表执行优化的存储过程
CREATE PROCEDURE `inventory`.`optimize_table` (db_name VARCHAR(64))BEGIN DECLARE t VARCHAR(64); DECLARE done INT DEFAULT 0; DECLARE c CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=db_name AND TABLE_TYPE='BASE TABLE'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN c; tables_loop:LOOP FETCH c INTO t; IF done THEN CLOSE c; LEAVE tables_loop; END IF; SET @stmt_text:=CONCAT("OPTIMIZE TABLE ",db_name,'.',t); PREPARE stmt FROM @stmt_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE c;END语句2：CREATE PROCEDURE `inventory`.`optimize_tables2` (db_name VARCHAR(64))BEGIN DECLARE t VARCHAR(64); DECLARE done INT DEFAULT 0; DECLARE c CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=db_name AND TABLE_TYPE='BASE TABLE'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN c; REPEAT FETCH c INTO t; IF NOT done THEN SET @stmt_text:=CONCAT("OPTIMIZE TABLE ",db_name,'.',t); PREPARE stmt FROM @stmt_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; UNTIL done END REPEAT; CLOSE c;END调用时为call optimize_tables2('库名');或者call optimize_tables('库名');
