批量修改MySQL的表引擎

柔情只为你懂 2022-08-24 04:22 171阅读 0赞

批量修改MySQL的表引擎

由于今天要导入好多表，而且要批量转换为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 ;``  
  
3、测试结果：  
mysql> call sp\_alter\_db\_engine('t\_girl','innodb');  
Query OK, 0 rows affected (2 min 51.09 sec)

From：[http://blog.chinaunix.net/u/29134/showart\_454996.html][http_blog.chinaunix.net_u_29134_showart_454996.html]

[http_blog.chinaunix.net_u_29134_showart_454996.html]: http://blog.chinaunix.net/u/29134/showart_454996.html