MySQL存储过程之循环遍历查询的结果集详解

目录

• 前言
• 1. 表结构
• 2. 存储过程
• 3. 关于存储过程的SQL补充
• 总结

前言

近来碰到这样一个问题：在生产上导入的数据发现会员的相册数量统计结果与相册中实际的数量不一致的问题。

解决这个问题有两种办法：

• 1：使用程序修正数量不一致的问题
• 2：使用mysql的存储过程

若使用第一种办法的话，需要重新发布版本，比较麻烦，再加上领导对发布版本有些抵触，我觉得我们还是使用第二种方式比较快捷。

1. 表结构

测试表结构如下：

CREATE TABLE `member_album` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '数据ID',
  `member_id` int(11) DEFAULT NULL COMMENT '会员ID',
  `file_type` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件类型(image：照片；video：视频)',
  `f编程客栈ileTWgYKFnkZ_id` int(11) DEFAULT NULL COMMENT '文件ID',
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件地址(相对地址)',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标识(0：正常；1：已删除)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员相册';

CREATE TABLE `member_album_count` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '数据ID',
  `member_id` int(11) DEFAULT NULL Chttp://www.devze.comOMMENT '会员ID',
  `img_pass_count` int(11) DEFAULT '0' COMMENT '照片通过的数量',
  `img_verify_count` int(11) DEFAULT '0' COMMENT '照片审核中的数量',
  `img_fail_count` int(11) DEFAULT '0' COMMENT '照片未通过的数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员照片数量表';

测试表数据如下：

会员相册表：

会员相册数量表：

很明显，会员相册数量表中的数据是不对的，例如会员ID为10024的照片有3张，而在会员相册数量表中显示的是0张。

2. 存储过程

-- 建立存储过程之前需要判断该存储过程是否存在，若存在则删除
DROP PROCEDURE IF EXISTS update_al编程客栈bum_count;
-- 创建存储过程，update_album_count为存储过程名
CREATE PROCEDURE update_album_count()
-- 标识存储过程开始
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE memberId int;
	DECLARE count int;
	-- 定义游标，并将sql结果集赋值到游标中，report为游标名
	DECLARE report CURSOR FOR SELECT member_id, COUNT(member_id)
							  FROM member_album 
							  GROUP BY member_id
							  HAVING COUNT(member_id) > 0
							  ORDER BY member_id ASC;
	-- 声明当游标遍历完后将标志变量置为某个值
	DECLARE CONTINUE HANDLER FOR NOT编程客栈 FOUND SET s = 1;
	-- 打开游标
	OPEN report;
	-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
	FETCH report INTO memberId, count;
	-- 当s不等于1时，也就是未遍历完时，会一直循环
	WHILE s <> 1 DO
	-- 执行业务逻辑
	UPDATE member_album_count t SET t.img_pass_count = count WHERE t.member_id = memberId;
	-- 当s等于1时代表遍历已完成，退出循环
	FETCH report INTO memberId, count;
	END WHILE;
	-- 关闭游标
	CLOSE report;
-- 标识存储过程结束
END;

执行存储过程：

CALL update_album_count();

此时再来看会员相册数量表数据：

已经正常了！！！

3. 关于存储过程的SQL补充

-- 显示存储过程的状态
show procedure status;
-- 查询指定数据库的存储过程名称
select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。