Mysql中对游标cursor的理解与使用

本文理论介绍很大部分来自别人的博客,加上一些《Mysql5.7从入门到精通》里面的说法。

什么是游标

MySQL的游标(cursor)是一个重要的概念,个人觉得游标适合查询到的东西来做二次利用甚至N次利用,据说用游标直接能在mysql写出来kmeans算法。
1.有数据缓冲的思想:游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果。
2.先有数据基础:游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
3.类似于指针:游标类似于指向数据结构堆栈中的指针,用来pop出所指向的数据,并且只能每次取一个。

游标的优缺点

(1)游标的优点:

1.因为游标是针对行操作的,所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作,是一种分离的思想。可以满足对某个结果行进行特殊的操作。
2.游标与基于游标位置的增删改查能力。
3.MySQL数据库中没有专门描述一行的表达形式,但这是需要的,所以,个人理解的话,我觉得游标是在关系数据库这种面向集合的系统中抽离出来,单独针对行进行表达(也可以理解成网上资料说的:游标是面向集合与面向行的设计思想之间的一种桥梁

(2)游标缺点

1.游标的缺点是针对优点而言的,也就是只能一行一行操作,在数据量大的情况下,是不适用的,速度过慢。这里有个比喻就是:当你去ATM存钱是希望一次性存完呢,还是100一张一张的存,这里的100一张一张存就是游标针对行的操作。
2.数据库大部分是面对集合的,业务会比较复杂,而游标使用会有死锁,影响其他的业务操作,不可取。
3.当数据量大时,使用游标会造成内存不足现象。

游标的使用场景

游标很像一个临时的数据表,但只是中间状态,就像a,b互换数字时候的临时变量c一样。
比如现在有一个表要更新到到另一个表,列名不同,中间还要加些判断,但是数据类型相同,直接join或者insert都没有游标用起来顺手。

游标使用

游标的使用一般分为5个步骤,主要是:定义游标->打开游标->使用游标->关闭游标->释放游标。
还有就是mysql中,游标通常和procedure一起使用,这跟数据库后台编程技术息息相关。有时间好好整理一下数据库后台编程相关的知识。
先放一个例子,有改动。例子来源
假如我有两个表,表列名因为版本需求有些变化,建表脚本如下:

CREATE TABLE `stu_info` (
  `stuId` varchar(20) NOT NULL COMMENT '学号',
  `stuName` varchar(20) NOT NULL COMMENT '姓名',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号码',
  `idNumber` varchar(20) DEFAULT NULL COMMENT '身份证号码',
  PRIMARY KEY (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息';

CREATE TABLE `stu_info_bak` (
  `stuid` varchar(20) NOT NULL COMMENT '学号',
  `student` varchar(20) NOT NULL COMMENT '姓名',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号码',
  `idNumber` varchar(20) DEFAULT NULL COMMENT '身份证号码',
  PRIMARY KEY (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息备份';

stu_info表中有几条数据,stu_info_bak中没有数据,现在想要把stu_info中的数据全部备份到stu_info_bak表中。

于是可以利用游标去循环插入,像下面一样

delimiter $$
drop procedure if exists `proc_copy_stu_info` $$
CREATE PROCEDURE `proc_copy_stu_info`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_stuId VARCHAR(20);
    DECLARE v_stuName VARCHAR(20);
    DECLARE v_phone VARCHAR(11);
    DECLARE v_idNumber VARCHAR(20);
    DECLARE cur CURSOR FOR SELECT stuId,stuName,phone,idNumber FROM stu_info;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    OPEN cur;
    read_loop : LOOP
        FETCH cur INTO v_stuId,v_stuName,v_phone,v_idNumber;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql_insert = CONCAT("insert into stu_info_bak(stuid,student,phone,idNumber) VALUES ('",v_stuId,"','",v_stuName,"','",v_phone,"','",v_idNumber,"')");

        PREPARE sqlli FROM @sql_insert;
            EXECUTE  sqlli;
        COMMIT;
    END LOOP read_loop;
    CLOSE cur;
END
$$
delimiter;
call `proc_copy_stu_info`();
commit;

再来说一下上面这个例子:

定义游标

例子中的这一句就是定义了名字为cur的游标,游标一般是接select语句。
DECLARE cur CURSOR FOR SELECT stuId,stuName,phone,idNumber FROM stu_info;
定义语法为:
DECLARE <游标名> CURSOR FOR select语句;

打开游标

OPEN cur;
就上面这一句,没什么要说的。

使用游标

FETCH cur INTO v_stuId,v_stuName,v_phone,v_idNumber;
指针指向了第一个v_stuId,v_stuName,v_phone,v_idNumber上。
FETCH [NEXT | PRIOR | FIRST | LAST] <游标名> INTO 变量名1,变量名2,变量名3,...
[NEXT | PRIOR | FIRST | LAST]这个是可选的,FETCH和INTO是关键字。

关闭游标

close cur;

释放游标

deallocate cur;

至于循环使用,我觉得这应该算是存储过程和函数的内容,以后再说。

发表评论

电子邮件地址不会被公开。