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