解析MySQL存储过程的游标执行过程

ywz888
发布于 2022-9-6 11:37
浏览
0收藏

内容提纲
一、测试环境搭建

二、执行过程解析

三、注意事项

 

一、测试环境搭建


首先创建一张表,并插入几行数据字段:

CREATE TABLE t (s1 INT, s2 char(100),PRIMARY KEY (s1));
INSERT INTO t values(1,'aaa');
INSERT INTO t values(2,'bbb');
INSERT INTO t values(3,'ccc');

 

接着创建存储过程,这里的v_total用于判断数据行数。

 

因为MySQL游标获取完后,最后一行没有退出机制。所以不进行判断是否取完最后一行,就继续取数会发生报错。

DELIMITER ;;
  CREATE PROCEDURE test_mysql_cursor_loop()
    BEGIN
      declare v_total int default 0; 
      declare i int default 0; 
      declare str1 int;
      declare str2 varchar(255);
      DECLARE stuCursor CURSOR FOR SELECT s1,s2 FROM t;
      select count(s2) into v_total from t;
      OPEN stuCursor;
      stuLoop:LOOP
        IF i = v_total THEN LEAVE stuLoop; end if;
        FETCH stuCursor INTO str1,str2;
          SELECT str1,str2;
          set i = i+1;
      end loop stuLoop;
    END;;
DELIMITER ;

 

二、执行过程解析


这个存储过程在MySQL内部转化为存储过程指令如下:

mysql> show PROCEDURE code test_mysql_cursor_loop;
+-----+---------------------------------------------+
| Pos | Instruction                                 |
+-----+---------------------------------------------+
|   0 | set v_total@0 0                             |
|   1 | set i@1 0                                   |
|   2 | set str1@2 NULL                             |
|   3 | set str2@3 NULL                             |
|   4 | cpush stuCursor@0: SELECT s1,s2 FROM t      |
|   5 | stmt "select count(s2) into v_total from t" |
|   6 | copen stuCursor@0                           |
|   7 | jump_if_not 9(9) (i@1 = v_total@0)          |
|   8 | jump 13                                     |
|   9 | cfetch stuCursor@0 str1@2 str2@3            |
|  10 | stmt "SELECT str1,str2"                     |
|  11 | set i@1 (i@1 + 1)                           |
|  12 | jump 7                                      |
|  13 | cclose stuCursor@0                          |
|  14 | cpop 1                                      |
+-----+---------------------------------------------+
15 rows in set (0.00 sec)

 

其中:

 

● v_total@0的 @0 代表这是第一个自定义变量。


● stuCursor@0的 @0 代表这是第一个cursor变量。


从指令可以看出cursor的运行需要经过几个过程:

 

1、cpush

 

这个用于注册一个cursor,初始化堆栈。

 

2、copen

 

这里会先运行SELECT s1,s2 FROM t指令,让cursor获得表的列信息,然后调用cpush,经过cpush->open来打开一个cursor,用于后续获取表数据。

 

3、cfetch

 

这个用于从cursor获取的数据赋值给自定义变量str1,str2,然后传递给client端显示。注意到这里用了jump 7来循环取数据。

 

4、cclose

 

关闭cursor。

 

5、cpop

 

把当前cursor从堆栈释放掉。

 

上面循环取数和退出机制如下:

 

jump_if_not 9(9) (i@1 = v_total@0)用于判断i@1是否等于v_total@0,如果不等于的话当前指令就跳转到第9个指令,继续执行cfetch。

 

如果i@1等于v_total@0的话就继续执行下一条jump 13关闭cursor退出当前循环。set i@1 (i@1 + 1)用于计数,jump 7用于继续循环取数。

 

三、注意事项


str1和str2的类型和数量必须与declare cursor的一致,如果声明不正确就会导致cfetch出错。

 

因为declare str1和str2之后,mysql就会对这两个列建立一张临时表,当cfetch数据之后就会把数据存入这张表,后续用户select的时候就从这张临时表取数据来显示,所以就必须进行正确的变量声明。

 


Enjoy MySQL :)

 

文章转载自公众号:GreatSQL社区

分类
标签
已于2022-9-6 11:37:27修改
收藏
回复
举报
回复
    相关推荐