MySQL 游标的作用与使用相关

2023-07-06 10:36:03 246

定义

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

游标的作用

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

selectstudentid,studentname,scorefromstudents;

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

游标的使用

声明游标：创建一个游标，并指定这个游标需要遍历的select查询，声明游标时并不会去执行这个sql。
打开游标：打开游标的时候，会执行游标对应的select语句。
遍历数据：使用游标循环遍历select结果中每一行数据，然后进行处理。
业务操作：对遍历到的每行数据进行操作的过程，可以放置任何需要执行的执行的语句(增删改查):这里视具体情况而定
关闭游标：游标使用完之后一定要释放。

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

声明游标

DECLAREcursor_nameCURSORFORselect_statement;

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

打开游标

OPENcursor_name;

打开先前声明的游标。

遍历游标数据

FETCHcursor_nameINTOvar_list;

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

关闭游标

CLOSEcursor_name;

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

游标举例

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

数据基础

mysql>select*fromstudents;
+-----------+-------------+-------+---------+
|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|
+-----------+-------------+-------+---------+
13rowsinset

编写包含游标的函数

这边注释很清晰

mysql>
/*判断函数如果存在则删除*/
DROPFUNCTIONIFEXISTSfun_test;
/*声明结束符为$*/
DELIMITER$
/*创建函数，对符合条件的每个同学的分数进行加分，加的分数不能超过给定的值max_score*/
CREATEFUNCTIONfun_test(max_scoredecimal(10,2))
RETURNSint
BEGIN
/*定义实时StudentId的变量*/
DECLAREvar_studentIdintDEFAULT0;
/*定义计算后分数的变量*/
DECLAREvar_scoredecimal(10,2)DEFAULT0;
/*定义游标结束标志变量*/
DECLAREvar_doneintDEFAULTFALSE;
/*创建游标*/
DECLAREcur_testCURSORFORSELECTstudentid,scorefromstudentswhereclassid<>0;
/*游标结束时会设置var_done为true，后续可以使用var_done来判断游标是否结束*/
DECLARECONTINUEHANDLERFORNOTFOUNDSETvar_done=TRUE;
/*打开游标*/
OPENcur_test;
/*使用Loop循环遍历游标*/
select_loop:LOOP
/*先获取当前行的数据，然后将当前行的数据放入var_studentId,var_score中，如果无数据行了，var_done会被置为true*/
FETCHcur_testINTOvar_studentId,var_score;
/*通过var_done来判断游标是否结束了，退出循环*/
IFvar_doneTHEN
LEAVEselect_loop;
ENDIF;
/*对var_score值添加随机值，不能超过给定的分数*/
setvar_score=var_score+LEAST(ROUND(rand()*10,0),max_score);
updatestudentssetscore=var_scorewherestudentId=var_studentId;
ENDLOOP;
/*关闭游标*/
CLOSEcur_test;
/*返回结果:可以根据实际情况返回需要的内容*/
RETURN1;
END$
/*结束符置为;*/
DELIMITER;
QueryOK,0rowsaffected

调用函数

mysql>
/*参数为8，表示加分上限为8*/
selectfun_test(8);
+-------------+
|fun_test(8)|
+-------------+
|1|
+-------------+
1rowinset

查看结果

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

mysql>select*fromstudents;
+-----------+-------------+-------+---------+
|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|
+-----------+-------------+-------+---------+
13rowsinset

查看触发器日志

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

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

游标的执行过程

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

总结

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

以上就是全面剖析MySQL游标的详细内容，更多关于MySQL游标的资料请关注毛票票其它相关文章！