Mysql 遊标初識

MySql 遊标初識

認識

遊标(cursor), 按字面意思可了解為, 遊動的辨別, 或者叫做"光标", 這樣更容易了解. 就好比現有一張表存儲了n行記錄, 然後我想每次取出一行, 進行為所欲為, 這時候就用到了遊标cursor, 資料的搬運工, 搬運完目前資料(遊标指向目前), 然後又移動到下一條資料的位置.

"移動", 和 "指向" 這兩個詞很重要, 跟C的指針有點類似, 舉一個連結清單的例子吧. a -> b -> c -> d, 需求是求計算連結清單的長度. 則我們需要定義一個遊标變量cursor, 預設定位到"a" 節點位置, 還需定義一個技術變量count 預設為0, 然後移動遊标cursor (Python裡 "=" 即表示"指向"), 沿着 a->b->c->d, 每移動一次, 則遊标指向目前節點, 計數器加1....

應用場景, 在于, 好比一條 select xxx where xxx ; 傳回的是一個查詢集, 但我不想啪啪啪一頓傳回一堆資料, 我要自己逐行檢查和寫邏輯判斷, 這時候就需要cursor. 即, 一條sql, 對應N條資料, 取出(依次 or 自定義順序) 資料的接口(interface) / 句柄, 就是遊标, 沿着遊标方向, 依次可以一次取出1行.

接口(interface)

接口泛指實體把自己提供給外界的一種抽象化物（可以為另一實體），用以由内部操作分離出外部溝通方法，使其能被内部修改而不影響外界其他實體與其互動的方式。

通俗了解, 就是, A隻想用B的一些功能,但并不關心B是怎麼實作的, 由此B根據A的需求, 提供一些能滿足A的服務, 這些服務, 就是"接口".舉幾個栗子.

我想開車, 車裡面是什麼樣的我并不關系, 這時候車給了提供了, 方向盤, 油門,離合器, 刹車, 擋位等, 我就是能開了, 這些就是"接口" (好像不太恰當哦); 或者是想學外語, 這時候給我提給了一張V國際電話預付卡, 那我就可以學外語了, 這張V國際電話預付卡, 就是接口.

我編寫了一個web網站, 想讓使用者用微信, QQ, 微網誌, 支付寶賬号也能登陸, 那這時候,我就要向這些大佬公司申請這些使用者ID驗證, 怎麼驗證我不管, 隻按照他們提供的 "驗證規則"傳參進去, 等待就好, 那, 這個驗證規則邏輯, 就是微信, 微網誌...向外界提供的接口.

我想要操作電腦, 這時候, windows / linux 給我提給了圖形化 / 指令行的方式讓我操作, 這也是接口.

建立一張MySql二維表存儲資料, 我可以對其進行增删改查, 那這些功能, 也是接口.

編碼時, 調用自定義的或别人的或系統的類的方法時, 這些方法也是接口.

文法

聲明遊标: declare 遊标名 cursor for select _staetment;
打開遊标: open 遊标名
取出資料: fetch 遊标名 into var1, var2 ....
關閉遊标: close 遊标名

-- 文檔說明
-- 遊标聲明必須在procedure 資料處理之前, 和在變量聲明之後.
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;  -- 打開遊标(目前塊,唯一命名)
FETCH cursor_name INTO var_name, [, var_name] ... -- 讀取遊标資料, 并前進指針
CLOSE cursor_name;  -- 如果不close, 則會在其被聲明的複合語句末尾被關閉

案例

用之前的goods, 表操作一波.

-- 檢視一下資料
-- out
mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  |  37 |
|   2 | dog  |  72 |
|   3 | pig  |  18 |
+-----+------+-----+
3 rows in set (0.09 sec)

-- 更新一波 cat 的數量吧
mysql> update goods set num = 100 where gid=1;

Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  | 100 |
|   2 | dog  |  72 |
|   3 | pig  |  18 |
+-----+------+-----+
3 rows in set (0.11 sec)

需求是要逐條取出每行資料, 而不是一下子都給我.

-- cursor: 依次擷取每行資料
drop procedure if exists cur1;
delimiter //
create procedure cur1()
begin
  -- select * from goods; 每行有3個值, gid, name, num 
  -- 首先要定義變量來存儲
  -- 聲明和打開遊标
  -- fetch 每行資料
  -- 處理邏輯
  -- 關閉遊标
end //
delimiter ;

具體實作

drop procedure if exists cur1;
delimiter //
create procedure cur1()
begin
  -- select * from goods; 每行有3個值, gid, name, num 
  -- 首先就為每行資料, 定義相應臨時變量來存儲
  declare tmp_gid int;
  declare tmp_name varchar(20);
  declare tmp_num int;
  -- 聲明和打開遊标
  declare getGoods cursor for select gid, name, num from goods;
  open getGoods;
  -- fetch 每行資料, 要一一有對應的變量來接收哦
  fetch getGoods into tmp_gid, tmp_name, tmp_num;
  -- 處理邏輯(這裡隻列印一下)
  select tmp_gid, tmp_name, tmp_num;
  -- 關閉遊标
  close getGoods;
  
end //
delimiter ;

-- out
mysql> call cur1();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.14 sec)

與之前直接取出一行的差別在于, 控制權在我們手裡, 真的可以為所欲為, 進一步可以進行判斷,取值等各種程式設計操作, 真的可以為所欲為.

如何fetch多行呢?

-- fetch 多行 及 遊标到尾(沒有資料了, 不會報錯)
drop procedure if exists cur1;
delimiter //
create procedure cur1()
begin
    declare tmp_gid int;
    declare tmp_name varchar(20);
    declare tmp_num int;
    
    declare getGoods cursor for select gid, name, num from goods;
    open getGoods;
    -- fetch and into val1, var2.....
    -- fetch 多行
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    select tmp_gid, tmp_name, tmp_num;
    
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    select tmp_gid, tmp_name, tmp_num;
    
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    select tmp_gid, tmp_name, tmp_num;
    
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    select tmp_gid, tmp_name, tmp_num;
    -- cursor 即便到尾了(沒有data, 也不會報錯哦)
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    select tmp_gid, tmp_name, tmp_num;
    -- close
    close getGoods;
end //
delimiter ;

-- out

mysql> call cur1();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.20 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.37 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.54 sec)

mysql> select * from goods;
+-----+------+-----+
| gid | name | num |
+-----+------+-----+
|   1 | cat  | 100 |
|   2 | dog  |  72 |
|   3 | pig  |  18 |
+-----+------+-----+
3 rows in set (0.18 sec)

可以看出, 遊标的特點是, 每fetch一次, 就往後遊動一次, 即稱為遊标嘛.

現在, 要采用循環與遊标相配合取出每條資料. 思路可以是先查詢到表的行數rows_num;作為循環的退出條件, 然後循環fetch即可.(while, repeat都行).

-- 通過cursor, 循環取出每行資料
drop procedure if exists curRepeat;
delimiter //
create procedure curRepeat()
begin
  declare getGoods cursor for select gid, name, num from goods;
  open getGoods;
  -- 循環fetch
  repeat
    -- 這裡需要一大波的定義變量哦.
    fetch getGoods into xxx, xxx, ...;
    select xxx, xxx ...;
    until i > rows_num
  end repeat;
end //
delimiter ;

詳細repeat 實作

-- 通過cursor, 循環取出每行資料
drop procedure if exists curRepeat;
delimiter //
create procedure curRepeat()
begin
    declare tmp_gid int;
    declare tmp_name varchar(20);
    declare tmp_num int;
    
    declare i int default 0;  -- 自增變量
    declare rows_num int default 0;  -- 存儲查詢集的行數(循環的退出條件)
    
    declare getGoods cursor for select gid, name, num from goods;
    
    -- 擷取查詢集的行數 rows_num, 注意順序, 操作要放在 declare 之後哦
    -- 錯誤文法: set rows_num := select count(*) from goods;
    select count(*) into rows_num from goods;
    open getGoods;
    -- 循環fetch
    repeat
        fetch getGoods into tmp_gid, tmp_name, tmp_num;
        -- 業務邏輯處理(這裡隻是簡單列印), 遊标的作用就在于此, 這裡可以為所欲為.
        select tmp_gid, tmp_name, tmp_num;
        
        set i := i + 1;
        until i > rows_num
    end repeat;
    -- 别忘了close
    close getGoods;
end //
delimiter ;

-- out
call curRepeat();
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.12 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.26 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.41 sec)

思路二: 遊标取值越界時, 利用越界辨別判斷, 用到DECLARE ... HANDLER... 處理程式

SQLWARNING 是對所有以01開頭的SQLSTATE代碼的标記
NOT FOUND 是對02開頭的SQLSTATE代碼标記
SQLEXCEPTION 是對除了01, 02外的代碼标記

DECLARE handler_type HANDLER FOR condtion_value ... sp_statement.

-- cursor 越界辨別來退出循環
drop procedure if exists curBorder;
delimiter //
create procedure curBorder()
begin
  declare tmp_gid int;
  declare tmp_name varchar(20);
  declare tmp_num int;
  -- 遊标周遊資料結束的标志
  declare done boolean default false;
  declare getGoods cursor for select gid, name, num from goods;
  -- 退出的 handler 标記, not found 時執行
  -- declare continue handler for NOT FOUND set done := True;
  -- 解決continue 多取一行的問題, 用 EXit 即可
  declare EXIT handler for NOT FOUND set done := True;
  
  open getGoods;
  -- 循環取每行值
  repeat
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    -- 業務邏輯處理
    select tmp_gid, tmp_name, tmp_num;
    until done=True
  end repeat;
  -- 總是忘了最後關閉遊标呀
  close getGoods;
  
end //
delimiter ;

-- out

mysql> call curBorder();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.12 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.27 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.38 sec)

Query OK, 0 rows affected (0.00 sec)

小結 handler 類型

continue handler ...not found ... 是觸發後, 後面的語句繼續執行
exit handler ...not found ... 是出發後, 後面的語句不執行
undo handler ... 前面的語句撤銷...

從邏輯上, 就用continue handler 來取出資料

-- 堅持用contine取出所有行,并考慮特殊情況
drop procedure if exists curContinue;
delimiter //
create procedure curContinue()
begin
  declare tmp_gid int;
  declare tmp_name varchar(20);
  declare tmp_num int;
  
  declare done boolean default False;
  declare getGoods cursor for select gid, name, num from goods;
  declare continue handler for not found set done := True;
  
  open getGoods;
  -- 先fetch 一行出來
  fetch getGoods into tmp_gid, tmp_name, tmp_num;
  
  repeat
    -- 先取出一條來出來(不論是0,1或多), 再繼續 fetch 
    select tmp_gid, tmp_name, tmp_num;
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
    until done = True
  end repeat;
  -- colse
  close getGoods;
end //
delimiter ;

-- out

mysql> call curContinue();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.14 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.28 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.39 sec)

Query OK, 0 rows affected (0.00 sec)

同樣, 換成while循環也是一樣的.

drop procedure if exists cur_while;
delimiter //
create procedure cur_while()
begin
  -- 聲明臨時變量來存儲fetch每行值和一狀态變量
  declare tmp_gid int;
  declare tmp_name varchar(20);
  declare tmp_num int;
  declare done boolean default True;
  -- 聲明遊标對應的查詢集
  declare getGoods cursor for select gid, name, num from goods;
  -- 聲明退出條件的 handler
  declare continue handler for not found set done := False;
  
  open getGoods;
  -- 先取一行
  fetch getGoods into tmp_gid, tmp_name, tmp_num;
  -- while 循環 來取資料
  while done do
    -- 處理每行的業務邏輯
    select tmp_gid, tmp_name, tmp_num;
    -- 繼續往後fetch
    fetch getGoods into tmp_gid, tmp_name, tmp_num;
  end while;
  close getGoods;
end //
delimiter ;

-- out
mysql> call cur_while();
+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       1 | cat      |     100 |
+---------+----------+---------+
1 row in set (0.12 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       2 | dog      |      72 |
+---------+----------+---------+
1 row in set (0.27 sec)

+---------+----------+---------+
| tmp_gid | tmp_name | tmp_num |
+---------+----------+---------+
|       3 | pig      |      18 |
+---------+----------+---------+
1 row in set (0.38 sec)

Query OK, 0 rows affected (0.00 sec)

小結遊标

了解遊标cusror的概念, "移動的光标", 在腦海裡要有畫面感, 結合 C 的"指針" 和Python的 "=".
遊标的用處在于,可以依次去一行資料, 然後可按業務需求邏輯, 對其為所欲為.
遊标的文法: declare, open, fetch, close.
結合循環代碼實作

聲明 fetch 每行資料, 每個值的變量取接收資料
聲明一個狀态變量(boolean型), 當遊标走到最後時, 此變量改變狀态,并作為退出循環的依據
declare getGoods cursor for select xxxx;
declare continue handler for not found set done := False;
先試着取出一行 + 對應該行的業務邏輯處理
再進行循環取資料 + 業務邏輯
close cursor_name;

文法小細節

遊标聲明要在 declare變量之後, 在handler之前
注意不要忘了結束的 "; " 和關鍵字 for, set , 單詞拼錯, 這些
記得最好要close cursor_name; 釋放資源

Mysql 遊标初識

MySql 遊标初識

認識

文法

案例

小結遊标

繼續閱讀

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

DOG（4）：解析器的部分實作細節先來說說parser一些可能迷惑的地方結果如何傳回?pcd其實是一回事最後的一點說明

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案