乐趣区

关于mysql:解析MySQL存储过程的游标执行过程

  • GreatSQL 社区原创内容未经受权不得随便应用,转载请分割小编并注明起源。

内容提纲

一、测试环境搭建

二、执行过程解析

三、注意事项

一、测试环境搭建

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

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 MGR FAQ
https://mp.weixin.qq.com/s/J6…

万答 #12,MGR 整个集群挂掉后,如何能力主动选主,不必手动干涉
https://mp.weixin.qq.com/s/07…

『2021 数据技术嘉年华·ON LINE』:《MySQL 高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7…

一条 sql 语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY…

万答 #15,都有哪些状况可能导致 MGR 服务无奈启动
https://mp.weixin.qq.com/s/in…

技术分享 | 为什么 MGR 一致性模式不举荐 AFTER
https://mp.weixin.qq.com/s/rN…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支,专一于晋升 MGR 可靠性及性能,反对 InnoDB 并行查问个性,是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili:
https://space.bilibili.com/13…

微信 &QQ 群:
可搜寻增加 GreatSQL 社区助手微信好友,发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群:533341697
微信小助手:wanlidbc

本文由博客一文多发平台 OpenWrite 公布!

退出移动版