Mysql 游標初識_ZenDei技術網路在線

MySql 游標初識認識游標(cursor), 按字面意思可理解為, 游動的標識, 或者叫做 "游標" , 這樣更容易理解. 就好比現有一張表存儲了n行記錄, 然後我想每次取出一行, 進行為所欲為, 這時候就用到了游標cursor, 數據的搬運工, 搬運完當前數據(游標指向當前), 然後又移動到 ...

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的服務, 這些服務, 就是"介面".舉幾個慄子.

我想開車, 車裡面是什麼樣的我並不關係, 這時候車給了提供了, 方向盤, 油門,離合器, 剎車, 擋位等, 我就是能開了, 這些就是"介面" (好像不太恰當哦); 或者是想學外語, 這時候給我提給了一張VIP卡, 那我就可以學外語了, 這張VIP卡, 就是介面.

我編寫了一個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; 釋放資源