mysql游标概述

官方文档：https://dev.mysql.com/doc/refman/5.7/en/cursors.html

定义

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。

游标也是一种面向过程的sql编程方法，所以一般在存储过程、函数、触发器、循环处理中使用。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

游标的作用

select a,b from test1;

上面这个查询返回了test1中的数据，如果我们想对这些数据进行遍历处理，此时我们就可以使用游标来进行操作。

游标相当于一个指针，这个指针指向select的第一行数据，可以通过移动指针来遍历后面的数据。

游标的使用

在mysql中，游标可以在存储过程、函数、触发器和事件中使用。游标需要与相关handler一起使用，并在handler之前定义。游标有以下三个属性：

• Asensitive: 数据库也可以选择不复制结果集

• Read only: 不可更新，只读

• Nonscrollable: 游标只能向一个方向前进，并且不可以跳过任何一行数据。

  Cursor declarations must appear before handler declarations and after variable and condition declarations.

声明游标：创建一个游标，并指定这个游标需要遍历的select查询，声明游标时并不会去执行这个sql。

打开游标：打开游标的时候，会执行游标对应的select语句。

遍历数据：使用游标循环遍历select结果中每一行数据，然后进行处理。

业务操作：对遍历到的每行数据进行操作的过程，可以放置任何需要执行的执行的语句(增删改查):这里视具体情况而定

关闭游标：游标使用完之后一定要释放（游标占用的内存还是有点大的）。

注:使用的临时字段需要在定义游标之前进行声明。

游标语法

游标的使用过程：声明游标、打开游标、遍历游标、关闭游标

声明游标

https://dev.mysql.com/doc/refman/5.7/en/declare-cursor.html

DECLARE 游标名称 CURSOR FOR 查询语句;

游标可以声明多个，但一个begin end中只能声明一个游标。

打开游标

https://dev.mysql.com/doc/refman/5.7/en/open.html

open 游标名称;

遍历游标

https://dev.mysql.com/doc/refman/5.7/en/fetch.html

fetch 游标名称 into 变量列表;

取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。

当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的 NOT FOUND错误

关闭游标

https://dev.mysql.com/doc/refman/5.7/en/close.html

close 游标名称;

游标使用完毕之后一定要关闭。

条件处理

DECLARE CONTINE HANDLER 表达式 1 SET 表达式 2：

这段代码的作用是定义一个 CONTINE HANDLER，这个的作用是当表达式 1 的条件出现时，将执行表达式 2 的语句。用这个语句可以实现条件的变更

实质是利用mysql的异常处理，也常常在游标上使用，来辅助判断游标数据是否遍历完了

例如DECLARE CONTINUE HANDLER FOR NOT FOUND …的语句，这是为了对游标没有下一条记录可供访问的情况做出异常处理。

准备测试数据

创建表：test1、test2、test3

DROP TABLE IF EXISTS test1; 
CREATE TABLE test1(a int,b int); 
INSERT INTO test1 VALUES (1,2),(3,4),(5,6); 

DROP TABLE IF EXISTS test2; 
CREATE TABLE test2(a int); 
INSERT INTO test2 VALUES (100),(200),(300); 

DROP TABLE IF EXISTS test3; 
CREATE TABLE test3(b int); 
INSERT INTO test3 VALUES (400),(500),(600);

示例

写一个函数，计算test1表中a、b字段所有的和。

/*删除函数*/ 
DROP FUNCTION IF EXISTS fun1; 
/*声明结束符为$*/ 
DELIMITER $ 

/*创建函数*/ 
CREATE FUNCTION fun1(v_max_a int) 
  RETURNS int 
  BEGIN 
    /*用于保存结果*/ 
    DECLARE v_total int DEFAULT 0; 
    /*创建一个变量，用来保存当前行中a的值*/
    DECLARE v_a int DEFAULT 0; 
    /*创建一个变量，用来保存当前行中b的值*/ 
    DECLARE v_b int DEFAULT 0; 
    /*创建游标结束标志变量*/ 
    DECLARE v_done int DEFAULT FALSE; 
    /*创建游标*/ 
    DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a; 
    /*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/ 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 
    /*设置v_total初始值*/ 
    SET v_total = 0; 
    /*打开游标*/ 
    OPEN cur_test1; 
    /*使用Loop循环遍历游标*/ 
    a:LOOP 
      /*先获取当前行的数据，然后将当前行的数据放入v_a,v_b中，如果当前行无数据，v_done会被置 为true*/
      FETCH cur_test1 INTO v_a, v_b; 
      /*通过v_done来判断游标是否结束了，退出循环*/ 
      if v_done THEN 
      LEAVE a; 
      END IF; 
      /*对v_total值累加处理*/ 
      SET v_total = v_total + v_a + v_b;
    END LOOP; 
    /*关闭游标*/ 
    CLOSE cur_test1; 
    /*返回结果*/ 
    RETURN v_total; 
  END $ 
/*结束符置为;*/ 
DELIMITER ;

其中DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 是异常处理的语法

意思是当遇到NOT FOUND错误时,将v_done设为ture，continue继续执行当前任务

同样，创建函数在开启了bin-log的数据库可能报错

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declarationand binary

mysql的设置默认是不允许创建函数

解决办法1:

执行：

SET GLOBAL log_bin_trust_function_creators = 1;

不过 重启了 就失效了

注意： 有主从复制的时候 从机必须要设置 不然会导致主从同步失败

解决办法2：

在my.cnf里面设置

log-bin-trust-function-creators=1

不过这个需要重启服务

测试：

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)

mysql> select fun1(1);
+---------+
| fun1(1) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select fun1(2);
+---------+
| fun1(2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select fun1(3);
+---------+
| fun1(3) |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

mysql> select fun1(4);
+---------+
| fun1(4) |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

mysql> select fun1(6);
+---------+
| fun1(6) |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)

mysql> select fun1(5);
+---------+
| fun1(5) |
+---------+
|      21 |
+---------+

游标过程解析

以上面的示例代码为例，看一下游标的详细执行过程。

游标中有个指针，当打开游标的时候，才会执行游标对应的select语句，这个指针会指向select结果中第一行记录。

当调用 fetch 游标名称 时，会获取当前行的数据，如果当前行无数据，会触发 NOT FOUND 异常

当触发 NOT FOUND 异常的时候，我们可以使用一个变量来标记一下，如下代码：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;

当游标无数据触发 NOT FOUND 异常的时候，将变量 v_down 的值置为 TURE ，循环中就可以通过v_down 的值控制循环的退出。

如果当前行有数据，则将当前行数据存到对应的变量中，并将游标指针指向下一行数据，如下语句：

fetch 游标名称 into 变量列表;

嵌套游标

写个存储过程，遍历test2、test3，将test2中的a字段和test3中的b字段任意组合，插入到test1表中。

创建存储过程

/*删除存储过程*/ 
DROP PROCEDURE IF EXISTS proc1; 
/*声明结束符为$*/ 
DELIMITER $ 

/*创建存储过程*/ 
CREATE PROCEDURE proc1() 
  BEGIN 
    /*创建一个变量，用来保存当前行中a的值*/ 
    DECLARE v_a int DEFAULT 0; 
    /*创建游标结束标志变量*/ 
    DECLARE v_done1 int DEFAULT FALSE; 
    /*创建游标*/ 
    DECLARE cur_test1 CURSOR FOR SELECT a FROM test2; 
    /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_test1是否结束了*/ 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1=TRUE; 
    /*打开游标*/
    OPEN cur_test1; 
    /*使用Loop循环遍历游标*/ 
    a:LOOP 
      FETCH cur_test1 INTO v_a; 
      /*通过v_done1来判断游标是否结束了，退出循环*/ 
      if v_done1 THEN 
        LEAVE a; 
      END IF;
      BEGIN 
        /*创建一个变量，用来保存当前行中b的值*/ 
        DECLARE v_b int DEFAULT 0; 
        /*创建游标结束标志变量*/
        DECLARE v_done2 int DEFAULT FALSE; 
        /*创建游标*/ 
        DECLARE cur_test2 CURSOR FOR SELECT b FROM test3; 
        /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_test2是否结束了*/ 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done2=TRUE; 
        /*打开游标*/ 
        OPEN cur_test2; 
        /*使用Loop循环遍历游标*/ 
        b:LOOP 
          FETCH cur_test2 INTO v_b; 
          /*通过v_done1来判断游标是否结束了，退出循环*/ 
          if v_done2 THEN 
            LEAVE b; 
          END IF; 
          
          /*将v_a、v_b插入test1表中*/ 
          INSERT INTO test1 VALUES (v_a,v_b); 
          
        END LOOP b; 
        /*关闭cur_test2游标*/
        CLOSE cur_test2; 
      END; 
    END LOOP; 
    /*关闭游标cur_test1*/ 
    CLOSE cur_test1; 
  END $ 
/*结束符置为;*/ 
DELIMITER ;

测试：

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

mysql> select *  from test1;
Empty set (0.00 sec)

mysql> call proc1();
Query OK, 0 rows affected (0.06 sec)

mysql> select *  from test1;
+------+------+
| a    | b    |
+------+------+
|  100 |  400 |
|  100 |  500 |
|  100 |  600 |
|  200 |  400 |
|  200 |  500 |
|  200 |  600 |
|  300 |  400 |
|  300 |  500 |
|  300 |  600 |
+------+------+
9 rows in set (0.00 sec)

总结

• 游标是存储在DBMS上的数据库查询，是被检索出来的结果集，在存储了游标之后，通过游标来对查询结果进行遍历处理，它是面向过程的
• 游标的主要开销在于对每行进行处理，处理过程越复杂，游标效率越低，但在某些特殊情况下游标效率很高，如连续聚合情况下，分组有较多数据时，游标远比基于集合的方法快，因为游标从始至终只扫表一次
• 游标是面向过程的，普通sql语句是面向集合的
• 游标的使用过程：声明游标、打开游标、遍历游标、关闭游标
• 一个begin end中只能有一个游标
• 游标可以在存储过程、触发器、函数等中使用