一、MySQL游标的概念
1、游标介绍
MySQL的游标(cursor)是一个重要的概念,通过查找资料与自己的理解,主要得出以下几点关于自己的理解。
- 有数据缓冲的思想:游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果。
- 先有数据基础:游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
- 类似于指针:游标类似于指向数据结构堆栈中的指针,用来pop出所指向的数据,并且只能每次取一个。
2、游标优缺点:
(1)游标的优点:
- 因为游标是针对行操作的,所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作,是一种分离的思想。可以满足对某个结果行进行特殊的操作。
- 游标与基于游标位置的增删改查能力。
- MySQL数据库中没有专门描述一行的表达形式,但这是需要的,所以,个人理解的话,我觉得游标是在关系数据库这种面向集合的系统中抽离出来,单独针对行进行表达(也可以理解成网上资料说的:游标是面向集合与面向行的设计思想之间的一种桥梁)
(2)游标缺点
- 游标的缺点是针对有点而言的,也就是只能一行一行操作,在数据量大的情况下,是不适用的,速度过慢。这里有个比喻就是:当你去ATM存钱是希望一次性存完呢,还是100一张一张的存,这里的100一张一张存就是游标针对行的操作。
- 数据库大部分是面对集合的,业务会比较复杂,而游标使用会有死锁,影响其他的业务操作,不可取。
- 当数据量大时,使用游标会造成内存不足现象。
3、游标的使用场景
针对游标的优缺点,我总结游标的使用场景,主要用在循环处理、存储过程、函数中使用,用来查询结果集,就比如:我们需要从表中循环判断并得到想要的结果集,这时候使用游标操作很方便速度也很快。
二、游标的使用
1、游标的使用步骤
游标的使用一般分为5个步骤,主要是:定义游标->打开游标->使用游标->关闭游标->释放游标。
(1).定义游标
语法为:
DECLARE <游标名> CURSOR FOR select语句;
具体例子:
-- 这样游标就对select语句声明了一个游标
DECLARE mycursor CURSOR FOR select * from shops_info;
(2).打开游标
打开游标很简单就是下面的语句
open <游标名>
(3).使用游标
使用游标需要用关键字fetch来取出数据,然后取出的数据需要有存放的地方,我们需要用declare声明变量存放列的数据其语法格式为:
declare 变量1 数据类型(与列值的数据类型相同)
declare 变量2 数据类型(与列值的数据类型相同)
declare 变量3 数据类型(与列值的数据类型相同)
FETCH [NEXT | PRIOR | FIRST | LAST] FROM <游标名> [ INTO 变量名1,变量名2,变量名3[,…] ]
NEXT:取下一行的数据,游标一开始默认的第一行之前,故要让游标指向第一行,就必须第一次就执行FETCH NEXT操作
INTO:将一行中每个对应的列下的数据放到与列 的数据类型相同的变量中。
-- 声明四个变量
declare id varchar(20);
declare pname varchar(20);
declare pprice varchar(20);
declare pdescription varchar(20);
-- 1、定义一个游标mycursor
declare mycursor cursor for
select *from shops_info;
-- 2、打开游标
open mycursor;
-- 3、使用游标获取列数据放入变量中
fetch next from mycursor into id,pname,pprice,pdescription;
(4).关闭游标:
很简单的使用语句:
close mycursor;
(5).释放游标
deallocate mycursor;
2、具体实例
1、未使用循环的游标实例
从表shops_info中使用游标对每一行进行fetch
代码如下:
drop procedure if exists cursor_test;
delimiter //
create procedure cursor_test()
begin
-- 声明与列的类型相同的四个变量
declare id varchar(20);
declare pname varchar(20);
declare pprice varchar(20);
declare pdescription varchar(20);
-- 1、定义一个游标mycursor
declare mycursor cursor for
select *from shops_info;
-- 2、打开游标
open mycursor;
-- 3、使用游标获取列的值
fetch next from mycursor into id,pname,pprice,pdescription;
-- 4、显示结果
select id,pname,pprice,pdescription;
-- 5、关闭游标
close mycursor;
end;
//
delimiter ;
call cursor_test();
结果如下:
未使用循环得到全部的行记录,值取到第一个fetch next
指向的第一行记录。
2、使用循环的游标
(1)使用repeat循环读取行数据,操作游标
drop procedure if exists cursor_test;
delimiter //
create procedure cursor_test()
begin
-- 声明与列的类型相同的四个变量
declare id varchar(20);
declare pname varchar(20);
declare pprice varchar(20);
declare pdescription varchar(20);
declare temp varchar(20);
declare i int default 0; -- 声明计数i初始值为0;
-- 1、定义一个游标mycursor
declare mycursor cursor for
select *from shops_info;
-- 获得总的记录数
set @cur_count=(select count(*) from shops_info);
-- 2、打开游标
open mycursor;
-- 3、使用游标获取列的值:取出第一行记录
-- 循环读数据
repeat
fetch next from mycursor into id,pname,pprice,pdescription;
-- 4、显示结果
select id,pname,pprice,pdescription;
set i=i+1;
until i=@cur_count
end repeat;
-- 5、关闭游标
close mycursor;
end;
//
delimiter ;
call cursor_test();
如下所示得到表shops_info的三行记录
(2)使用while操作游标循环读取行记录
要配合使用continue handler来识别游标一个越界标识,该语法为:
-- 当发生NOT FOUND事件时就会执行statement语句
declare continue handler for NOT FOUND statement
-- 或者SQLSTATE='0200'
declare CONTINUE HANDLER FOR SQLSTATE '02000' statement
具体实例代码为:
drop procedure if exists cursor_test;
delimiter //
create procedure cursor_test()
begin
-- 声明与列的类型相同的四个变量
declare id varchar(20);
declare pname varchar(20);
declare pprice varchar(20);
declare pdescription varchar(20);
declare done varchar(20) default '';
-- 1、定义一个游标mycursor
declare mycursor cursor for
select *from shops_info;
-- 声明一个越界标识done:continue当not found之后,还会继续执行一次select语句
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = null;
-- 2、打开游标
open mycursor;
-- 3、使用游标获取列的值:取出第一行记录
fetch next from mycursor into id,pname,pprice,pdescription;
-- -- 循环读数据
while ( done is not null) do
select id,pname,pprice,pdescription;
fetch next from mycursor into id,pname,pprice,pdescription;
-- 4、显示结果
end while;
-- 5、关闭游标
close mycursor;
end;
//
delimiter ;
call cursor_test();
结果如下: