最近研究了一下存储过程的高级特性:游标与流程控制。以一个简单的例子来说明,例子要达到的目的是这样的:公司因为创建数据库之初,将Mysql的存储引擎设定为了MYISAM,这种引擎不支持主子表关联,无法创建主外键,而且数据库涉及到的表很多,手动更改很麻烦,需要借助一个存储过程来批量修改。即:批量修改数据库表的引擎类型。
这里首先创建一个测试的数据库:testaaa,其包含如下表 and 初始数据库引擎类型如下:
- SELECT table_name,ENGINE FROM information_schema.TABLES WHERE table_schema='testaaa' ;
需要把engine列变成InnoDB,创建脚本如下:
- DELIMITER &&
- CREATE PROCEDURE changeEngineType(IN DB_NAME VARCHAR(32),IN Engine_type VARCHAR(16))
# 数据库名称,引擎类型 - BEGIN
- DECLARE done INT DEFAULT 0; # 游标的标志位
- DECLARE TB_NAME VARCHAR(64); # 数据库的表名
- DECLARE COMMAND VARCHAR(64); # 更改数据库引擎的命令
- DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME ;
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; #错误定义,标记循环结束
- OPEN cur1;
- REPEAT # 循环开始
- FETCH cur1 INTO TB_NAME;
- IF NOT done THEN
- SET COMMAND=CONCAT('ALTER TABLE ',DB_NAME,'.',TB_NAME,' ENGINE = ',Engine_type);
# 拼更改引擎的命令 ALTER TABLE test_3 ENGINE = MYISAM ; - SET @E=COMMAND;
- PREPARE stmt1 FROM @E;
- EXECUTE stmt1; # 执行命令
- DEALLOCATE PREPARE stmt1; # 释放对象 deallocate prepare
- END IF;
- UNTIL done END REPEAT; # 循环结束
- CLOSE cur1; #关闭游标
- END;
- &&
- DELIMITER ;
执行完成后,调用该存储过程:
- CALL changeEngineType('testaaa','MYISAM');
- CALL changeEngineType('testaaa','InnoDB');
从上表可以看出,每个数据表的类型都已经改变。