欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mysql 游标初识

程序员文章站 2023-12-30 21:40:58
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; 释放资源

上一篇:

下一篇: