MySQL 游标(PREPARE预处理语句)

概述

本章节介绍使用游标来批量进行表操作,包括批量添加索引、批量添加字段等。如果对存储过程、变量定义、预处理还不是很熟悉先阅读我前面写过的关于这三个概念的文章,只有先了解了这三个概念才能更好的理解这篇文章。

 

MySQL变量和条件:http://www.cnblogs.com/chenmh/p/5203429.html

PREPARE 预处理语句:http://www.cnblogs.com/chenmh/articles/5308085.html

MySQL存储过程和函数:http://www.cnblogs.com/chenmh/p/5201473.html

正文

光标声明

声明光标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。

注意:SELECT语句不能有INTO子句。

打开光标

OPEN cursor_name

这个语句打开先前声明的光标。

前进光标

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开光标读取下一行(如果有下一行的话),并且前进光标指针。

关闭光标

CLOSE cursor_name

这个语句关闭先前打开的光标。 

 

批量添加索引

共享一个批量添加索引的游标,当一个库中有上百张表结构一样但是名称不一样的表,这个时候批量操作就变得简单了。

#删除创建存储过程
DROP
PROCEDURE IF EXISTS FountTable; DELIMITER $$ CREATE PROCEDURE FountTable() BEGIN DECLARE TableName varchar(64);
#声明游标
DECLARE cur_FountTable CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='front' AND TABLE_NAME LIKE 'student%'; DECLARE EXIT HANDLER FOR not found CLOSE cur_FountTable; #打开游标 OPEN cur_FountTable; REPEAT FETCH cur_FountTable INTO TableName; #定义预处理 SET @SQLSTR1 = CONCAT('create index Flag on ','`',TableName,'`',' (Flag); '); SET @SQLSTR2 = CONCAT('create index State on ','`',TableName,'`',' (State); '); SET @SQLSTR3 = CONCAT('create index upload on ','`',TableName,'`',' (upload); '); SET @SQLSTR4 = CONCAT('create index ccFlag on ','`',TableName,'`',' (lockFlag); '); SET @SQLSTR5 = CONCAT('create index comes on ','`',TableName,'`',' (comes); '); ###SET @SQLSTR=CONCAT(@SQLSTR1,@SQLSTR2,@SQLSTR3,@SQLSTR4,@SQLSTR5 ); PREPARE STMT1 FROM @SQLSTR1; PREPARE STMT2 FROM @SQLSTR2; PREPARE STMT3 FROM @SQLSTR3; PREPARE STMT4 FROM @SQLSTR4; PREPARE STMT5 FROM @SQLSTR5; EXECUTE STMT1; EXECUTE STMT2; EXECUTE STMT3; EXECUTE STMT4; EXECUTE STMT5; DEALLOCATE PREPARE STMT1; DEALLOCATE PREPARE STMT2; DEALLOCATE PREPARE STMT3; DEALLOCATE PREPARE STMT4; DEALLOCATE PREPARE STMT5; # SELECT @SQLSTR; UNTIL 0 END REPEAT; #关闭游标 CLOSE cur_FountTable; END $$ DELIMITER ; CALL FountTable();

这里有几个细节:

  • 在声明游标的时候记得修改自己需要查询的条件
  • 在预处理这里也需要改成对应的字段
  • 在定义条件变量的时候这里我使用的是EXIT就是遇到错误就中断,当然也可以使用CONTINUE 。

游标嵌套授权 

DROP PROCEDURE IF EXISTS pro_grant_pri;
DELIMITER $$
CREATE PROCEDURE pro_grant_pri()
BEGIN
    DECLARE cur1_username varchar(128);
    DECLARE cur2_dbname varchar(128);
    DECLARE done int;
    
    DECLARE cur1 CURSOR FOR SELECT username FROM testuser;
    DECLARE cur2 CURSOR FOR SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME like 'test%';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur1;
    out_loop:LOOP
        FETCH cur1 INTO cur1_username;
        IF done = 1 THEN
            LEAVE out_loop;
        END IF;
        
        OPEN cur2;
        inner_loop:LOOP
        FETCH cur2 INTO cur2_dbname;
        IF done = 1 THEN
            LEAVE inner_loop;
        END IF;
        
             SET @SQLSTR1 = CONCAT('grant select,insert,delete,update on ',cur2_dbname,'.*',' to ','''',cur1_username,'''','@','''%'';') ;
             PREPARE STMT1 FROM @SQLSTR1; 
             EXECUTE STMT1; 
             DEALLOCATE PREPARE STMT1;
        
        end LOOP inner_loop;
        CLOSE cur2;
        SET done=0;
    END LOOP out_loop;
  CLOSE cur1;

END $$
DELIMITER ;

 

注意:

1.由于mysql在存储过程当中无法将查询出来的变量名直接作为表名来用,所以这里要用到动态拼接SQL的方法,但是通常的SET CONCAT的方法并不管用,所以这里就使用了PREPARE来进行预编译。

2.在定义游标之前所以的定义都只能通过decalre定义,不能使用SET定义!!!!!!

3.定义的变量名不能和表返回的字段名相同,否则接收的值为空。

总结

 批量处理虽然有时候能提高工作的效率,但是带来的潜在危险也是挺大了,所以在执行之前必须要非常有把握你执行的语句对数据的影响,否则在生成环境就非常危险了。

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

 

posted @ 2016-03-22 19:38  pursuer.chen  阅读(5353)  评论(0编辑  收藏  举报