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