MySQL 遊標（PREPARE預處理語句）

pursuer.chen發表於2016-03-22
MySql

概述

本章節介紹使用遊標來批量進行表操作，包括批量新增索引、批量新增欄位等。如果對儲存過程、變數定義、預處理還不是很熟悉先閱讀我前面寫過的關於這三個概念的文章，只有先了解了這三個概念才能更好的理解這篇文章。

 

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

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結。

《歡迎交流討論》

 

相關文章