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

说明：此处为《高性能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中对数据库的每个表执行优化的存储过程

最新文章

热门文章