一、游标的特点和限制
特点:
只读:只能读取数据,不能通过游标更新数据
单向:只能从前向后顺序读取,不能回滚或跳跃
敏感:游标指向实际数据,其他连接对数据的修改会影响游标结果
临时:游标只在存储过程或函数中有效
限制:
只能在存储过程或函数中使用
不支持滚动(只能向前移动)
性能开销较大,应谨慎使用
二、游标的使用步骤
1. 声明游标 → 2. 打开游标 → 3. 读取数据 → 4. 关闭游标
三、声明游标 (DECLARE CURSOR)
语法:
DECLARE cursor_name CURSOR FOR select_statement;
示例:
DELIMITER //
CREATE PROCEDURE process_students()
BEGIN
声明游标
DECLARE student_cursor CURSOR FOR
SELECT id, name, score FROM tb_student WHERE score > 60;
其他变量和逻辑...
END //
DELIMITER ;
四、打开游标 (OPEN)
语法:
OPEN cursor_name;
注意: 打开游标时,游标并不指向第一条记录,而是在第一条记录之前的位置。
五、读取游标 (FETCH)
语法:
FETCH cursor_name INTO var1, var2, ...;
重要: 必须事先声明与查询结果列对应的变量来接收数据。
六、关闭游标 (CLOSE)
语法:
CLOSE cursor_name;
最佳实践: 显式关闭游标以释放资源,不要依赖 MySQL 的自动关闭。
七、游标与异常处理
通常需要定义 `NOT FOUND` 处理程序来检测游标是否遍历完成。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
八、完整示例
示例1:基本游标使用(遍历所有学生)
DELIMITER //
CREATE PROCEDURE display_all_students()
BEGIN
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE v_score DECIMAL(5,2);
DECLARE done INT DEFAULT 0;
1. 声明游标
DECLARE student_cursor CURSOR FOR
SELECT id, name, score FROM tb_student;
2. 定义异常处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
3. 打开游标
OPEN student_cursor;
4. 循环读取数据
read_loop: LOOP
FETCH student_cursor INTO v_id, v_name, v_score;
IF done THEN
LEAVE read_loop;
END IF;
处理每行数据(这里只是示例)
SELECT CONCAT('ID:', v_id, ' Name:', v_name, ' Score:', v_score) AS student_info;
END LOOP;
5. 关闭游标
CLOSE student_cursor;
END //
DELIMITER ;
示例2:使用 WHILE 循环的游标
DELIMITER //
CREATE PROCEDURE update_low_scores()
BEGIN
DECLARE v_id INT;
DECLARE v_score DECIMAL(5,2);
DECLARE done INT DEFAULT 0;
DECLARE score_cursor CURSOR FOR
SELECT id, score FROM tb_student WHERE score < 60;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN score_cursor;
FETCH score_cursor INTO v_id, v_score;
WHILE done = 0 DO
更新低分学生
UPDATE tb_student
SET score = score + 10
WHERE id = v_id;
FETCH score_cursor INTO v_id, v_score;
END WHILE;
CLOSE score_cursor;
END //
DELIMITER ;
示例3:使用 REPEAT 循环的游标
DELIMITER //
CREATE PROCEDURE process_user_data(out result TEXT)
BEGIN
DECLARE v_name VARCHAR(60);
DECLARE v_pass VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE user_cursor CURSOR FOR
SELECT user_name, user_pass FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET result = '';
OPEN user_cursor;
REPEAT
FETCH user_cursor INTO v_name, v_pass;
IF NOT done THEN
SET result = CONCAT_WS(';', result, CONCAT(v_name, ':', v_pass));
END IF;
UNTIL done END REPEAT;
CLOSE user_cursor;
END //
DELIMITER ;
九、游标使用的最佳实践
1. 尽量减少游标使用:基于集合的操作通常比游标更高效
2. 尽快关闭游标:减少资源占用时间
3. 使用合适的循环结构:
`LOOP` + `LEAVE`: 灵活控制
`WHILE`: 先判断后执行
`REPEAT`: 先执行后判断
4. 批量处理:如果可能,尽量使用批量操作而不是逐行处理
5. 错误处理:始终包含适当的异常处理
十、游标的性能考虑
游标的缺点:
增加服务器开销
占用锁和内存资源
通常比基于集合的操作慢
替代方案考虑:
使用 `UPDATE ... WHERE ...` 代替逐行更新
使用临时表处理复杂逻辑
应用层处理分页和数据遍历
十一、嵌套游标
MySQL 支持嵌套游标,但需要谨慎使用:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_inner = 1, done_outer = 1;
OPEN outer_cursor;
FETCH outer_cursor INTO ...;
WHILE done_outer = 0 DO
OPEN inner_cursor;
FETCH inner_cursor INTO ...;
WHILE done_inner = 0 DO
处理逻辑
FETCH inner_cursor INTO ...;
END WHILE;
CLOSE inner_cursor;
SET done_inner = 0;
FETCH outer_cursor INTO ...;
END WHILE;
CLOSE outer_cursor;
C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:
一点编程也不会写的:零基础C语言学练课程
解决困扰你多年的C语言疑难杂症特性的C语言进阶课程
从零到写出一个爬虫的Python编程课程
只会语法写不出代码?手把手带你写100个编程真题的编程百练课程
信息学奥赛或C++选手的 必学C++课程
蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程
手把手讲解近五年真题的蓝桥杯辅导课程