批量修改表引擎-白红宇

批量修改表引擎

阅读量：6898 次

发布时间：2019-06-27

本文共 2758 字，大约阅读时间需要 9 分钟。

由于今天要导入好多表，而且要批量转换为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 = '';
      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 = '';
  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 = '';
      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 = '';
END$$
DELIMITER ;