日志

Mysql高手系列 - 第19篇:mysql游标详解,此技能可用于救火

 来源    2019-10-09    1  

Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

这是Mysql系列第19篇。

环境:mysql5.7.25,cmd命令中进行演示。

代码中被[]包含的表示可选,|符号分开的表示可选其一。

需求背景

当我们需要对一个select的查询结果进行遍历处理的时候,如何实现呢?

此时我们需要使用游标,通过游标的方式来遍历select查询的结果集,然后对每行数据进行处理。

本篇内容

  • 游标定义
  • 游标作用
  • 游标使用步骤
  • 游标执行过程详解
  • 单游标示例
  • 嵌套游标示例

准备数据

创建库:javacode2018

创建表:test1、test2、test3

/*建库javacode2018*/
drop database if exists javacode2018;
create database javacode2018;

/*切换到javacode2018库*/
use javacode2018;

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);

游标定义

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

游标只能在存储过程和函数中使用。

游标的作用

如sql:

select a,b from test1;

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

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

游标的使用步骤

声明游标:这个过程只是创建了一个游标,需要指定这个游标需要遍历的select查询,声明游标时并不会去执行这个sql。

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

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

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

游标语法

声明游标

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

一个begin end中只能声明一个游标。

打开游标

open 游标名称;

遍历游标

fetch 游标名称 into 变量列表;

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

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

关闭游标

close 游标名称;

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

单游标示例

写一个函数,计算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 ;

上面语句执行过程中可能有问题,解决方式如下。

错误信息:Mysql 创建函数出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA

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

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

解决办法1:

执行:

SET GLOBAL log_bin_trust_function_creators = 1;

不过 重启了 就失效了

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

解决办法2:

在my.cnf里面设置

log-bin-trust-function-creators=1

不过这个需要重启服务

见效果:

mysql> SELECT a,b 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)

游标过程详解

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

游标中有个指针,当打开游标的时候,才会执行游标对应的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, 9 rows affected (0.00 sec)

mysql> SELECT * FROM test1;
Empty set (0.00 sec)

mysql> CALL proc1();
Query OK, 0 rows affected (0.02 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)

成功插入了9条数据。

总结

  1. 游标用来对查询结果进行遍历处理
  2. 游标的使用过程:声明游标、打开游标、遍历游标、关闭游标
  3. 游标只能在存储过程和函数中使用
  4. 一个begin end中只能声明一个游标
  5. 掌握单个游标及嵌套游标的使用
  6. 大家下去了多练习一下,熟练掌握游标的使用

Mysql系列目录

  1. 第1篇:mysql基础知识
  2. 第2篇:详解mysql数据类型(重点)
  3. 第3篇:管理员必备技能(必须掌握)
  4. 第4篇:DDL常见操作
  5. 第5篇:DML操作汇总(insert,update,delete)
  6. 第6篇:select查询基础篇
  7. 第7篇:玩转select条件查询,避免采坑
  8. 第8篇:详解排序和分页(order by & limit)
  9. 第9篇:分组查询详解(group by & having)
  10. 第10篇:常用的几十个函数详解
  11. 第11篇:深入了解连接查询及原理
  12. 第12篇:子查询
  13. 第13篇:细说NULL导致的神坑,让人防不胜防
  14. 第14篇:详解事务
  15. 第15篇:详解视图
  16. 第16篇:变量详解
  17. 第17篇:存储过程&自定义函数详解
  18. 第18篇:流程控制语句
  19. 第19篇:游标详解
  20. 第20篇:异常捕获及处理详解
  21. 第21篇:什么是索引?

mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!

相关文章
Mysql高手系列 - 第14篇:详解事务
日志这是Mysql系列第14篇. 环境:mysql5.7.25,cmd命令中进行演示. 开发过程中,会经常用到数据库事务,所以本章非常重要. 本篇内容 什么是事务,它有什么用? 事务的几个特性 事务常见操 ...
Mysql高手系列 - 第13篇:细说NULL导致的神坑,让人防不胜防
日志这是Mysql系列第13篇. 环境:mysql5.7.25,cmd命令中进行演示. 当数据的值为NULL的时候,可能出现各种意想不到的效果,让人防不胜防,我们来看看NULL导致的各种神坑,如何避免? ...
Mysql高手系列 - 第12篇:子查询详解
日志这是Mysql系列第12篇. 环境:mysql5.7.25,cmd命令中进行演示. 本章节非常重要. 子查询 出现在select语句中的select语句,称为子查询或内查询. 外部的select查询语 ...
Mysql高手系列 - 第10篇:常用的几十个函数详解,收藏慢慢看
日志这是Mysql系列第10篇. 环境:mysql5.7.25,cmd命令中进行演示. MySQL 数值型函数 函数名称 作 用 abs 求绝对值 sqrt 求二次方根 mod 求余数 ceil 和 ce ...
Mysql高手系列 - 第11篇:深入了解连接查询及原理
日志这是Mysql系列第11篇. 环境:mysql5.7.25,cmd命令中进行演示. 当我们查询的数据来源于多张表的时候,我们需要用到连接查询,连接查询使用率非常高,希望大家都务必掌握. 本文内容 笛卡 ...
Mysql高手系列 - 第9篇:详解分组查询,mysql分组有大坑!
日志这是Mysql系列第9篇. 环境:mysql5.7.25,cmd命令中进行演示. 本篇内容 分组查询语法 聚合函数 单字段分组 多字段分组 分组前筛选数据 分组后筛选数据 where和having的区 ...
Mysql高手系列 - 第8篇:详解排序和分页(order by & limit),及存在的坑
日志这是Mysql系列第8篇. 环境:mysql5.7.25,cmd命令中进行演示. 代码中被[]包含的表示可选,|符号分开的表示可选其一. 本章内容 详解排序查询 详解limit limit存在的坑 分 ...
Mysql高手系列 - 第7篇:玩转select条件查询,避免踩坑
日志这是Mysql系列第7篇. 环境:mysql5.7.25,cmd命令中进行演示. 电商中:我们想查看某个用户所有的订单,或者想查看某个用户在某个时间段内所有的订单,此时我们需要对订单表数据进行筛选,按 ...
Mysql高手系列 - 第18篇:mysql流程控制语句详解(高手进阶)
日志Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能. 这是Mysql系列第18篇. 环境:mysql5.7.25,cmd命令中进行演示. 代码中被[]包含的表示可选,|符 ...
1
Mysql高手系列 - 第24篇:如何正确的使用索引?高手进阶
日志Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能. 欢迎大家加我微信itsoku一起交流java.算法.数据库相关技术. 这是Mysql系列第24篇. 学习索引,主要是 ...
1
JUC系列第三篇 -CAS算法详解
日志作者 : 毕来生 微信: 878799579 1.CAS是什么? CAS是英文单词(Compare-And-Swap)的缩写,中文意思是:比较并替换.CAS需要有3个操作数:内存地址V,旧的预期值A, ...
Mysql高手系列 - 第5天:DML操作汇总,确定你都会?
日志这是Mysql系列第5篇. 环境:mysql5.7.25,cmd命令中进行演示. DML(Data Manipulation Language)数据操作语言,以INSERT.UPDATE.DELETE ...
mysql 开发进阶篇系列 31 工具篇(mysql连接工具与MyISAM表压缩工具)
日志一.mysql 连接工具 在mysq提供的工具中,DBA使用最频繁的莫过于mysql.这里的mysql是指连接数据库的客户端工具. 1.1 连接选项 -u, -- user=name 指定用户名 -p ...
Python系列之入门篇——MYSQL
日志Python系列之入门篇--MYSQL 简介 python提供了两种mysql api, 一是MySQL-python(不支持python3),二是PyMYSQL(支持python2和python3) ...
mysql基础篇 - SELECT 语句详解
日志基础篇 - SELECT 语句详解 SELECT语句详解 一.实验简介 SQL 中最常用的 SELECT 语句,用来在表中选取数据,本节实验中将通过一系列的动手操作详细学习 SELECT 语句的用法. ...
Mysql—配置文件my.ini或my.cnf的详解
日志[mysqld] log_bin = mysql-bin binlog_format = mixed expire_logs_days = 7 # 超过7天的binlog删除 slow_query_l ...
[js高手之路] es6系列教程 - 函数的默认参数详解
日志在ES6之前,我们一般用短路表达式处理默认参数 function show( a, b ){ var a = a || 10; var b = b || 20; console.log( a, b ) ...
cloudera-scm-server启动出现Error creating bean with name 'entityManagerFactoryBean'与HHH010003: JDBC Driver class not found: com.mysql.jdbc.Driver错误解决办法(图文详解)
日志  不多说,直接上干货! 问题详情 2017-07-31 22:19:40,342 INFO main:com.cloudera.server.cmf.Main: Starting SCM Serve ...
Mysql自增主键ID重新排序方法详解
日志Mysql数据库表的自增主键ID号乱了,需要重新排列. 原理:删除原有的自增ID,重新建立新的自增ID. 1,删除原有主键: ALTER TABLE `table_name` DROP `id`; 2 ...