• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MySQL 游标的作用与使用相关

    定义

    我们经常会遇到这样的一种情况,需要对我们查询的结果进行遍历操作,并对遍历到的每一条数据进行处理,这时候就会使用到游标。
    所以:游标(Cursor)是处理数据的一种存储在MySQL服务器上的数据库查询方法,为了查看或者处理结果集中的数据,提供了在结果集中一次一行遍历数据的能力。
    游标主要用在循环处理、存储过程、函数、触发器 中。

    游标的作用

    比如我们上面那个students学生,需要对每个用户进行遍历,然后根据他们的其他评价进行加分或者减分。这时候我们就需要查询到所有的学生信息(包含成绩)。

    select studentid,studentname,score from students; 

    执行之后返回了的学生数据集合,我们如果需要对学生数据逐一遍历,然后根据具体的情况进行加分,那就需要是使用游标了。
    游标相当于一个指针,这个指针指向select的第一行数据,可以通过移动指针来遍历后面的数据。 

    游标的使用

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

    声明游标

    DECLARE cursor_name CURSOR FOR select_statement; 

    声明一个游标。也可以在子程序中定义多个游标,但是一个块中的每一个游标必须有唯一的名字。声明游标后也是单条操作的,但是SELECT语句不能有INTO子句。
    一个begin end中只能声明一个游标。

    打开游标

    OPEN cursor_name; 

    打开先前声明的游标。

    遍历游标数据

    FETCH cursor_name INTO var_list;

    这个语句用指定的打开游标读取下一行(如果有下一行的话),并且前进游标指针。取出当前行的结果,将结果放在对应的变量中,并将游标指针指向下一行的数据。
    当调用fetch的时候,会获取当前行的数据,如果当前行无数据,会引发mysql内部的NOT FOUND错误。

    关闭游标

    CLOSE cursor_name; 

    切记游标使用完毕之后要关闭。

    游标举例

    写一个函数,里面包含对students 学生用户成绩的计算和附加分计算

    数据基础

    mysql> select * from students;
    +-----------+-------------+-------+---------+
    | studentid | studentname | score | classid |
    +-----------+-------------+-------+---------+
    | 1 | brand | 97.5 | 1 |
    | 2 | helen | 96.5 | 1 |
    | 3 | lyn | 96 | 1 |
    | 4 | sol | 97 | 1 |
    | 5 | b1 | 81 | 2 |
    | 6 | b2 | 82 | 2 |
    | 7 | c1 | 71 | 3 |
    | 8 | c2 | 72.5 | 3 |
    | 9 | lala | 73 | 0 |
    | 10 | A | 99 | 3 |
    | 16 | test1 | 100 | 0 |
    | 17 | trigger2 | 107 | 0 |
    | 22 | trigger1 | 100 | 0 |
    +-----------+-------------+-------+---------+
    13 rows in set

    编写包含游标的函数

    这边注释很清晰

    mysql>
    /*判断函数如果存在则删除*/
    DROP FUNCTION IF EXISTS fun_test;
    /*声明结束符为$*/
    DELIMITER $
    /*创建函数,对符合条件的每个同学的分数进行加分,加的分数不能超过给定的值max_score*/
    CREATE FUNCTION fun_test(max_score decimal(10,2))
    RETURNS int
    BEGIN
    /*定义实时StudentId的变量*/
    DECLARE var_studentId int DEFAULT 0;
    /*定义计算后分数的变量*/
    DECLARE var_score decimal(10,2) DEFAULT 0;
    /*定义游标结束标志变量*/
    DECLARE var_done int DEFAULT FALSE;
    /*创建游标*/
    DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid>0;
    /*游标结束时会设置var_done为true,后续可以使用var_done来判断游标是否结束*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
    /*打开游标*/
    OPEN cur_test;
    /*使用Loop循环遍历游标*/
    select_loop:LOOP
    /*先获取当前行的数据,然后将当前行的数据放入var_studentId,var_score中,如果无数据行了,var_done会被置为true*/
    FETCH cur_test INTO var_studentId,var_score;
    /*通过var_done来判断游标是否结束了,退出循环*/
    IF var_done THEN
    LEAVE select_loop;
    END IF;
    /*对var_score值添加随机值,不能超过给定的分数*/
    set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
    update students set score = var_score where studentId= var_studentId;
    END LOOP;
    /*关闭游标*/
    CLOSE cur_test;
    /*返回结果:可以根据实际情况返回需要的内容*/
    RETURN 1;
    END $
    /*结束符置为;*/
    DELIMITER ;
    Query OK, 0 rows affected

    调用函数

    mysql>
    /* 参数为8,表示加分上限为8 */
    select fun_test(8);
    +-------------+
    | fun_test(8) |
    +-------------+
    | 1 |
    +-------------+
    1 row in set

    查看结果

    对比原来的成绩的值,发现成绩添加了随机值,但没超过给定的分数 8

    mysql> select * from students;
    +-----------+-------------+-------+---------+
    | studentid | studentname | score | classid |
    +-----------+-------------+-------+---------+
    | 1 | brand | 105.5 | 1 |
    | 2 | helen | 98.5 | 1 |
    | 3 | lyn | 97 | 1 |
    | 4 | sol | 97 | 1 |
    | 5 | b1 | 89 | 2 |
    | 6 | b2 | 90 | 2 |
    | 7 | c1 | 76 | 3 |
    | 8 | c2 | 73.5 | 3 |
    | 9 | lala | 73 | 0 |
    | 10 | A | 100 | 3 |
    | 16 | test1 | 100 | 0 |
    | 17 | trigger2 | 107 | 0 |
    | 22 | trigger1 | 100 | 0 |
    +-----------+-------------+-------+---------+
    13 rows in set

    查看触发器日志

    符合条件被修改分数的有9条数据,都已经被触发器记录到日志里面了

    mysql>
    /*上一篇编写了触发器,当修改students表的时候触发日志记录 */
    select * from triggerlog;
    +----+--------------+---------------+-----------------------------------------+
    | id | trigger_time | trigger_event | memo |
    +----+--------------+---------------+-----------------------------------------+
    | 1 | after | insert | new student info,id:21 |
    | 2 | after | update | update student info,id:21 |
    | 3 | after | update | delete student info,id:21 |
    | 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
    | 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
    | 6 | after | update | delete student info,id:11 |
    | 7 | after | update | from:brand,97.50 to:brand,105.50 |
    | 8 | after | update | from:helen,96.50 to:helen,98.50 |
    | 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
    | 10 | after | update | from:sol,97.00 to:sol,97.00 |
    | 11 | after | update | from:b1,81.00 to:b1,89.00 |
    | 12 | after | update | from:b2,82.00 to:b2,90.00 |
    | 13 | after | update | from:c1,71.00 to:c1,76.00 |
    | 14 | after | update | from:c2,72.50 to:c2,73.50 |
    | 15 | after | update | from:A,99.00 to:A,100.00 |
    +----+--------------+---------------+-----------------------------------------+
    15 rows in set

    游标的执行过程

    按照上面的例子,分析下这个游标的执行过程。
    1、我们创建了一个游标,数据源取自于student学生表。
    2、游标中有个指针,当打开游标的时候,会执行游标对应的select语句,这个指针会指向select结果中第一行记录。
    3、当调用fetch 游标名称时,会获取当前行的数据,如果当前行无数据,会触发NOT FOUND异常。
    当触发NOT FOUND异常的时候,我们可以使用一个变量来标记一下,如上面的:DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
    将变量var_done的值置为TURE,循环中就可以通过var_done的值控制循环的退出:LEAVE select_loop;。
    如果当前行有数据,则将当前行数据存到对应的变量中,并将游标指针指向下一行数据,如下语句:FETCH cur_test INTO var_studentId,var_score;

    总结

    1、游标用来对查询结果进行遍历处理。
    2、游标的使用过程:声明游标、打开游标、遍历游标、关闭游标。
    3、游标主要用在循环处理、存储过程、函数中使用,用来查询结果集。
    4、游标的缺点是只能一行一行操作,在数据量大的情况下,是不适用的,速度过慢。数据库大部分是面对集合的,业务会比较复杂,而游标使用会有死锁,影响其他的业务操作,不可取。 当数据量大时,使用游标会造成内存不足现象。

    以上就是全面剖析MySQL游标的详细内容,更多关于MySQL游标的资料请关注脚本之家其它相关文章!

    您可能感兴趣的文章:
    • MySQL 游标的定义与使用方式
    • Mysql 存储过程中使用游标循环读取临时表
    • mysql声明游标的方法
    • 详解Mysql 游标的用法及其作用
    • mysql游标的原理与用法实例分析
    • 带你彻底搞懂python操作mysql数据库(cursor游标讲解)
    • mysql存储过程之游标(DECLARE)原理与用法详解
    • MySQL游标概念与用法详解
    • mysql的存储过程、游标 、事务实例详解
    • Mysql存储过程中游标的用法实例
    • Mysql存储过程循环内嵌套使用游标示例代码
    • MySQL存储过程中游标循环的跳出和继续操作示例
    上一篇:mysql8.0.23 linux(centos7)安装完整超详细教程
    下一篇:MySQL 触发器的使用及需要注意的地方
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MySQL 游标的作用与使用相关 MySQL,游,标的,作用,与,使用,