批量修改表引擎

由于今天要导入好多表，而且要批量转换为NDB引擎，备份文件的太大。打开修改不现实。所以就写了一个SP。 里面涉及到三个存储过程。

1、存储过程详细语句：

这个存储过程有点问题，要反复运行几次才能把一个有好多数据的库全部更新。
具体原因还没有查清楚，暂时不能用到生产环境。
DELIMITER $$

CREATE PROCEDURE `sp_alter_engine`(
IN f_db_name varchar(255),IN f_table_name varchar(255),
IN f_engine_name varchar(255))
BEGIN
-- Get the total of the table with given database.

declare cnt1 int default 0;
-- Increment variable.

declare i int default 0;
-- The true statement.

select count(1) from information_schema.tables where table_schema = f_db_name and `engine` is not null and `engine` != f_engine_name into cnt1;
-- To determinate whether the given table's name is empty or not.

-- Begin if.

if char_length(f_table_name) = 0 then
-- Begin while.

while i < cnt1
do
set @stmt = concat('select table_name from information_schema.tables where table_schema=''',f_db_name,''' and `engine` is not null and `engine` != ''',f_engine_name,''' limit ',i,',1 into @tbname');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = concat('alter table ',@tbname,' engine ',f_engine_name);
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
set i = i + 1;
end while;
-- End while.

else
-- Change specific table's engine.

set @stmt = concat('alter table ',f_db_name,'.',f_table_name,' engine ',f_engine_name);
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
end if;
-- End if;

END$$

DELIMITER ;

2、下面这两个结合可以用到生产环境。
1）、修改整个库，调用第三个SP。
DELIMITER $$

DROP PROCEDURE IF EXISTS `t_girl`.`sp_alter_db_engine`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alter_db_engine`(
IN f_db_name varchar(255), IN f_engine_name varchar(255))
BEGIN
-- Get the total number of tables.

declare cnt1 int default 0;
declare i int;
set i = 0;
select count(1) from information_schema.tables where table_schema = f_db_name into cnt1;
while i < cnt1
do
set @stmt = concat('select @tbname:=table_name from information_schema.tables where table_schema=''',f_db_name,''' order by table_name desc limit ',i,',1 into @tbname');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
set @tbname = concat(f_db_name,'.',@tbname);
call sp_alter_table_engine(@tbname,f_engine_name);
set i = i + 1;
end while;
END$$

DELIMITER ;
2）、修改单个表
DELIMITER $$

DROP PROCEDURE IF EXISTS `t_girl`.`sp_alter_table_engine`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_alter_table_engine`(
IN f_tb_name varchar(255),IN f_engine_name varchar(20))
BEGIN
set @stmt = concat('alter table ',f_tb_name,' engine=',f_engine_name);
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
END$$

DELIMITER ;

3、测试结果：
mysql> call sp_alter_db_engine('t_girl','innodb');
Query OK, 0 rows affected (2 min 51.09 sec)