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('库名');
查看更多关于mysql中对数据库的每个表执行优化的存储过程 -的详细内容...