Mysql 高级特性：游标与流程控制

11年前

Mysql 高级特性：游标与流程控制

最近研究了一下存储过程的高级特性：游标与流程控制。以一个简单的例子来说明，例子要达到的目的是这样的：公司因为创建数据库之初，将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 ;