当前位置:   article > 正文

MySQL Cursor 的定义和使用

mysql cursor

前言

最近项目中需要迁移数据,对旧表A 的每一行记录处理后插入 新表B。 Google一下,发现 MySQL Cursor 适合这种场景,上篇文章介绍了 handler,那么本篇就一起看下 Cursor 是什么吧!

本文基于 MySQL 8.0

定义

我们来看看 ChatGPT 是怎么理解MySQL Cursor 的吧:

  • 我:MySQL Cursor 是什么?

  • ChatGPT:MySQL 中的 Cursor 是一种用于在存储过程、函数等数据库对象中,按照一定顺序依次遍历结果集中每一条记录的机制。具体地说,使用 Cursor 可以将结果集中的记录一条一条地处理,每次处理一条记录,直到处理完所有记录。

    Cursor 的使用非常灵活,可以用于在数据库对象中对结果集进行迭代,执行某些计算、条件判断等操作,在不同记录之间进行状态转移等。可以使用 Cursor 访问结果集的每一行、每一列,并对结果集中的数据进行修改、删除、插入等操作。

    需要注意的是,使用 Cursor 需要谨慎,因为 Cursor 首先会将所有记录都读入到内存中,开销较大,而且 Cursor 可能会导致并发性和性能问题。除非必要,一般情况下不建议滥用 Cursor。

Cursor 就是游标,可以理解为 Java 集合迭代器的游标,或者数组的下标。在存储过程中对查询的数据进行处理时,由于 Select 返回的是个数据集,无法处理第一行、下一行、前十行的数据,除非每次修改 where 条件获得想要的那一条记录,而 Cursor 可以很方便的处理这种情况。

MySQL游标是只读的,不可滚动且不敏感的。

  • 只读:无法通过游标更新表中的数据。
  • 不可滚动:只能朝着一个方向挨个遍历,不能反向遍历;不能跳行或者跳转到特定行。
  • 不敏感:有两种光标:敏感型游标和不敏感游标。敏感型光标指向实际数据,而不敏感型光标使用数据的临时副本。敏感型游标比不敏感游标执行得更快,因为它不必创建临时数据副本。但是,其他连接所做的任何更改都将影响敏感光标正在使用的数据。MySQL游标是不敏感的。

Cursor 的使用

Cursor 的使用分为如下四步:

  • 声明游标
  • 打开游标
  • 获取数据
  • 关闭游标

声明游标

DECLARE cursor_name CURSOR FOR select_statement
  • 1

注意:

  • 游标声明中,必须有一个 Select 语句
  • Select 语句中不能包含 into 关键字
  • 游标声明,必须在 handler 声明之前,在 变量 和 condition 声明之后
  • 在一个代码块中可以声明多个游标,但是多个游标的名字不能相同

打开游标

OPEN cursor_name
  • 1

获取数据

FETCH cursor_name INTO var_name [, var_name] ...
  • 1

注意:

  • fetch 中的 cursor_name 必须是开启状态
  • fetch 语句中,var_name 的数量要与声明语句中 Select 查询列的数量一致,保证每个列都有变量接收

使用 fetch 获取游标中 Select 语句的下一条记录,并将游标前进一步。如果查询到数据,就将每列对应的值赋值给相应变量;如果已经到了数据集末尾,没有数据可以获取了,会产生一个值为 '02000' 的 SQLSTATE (NOT FOUND),此时可以声明一个 handler 来处理这种情况。

如果我们在一个代码块中有多个游标,那么一个游标 fetch 时如果产生 NOT FOUND,handler 的触发可能会对另一个游标产生影响。如果想避免这种影响,可以在多个 BEGIG…END 代码块中分别声明各自的handler,然后再操作游标,保证互不影响。

关闭游标

CLOSE cursor_name
  • 1
  • 如果关闭一个非开启状态的游标会报错
  • 对于声明在一个 BEGIN…END 中的游标,如果没有显示的使用 close 去关闭的话,执行到 END 会自动关闭游标

示例

如下示例中,我们声明了两个游标 cur1 和 cur2,分别查询 id、data 和 i 字段;还声明了一个 CONTINUE handler,当其中一个游标访问到数据集末尾时,设置变量 done 为 TRUE,用于控制循环退出。

其中几个注意点:

  1. 声明游标必须在 handler 声明之前,在 变量 和 condition 声明之后
  2. 多个游标名称不能重复
  3. 使用游标前需要先打开游标
  4. 游标中 select 列的数量,与 fetch into 变量数量一样
  5. 使用完关闭游标
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

总结

本篇文章介绍了 MySQL Cursor 的使用方式,主要分为四步:声明、开启、获取、关闭,比较适合在刷数据或迁移数据时使用。

更多

微信公众号:CodePlayer

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/知新_RL/article/detail/619393
推荐阅读
相关标签
  

闽ICP备14008679号