mysql中对数据库的每个表执行优化的存储过程_MySQL

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('库名');

 

bitsCN.com