Optimize the stored procedure for each table in the database in mysql _ MySQL

The stored procedure bitsCN.com optimized for each table in the database in mysql

Mysql optimizes the storage process for each table in the database.

Optimize the storage process for each table in the database

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 '100' 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 Statement 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 '2013' 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; call optimize_tables2 ('database name') when calling END '); or call optimize_tables ('database name ');

BitsCN.com