欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

批量修改表引擎 F#MySQL 

程序员文章站 2022-07-12 10:52:37
...

由于今天要导入好多表,而且要批量转换为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)
相关标签: F# MySQL