/ 中存储网

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

2016-09-08 14:40:59 来源:中存储网

最近研究了一下存储过程的高级特性:游标与流程控制。以一个简单的例子来说明,例子要达到的目的是这样的:公司因为创建数据库之初,将Mysql的存储引擎设定为了MYISAM,这种引擎不支持主子表关联,无法创建主外键,而且数据库涉及到的表很多,手动更改很麻烦,需要借助一个存储过程来批量修改。即:批量修改数据库表的引擎类型。
这里首先创建一个测试的数据库:testaaa,其包含如下表 and 初始数据库引擎类型如下:

  1. SELECT table_name,ENGINE FROM information_schema.TABLES WHERE table_schema='testaaa' ;   
  2.  




需要把engine列变成InnoDB,创建脚本如下:

  1. DELIMITER &&  
  2. CREATE PROCEDURE changeEngineType(IN DB_NAME VARCHAR(32),IN Engine_type VARCHAR(16))   
      # 数据库名称,引擎类型  
  3. BEGIN  
  4.   DECLARE done INT DEFAULT 0;  # 游标的标志位  
  5.   DECLARE TB_NAME VARCHAR(64); # 数据库的表名  
  6.   DECLARE COMMAND VARCHAR(64); # 更改数据库引擎的命令  
  7.   DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME ;  
  8.   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; #错误定义,标记循环结束   
  9.   OPEN cur1;  
  10.   REPEAT # 循环开始  
  11.     FETCH cur1 INTO TB_NAME;  
  12.     IF NOT done THEN        
  13.        SET COMMAND=CONCAT('ALTER TABLE ',DB_NAME,'.',TB_NAME,' ENGINE = ',Engine_type); 
            # 拼更改引擎的命令 ALTER TABLE test_3 ENGINE = MYISAM ;  
  14.        SET @E=COMMAND;   
  15.        PREPARE stmt1 FROM @E;   
  16.           EXECUTE stmt1;              # 执行命令             
  17.           DEALLOCATE PREPARE stmt1;   # 释放对象 deallocate prepare  
  18.     END IF;  
  19.   UNTIL done END REPEAT;  # 循环结束  
  20.   CLOSE cur1; #关闭游标  
  21. END;  
  22. &&  
  23. DELIMITER ;   


执行完成后,调用该存储过程:

  1.   
  2. CALL changeEngineType('testaaa','MYISAM');  
  3.   
  4. CALL changeEngineType('testaaa','InnoDB');  
  5.   




从上表可以看出,每个数据表的类型都已经改变。