二十五、MySQL基础系列笔记之详记游标的使用

什么是游标

什么游标？MySQL官方定义：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

官方的定义还是有些难懂的。用开发的白话来举例，如当前我们使用PHP取出一个MySQL数据结果集时，会使用mysqlifetcharray()函数并结合循环将数据集中的数据一条一条的取出来。同理，MySQL游标的操作与此非常非常相似，都是遍历并从结果集中一条一条的取出数据。

游标特性

游标具有三个特性，如下：

• 不敏感（Asensitive）：数据库可以选择不复制结果集
• 只读（Read Only）
• 不滚动（Nonscrollable）：游标只能向一个方向前进且不可以跳过任何一行数据

游标优点

1、允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作；

2、提供对基于游标位置的表中的行进行删除和更新的能力；

3、游标是面向集合与面向行的设计思想之间的一种桥梁。

游标缺点

游标的主要缺点是性能不高。

游标使用场景

游标可以使用在存储过程、函数、触发器、事件中。

定义游标的语法

# 第一步：声明游标
DECLARE 自定义游标名 CURSOR FOR sql语句;
# 第二步：打开游标
OPEN 自定义游标名;
# 第三步：获取sql语句结果集数据
FETCH 自定义游标名 INTO 变量名1,变量2...;
# 第四步：关闭游标
CLOSE 自定义游标;

注意：游标的使用必须严格遵守这四个步骤！

游标案例

准备数据表并写入数据

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_and_email` (`name`(20),`email`(20))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into values (1,'自如初','ziruchu@163.com'),(2,'PHP','php@163.com');

游标案例一 异常之NOT FOUND

DELIMITER //
DROP PROCEDURE IF EXISTS cur01//
CREATE PROCEDURE cur01()
BEGIN
    # 声明变量，用于存储遍历时的数据
    DECLARE sname varchar(20);
    # 声明游标结束标识变量
    DECLARE done INT DEFAULT 1;

    # 第一步：创建游标
    DECLARE getTotal CURSOR FOR SELECT name FROM user;
    # 游标结束时返回值，将done变量设置为0
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=0;
    # 第二步：打开游标
    OPEN getTotal;
    # 循环遍历
    repeat
        # 第三步：取出数据将赋值
        FETCH getTotal INTO sname;
        # 输出数据
        SELECT sname;
    until done=0 end repeat;
    # 第四步：关闭游标
    CLOSE getTotal;
END//

# 调用
CALL cur01()//

注意：游标是定义完了并使用，但是这里有一个问题，那就是会把PHP与php@163.com这一行输出多输出一次，原因就在于异常处理中CONTINUE关键字，当游标结束后，将done设置为0，但由于是continue关键字，因此会继续执行下面的语句，由于已经为空了，上一次遍历的值还在，因此多输出了最后一行一次。

多输出解决之道就是将CONTINUE改为EXIT。

DELIMITER //
DROP PROCEDURE IF EXISTS cur01//
CREATE PROCEDURE cur01()
BEGIN
    DECLARE sname varchar(20);
    DECLARE done INT DEFAULT 1;

    DECLARE getTotal CURSOR FOR SELECT name FROM user;
    DECLARE EXIT HANDLER FOR NOT FOUND SET done=0;
    OPEN getTotal;
    repeat
        FETCH getTotal INTO sname;
        SELECT sname;
    until done=0 end repeat;

    CLOSE getTotal;
END//

游标案例二 异常之SQLSTATE

DELIMITER //
DROP PROCEDURE IF EXISTS cur01//
CREATE PROCEDURE cur01()
BEGIN
    DECLARE sname varchar(20);
    DECLARE done INT DEFAULT 1;

    DECLARE getTotal CURSOR FOR SELECT name FROM user where id > 30;
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET done=0;
    OPEN getTotal;
    repeat
        FETCH getTotal INTO sname;
        SELECT sname;
    until done=0 end repeat;

    CLOSE getTotal;
END//

游标案例三 CONTINUE正确逻辑输出

若是游标第一次取出来的数据为空，会怎么样？那结果怎么也取不出来数据，结果为null。

DELIMITER //
DROP PROCEDURE IF EXISTS cur01//
CREATE PROCEDURE cur01()
BEGIN
    DECLARE sname varchar(20);
    DECLARE done INT DEFAULT 1;

    DECLARE getTotal CURSOR FOR SELECT name FROM user;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=0;
    OPEN getTotal;
    # 打开游标后，首先取出一次结果
    FETCH getTotal INTO sname;
    repeat
        # 取出数据
        SELECT sname;
        FETCH getTotal INTO sname;
    until done=0 end repeat;

    CLOSE getTotal;
END//

说明：若碰巧就是没有取出数据，由于循环外已经取出了一次数据，而repeat循环无论如何都会执行一次，因此select sname会输出一行为null的结果。发现没有数据会直接触发NOT FOUND异常。

我是小白，期待和优秀的你一起同行!

小白

2020年11月20日