MySQL/MariaDB中游标的使用

1.游标说明

游标，有些地方也称为光标。它的作用是在一个结果集中逐条逐条地获取记录行并操作它们。

例如：

其中select是游标所操作的结果集，游标每次fetch一行中的name和age字段，并将每一行的这两个字段赋值给变量var1和var2。

有很多、很多、很多人，很多、很多、很多书都强烈建议：能不用游标尽量不要用游标。因为它违背了集合的理论，集合取数据是一把一把抓，游标取数据的时候一行一行取，每取一行操作一行，而且在每一行上都有额外的资源消耗。总之，游标效率低、资源消耗高。

其实很多领域都有这样的优化：把数据先集中起来，集中到了一定量再一次性处理，这样的处理方式效率要高得多。比如写日志到磁盘上，可以每产生一条日志就刷入磁盘，也可以先产生一堆日志缓存起来，之后一次性刷如磁盘。后者效率要高得多。

集合取数据的时候关注点在于想要什么数据，而不关注怎么去获取数据，游标的关注点则在于怎么获取这些数据：将游标指针作为遍历依据，遍历到哪行数据就返回这行数据然后停下来处理数据，再继续遍历数据。习惯于迭代的人比较喜欢游标，特别是习惯C语言的人，因为游标就是遍历数据行的行为。

在MySQL、MariaDB中实现的游标比较简单，它只有一种遍历方式：逐行向前遍历。MariaDB 10.3后，游标方面支持的更完整一点：支持游标参数。

光标的使用包括声明光标、打开光标、使用光标和关闭光标(MySQL/MariaDB中的游标无需释放)。光标必须声明在处理程序之前，并且在声明保存结果集的变量之后。另外，游标是一种复合语句结构(就像begin...end)，只能用于stored procedure或stored function中。

2.使用游标

1.声明游标

DECLARE cursor_name CURSOR FOR select_statement;

其中select_statement是游标需要从中获取的结果集。

例如：

declare cur_city cursor for select id,'name',population from world,city;

在MariaDB 10.3中，支持游标参数，该参数可以传递到select_statement中：

DECLARE cursor_name CURSOR(param1 data_type,param2 data_type2...) FOR select_statement;

例如：

declare cur_stu cursor(min int,max int) for select id,name from Student where id between min and max;

注意，mariaDB 10.3之前的语法也能在10.3版本上执行，因为之前的语法是10.3版本中不带参数的特殊情况。

2.声明处理程序

一般来说，光标是用在逐条取结果集的情况下，所以在使用光标的时候基本都会放在循环结构中循环获取数据存储到变量中。但如何在取完数据后退出循环？

在游标无法获取到下一行数据的时候，将会返回一个1329错误码，这个错误码对应的SQL状态码为"02000"，它们等价于NOT FOUND(这几个是等价的，只是MariaDB中分了3类描述问题的代码而已)。这时可以在声明游标后定义一个handler，用于处理NOT FOUND。

例如下面是适合游标NOT FOUND时的CONTINUE处理器，表示当找不到下一行数据时继续执行后面的程序：

DECLARE CONTINUE HANDLER FOR NOT FOUND statement;

对于处理游标的HANDLER，通常statement部分是SET语句，用于设置一些变量。例如：

declare continue handler for not found set var_name=value;

这时，当取不到下一条记录时即已经取完记录时，就设置变量var_name=value。之后就可以通过该变量的值作为退出循环的条件。

关于handler详细内容，见我翻译的MariaDB手册：https://mariadb.com/kb/zh-cn/declare-handler/

3.打开游标

当声明了一个游标后，必须要打开游标才能使用游标。

open cursor_name;

例如：

open cur_city;

对于mariadb 10.3，由于支持游标参数，因此语法为：

open cursor_name(value1,value2);

例如：

open cur_stu(4,10);

4.使用游标(fetch into)

通过fetch into命令将每次fetch到的结果存储到预先定义好的变量中。注意，这个变量必须是本地变量(局部变量)，不能是用户自定义变量，且这个变量必须定义在游标声明语句之前。

fetch cursor_name into var_name;

例如：

fetch cur_city into city_id,city_name,city_popcnt;

在上面已经说过了，一般游标都会在循环结构中使用。以下是在repeat结构中使用游标；

repeat
    fetch ... into ...
    until var_name=value
end repeat;

5.关闭游标

close cursor_name;

例如：

close cur_city;

3.游标使用示例

以下是MariaDB 10.3版本之前(也适用于10.3)的游标使用示例：将表t1和表t2中每行中的某一列作比较，将较大值插入到表t3中。

create or replace table t1(i int);
create or replace table t2(i int);
create or replace table t3(i int);

insert into t1 values(5),(10),(20);
insert into t2 values(15),(30),(10);


delimiter $$

create or replace procedure proc1()
begin
    declare done int default false;   /* 用于判断退出循环 */
    declare x,y int;                  /* 用于保存fetch结果 */
    declare cur1 cursor for select i from t1;    /* fetch t1的游标 */
    declare cur2 cursor for select i from t2;    /* fetch t2的游标 */
    declare continue handler for not found set done=true;   /* not found时，退出循环 */

    open cur1;
    open cur2;

    my_loop: LOOP
        fetch cur1 into x;
        fetch cur2 into y;
        if done then
            leave my_loop;
        end if;
    if  x <= y then 
        insert into t3 values(y);
    else
        insert into t3 values(x);
    end if;
    end loop;

    close cur1;
    close cur2;
end$$

delimiter ;

call proc1;

查看表t3：

select * from t3;
+------+
| i    |
+------+
|   15 |
|   30 |
|   20 |
+------+

下面是MariaDB 10.3上使用游标的一个示例：将表t1中i字段某一段数据插入到表t2中。

create or replace table t1(i int);
create or replace table t2(i int);

insert into t1 values(5),(10),(20),(30),(40);

delimiter $$

create or replace procedure proc1(min int,max int)
begin
    declare done int default false;
    declare x int;
    declare cur1 cursor(cmin int,cmax int) for select i from t1 where t1.i between cmin and cmax;
    declare continue handler for not found set done=true;

    open cur1(min,max);

    my_loop: LOOP
        fetch cur1 into x;
        if done then
            leave my_loop;
        end if;
        insert into t2 values(x);
    end loop;

    close cur1;
end$$

delimiter ;

call proc1(10,40);

查看t2结果：

MariaDB [test]> select * from t2;
+------+
| i    |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
+------+