MySQL使用游標(biāo)批量處理進(jìn)行表操作

更新時(shí)間：2016年03月23日 14:47:39 作者：pursuer.chen

這篇文章主要介紹了MySQL使用游標(biāo)批量進(jìn)行表操作，包括批量添加索引、批量添加字段等，感興趣的小伙伴們可以參考一下

一、概述

本章節(jié)介紹使用游標(biāo)來(lái)批量進(jìn)行表操作，包括批量添加索引、批量添加字段等。如果對(duì)存儲(chǔ)過(guò)程、變量定義、預(yù)處理還不是很熟悉先閱讀我前面寫過(guò)的關(guān)于這三個(gè)概念的文章，只有先了解了這三個(gè)概念才能更好的理解這篇文章。

理解MySQL變量和條件：http://www.dhdzp.com/article/81375.htm

理解Mysql prepare預(yù)處理語(yǔ)句：http://www.dhdzp.com/article/81378.htm

理解MySQL存儲(chǔ)過(guò)程和函數(shù)：http://www.dhdzp.com/article/81381.htm

二、正文

1、聲明光標(biāo)

DECLARE cursor_name CURSOR FOR select_statement

這個(gè)語(yǔ)句聲明一個(gè)光標(biāo)。也可以在子程序中定義多個(gè)光標(biāo)，但是一個(gè)塊中的每一個(gè)光標(biāo)必須有唯一的名字。

注意：SELECT語(yǔ)句不能有INTO子句。

2、打開(kāi)光標(biāo)

DECLARE cursor_name CURSOR FOR select_statement

這個(gè)語(yǔ)句打開(kāi)先前聲明的光標(biāo)。

3、前進(jìn)光標(biāo)

FETCH cursor_name INTO var_name [, var_name] ...

這個(gè)語(yǔ)句用指定的打開(kāi)光標(biāo)讀取下一行（如果有下一行的話），并且前進(jìn)光標(biāo)指針。

4、關(guān)閉光標(biāo)

CLOSE cursor_name

這個(gè)語(yǔ)句關(guān)閉先前打開(kāi)的光標(biāo)。

5、批量添加索引

共享一個(gè)批量添加索引的游標(biāo)，當(dāng)一個(gè)庫(kù)中有上百?gòu)埍斫Y(jié)構(gòu)一樣但是名稱不一樣的表，這個(gè)時(shí)候批量操作就變得簡(jiǎn)單了。

#刪除創(chuàng)建存儲(chǔ)過(guò)程
DROP PROCEDURE IF EXISTS FountTable;
DELIMITER $$
CREATE PROCEDURE FountTable()
BEGIN
  DECLARE TableName varchar(64); 
  #聲明游標(biāo)
  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;
  #打開(kāi)游標(biāo)
  OPEN cur_FountTable;
  REPEAT
     FETCH cur_FountTable INTO TableName;
     #定義預(yù)處理
     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;
  #關(guān)閉游標(biāo)
  CLOSE cur_FountTable;

END $$
DELIMITER ;


CALL FountTable();

這里有幾個(gè)細(xì)節(jié)：

在聲明游標(biāo)的時(shí)候記得修改自己需要查詢的條件
在預(yù)處理這里也需要改成對(duì)應(yīng)的字段
在定義條件變量的時(shí)候這里我使用的是EXIT就是遇到錯(cuò)誤就中斷，當(dāng)然也可以使用CONTINUE 。

注意：由于mysql在存儲(chǔ)過(guò)程當(dāng)中無(wú)法將查詢出來(lái)的變量名直接作為表名來(lái)用,所以這里要用到動(dòng)態(tài)拼接SQL的方法，但是通常的SET CONCAT的方法并不管用，所以這里就使用了PREPARE來(lái)進(jìn)行預(yù)編譯。

總結(jié)

批量處理雖然有時(shí)候能提高工作的效率，但是帶來(lái)的潛在危險(xiǎn)也是挺大了，所以在執(zhí)行之前必須要非常有把握你執(zhí)行的語(yǔ)句對(duì)數(shù)據(jù)的影響，否則在生成環(huán)境就非常危險(xiǎn)了。

您可能感興趣的文章: