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

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

WBOY

Libérer： 2016-06-01 13:28:36

original

1032 Les gens l'ont consulté

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=&#39;BASE TABLE&#39;; DECLARE CONTINUE HANDLER FOR SQLSTATE &#39;02000&#39; 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,&#39;.&#39;,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=&#39;BASE TABLE&#39;; DECLARE CONTINUE HANDLER FOR SQLSTATE &#39;02000&#39; SET done=1; OPEN c; REPEAT  FETCH c INTO t;  IF NOT done THEN   SET @stmt_text:=CONCAT("OPTIMIZE TABLE ",db_name,&#39;.&#39;,t);   PREPARE stmt FROM @stmt_text;   EXECUTE stmt;   DEALLOCATE PREPARE stmt;  END IF; UNTIL done END REPEAT; CLOSE c;END调用时为call optimize_tables2(&#39;库名&#39;);或者call optimize_tables(&#39;库名&#39;);

Copier après la connexion

bitsCN.com