概述
本章节介绍使用游标来批量进行表操作，包括批量添加索引、批量添加字段等。如果对存储过程、变量定义、预处理还不是很熟悉先阅读我前面写过的关于这三个概念的文章，只有先了解了这三个概念才能更好的理解这篇文章。
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 本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接。 《欢迎交流讨论》 |