一、概述
本章節介紹使用游標來批量進行表操作,包括批量添加索引、批量添加字段等。如果對存儲過程、變量定義、預處理還不是很熟悉先閱讀我前面寫過的關於這三個概念的文章,只有先了解了這三個概念才能更好的理解這篇文章。
理解MySQL變量和條件:http://www.jb51.net/article/81375.htm
理解Mysql prepare預處理語句:http://www.jb51.net/article/81378.htm
理解MySQL存儲過程和函數:http://www.jb51.net/article/81381.htm
二、正文
1、聲明光標
DECLARE cursor_name CURSOR FOR select_statement
這個語句聲明一個光標。也可以在子程序中定義多個光標,但是一個塊中的每一個光標必須有唯一的名字。
注意:SELECT語句不能有INTO子句。
2、打開光標
DECLARE cursor_name CURSOR FOR select_statement
這個語句打開先前聲明的光標。
3、前進光標
FETCH cursor_name INTO var_name [, var_name] ...
這個語句用指定的打開光標讀取下一行(如果有下一行的話),並且前進光標指針。
4、關閉光標
CLOSE cursor_name
這個語句關閉先前打開的光標。
5、批量添加索引
共享一個批量添加索引的游標,當一個庫中有上百張表結構一樣但是名稱不一樣的表,這個時候批量操作就變得簡單了。
#刪除創建存儲過程 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();
這裡有幾個細節:
注意:由於mysql在存儲過程當中無法將查詢出來的變量名直接作為表名來用,所以這裡要用到動態拼接SQL的方法,但是通常的SET CONCAT的方法並不管用,所以這裡就使用了PREPARE來進行預編譯。
總結
批量處理雖然有時候能提高工作的效率,但是帶來的潛在危險也是挺大了,所以在執行之前必須要非常有把握你執行的語句對數據的影響,否則在生成環境就非常危險了。