批量修改表引擎

 

由于今天要导入好多表，而且要批量转换为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)