MySQL游标

长安不见使人愁

发布于 2022-11-01 15:34:35

2.7K0

发布于 2022-11-01 15:34:35

文章被收录于专栏：给时光以生命

1、定义

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。游标也是一种面向过程的sql编程方法，所以一般在存储过程、函数、触发器、循环处理中使用。

2、作用

select a, b from table;

这个查询返回了table中的数据，如果我们想对这些数据进行遍历处理，此时我们就可以使用游标来进行操作。游标相当于一个指针，这个指针指向select的第一行数据，可以通过移动指针来遍历后面的数据。

3、属性

在mysql中，游标可以在存储过程、函数、触发器和事件中使用。游标需要与相关handler一起使用，并在handler之前定义。游标有以下三个属性：

Asensitive: 数据库也可以选择不复制结果集
Read only: 不可更新，只读
Nonscrollable: 游标只能向一个方向前进，并且不可以跳过任何一行数据。

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

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

4、语法

# 声明游标
# 游标可以声明多个，但一个begin end中只能声明一个游标。
declare 游标名称 cursor for 查询语句;

# 打开游标
open 游标名称;

# 遍历游标
# 取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。
# 当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的 NOT FOUND错误
fetch 游标名称 into 变量列表;

# 关闭游标
# 游标使用完毕之后一定要关闭。
close 游标名称;

# 条件处理
# 这段代码的作用是定义一个 contine handler，这个的作用是当表达式 1 的条件出现时，将执行表达式 2 的语句。
# 例如 declare continue handler for not found 表达式2 ，实质是利用mysql的异常处理，常常在游标上使用，来辅助判断游标数据是否遍历完了。
declare contine handler 表达式1 set 表达式2;

5、实例

5.1、业务描述

表1 person

表2 person2

编写存储过程p1，向person表生成100000条随机数据；再编写存储过程p2，使用游标遍历person表，将其中city='西安’的记录插入person2表，并且把gender字段由数字（0/1）转换为具体性别（女/男）。

5.2、存过实现

生成数据

create
    definer = root@localhost procedure p1()
begin
    declare randidnum varchar(18);
    declare c int default 0;
    declare tmp_gender varchar(1) default 0;
    declare tmp_city varchar(255) default '';
    declare tmp_city_code int;
    declare tmp_name varchar(255) default '';

    loop_label:
    loop
        set c = c + 1;

        if c > 100000
        then
            leave loop_label;
        end if;
        set tmp_city_code = rand() * 100;

        if tmp_city_code >= 0 && tmp_city_code <= 40 then
            set tmp_city = '武汉';
        elseif tmp_city_code > 40 && tmp_city_code <= 80 then
            set tmp_city = '西安';
        elseif tmp_city_code > 80 && tmp_city_code <= 100 then
            set tmp_city = '成都';
        end if;

        if (c mod 2) = 1 then
            set tmp_gender = '1';
        else
            set tmp_gender = '0';
        end if;

        set randidnum = concat('422823', left(rand() * 100000000000000, 12));

        set tmp_name = concat('测试人员', c);

        insert into `person`(`id_number`, `name`, `gender`, `city`)
        values (randidnum, tmp_name, tmp_gender, tmp_city);
    end loop;
end;

使用游标处理数据

create
    definer = root@localhost procedure p2()
begin
    declare c1 int;
    declare c2 varchar(18);
    declare c3 varchar(255);
    declare c4 varchar(10);
    declare c5 varchar(255);
    declare count int default 0;

    declare personCursor cursor for select * from person;
    open personCursor;

    set count = (select count(id_number) from person);

    loop_label:
    loop
        if count <= 0 then
            leave loop_label;
        end if;

        set count = count - 1;

        fetch personCursor into c1,c2,c3,c4,c5;

        if c5 = '西安' then
            if c4 = '0' then
                set c4 = '男';
            else
                set c4 = '女';
            end if;

            insert into person2(id, id_number, name, gender, city) VALUES (c1, c2, c3, c4, c5);
        end if;
    end loop;

    close personCursor;
end;

*插入数据前可以先删除索引以提升速度，完成插入后再添加索引。

Q.E.D.

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法