快捷導(dǎo)航

MySQL存儲(chǔ)過程之循環(huán)遍歷查詢的結(jié)果集詳解

更新時(shí)間：2025年06月12日 14:33:14 作者：xiaomu_a

這篇文章主要介紹了MySQL存儲(chǔ)過程之循環(huán)遍歷查詢的結(jié)果集,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

前言

近來碰到這樣一個(gè)問題：在生產(chǎn)上導(dǎo)入的數(shù)據(jù)發(fā)現(xiàn)會(huì)員的相冊(cè)數(shù)量統(tǒng)計(jì)結(jié)果與相冊(cè)中實(shí)際的數(shù)量不一致的問題。

解決這個(gè)問題有兩種辦法：

1：使用程序修正數(shù)量不一致的問題
2：使用MySQL的存儲(chǔ)過程

若使用第一種辦法的話，需要重新發(fā)布版本，比較麻煩，再加上領(lǐng)導(dǎo)對(duì)發(fā)布版本有些抵觸，我覺得我們還是使用第二種方式比較快捷。

1. 表結(jié)構(gòu)

測(cè)試表結(jié)構(gòu)如下：

CREATE TABLE `member_album` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '數(shù)據(jù)ID',
  `member_id` int(11) DEFAULT NULL COMMENT '會(huì)員ID',
  `file_type` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件類型(image：照片；video：視頻)',
  `file_id` int(11) DEFAULT NULL COMMENT '文件ID',
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件地址(相對(duì)地址)',
  `create_date` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `del_flag` tinyint(1) DEFAULT '0' COMMENT '刪除標(biāo)識(shí)(0：正常；1：已刪除)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會(huì)員相冊(cè)';

CREATE TABLE `member_album_count` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '數(shù)據(jù)ID',
  `member_id` int(11) DEFAULT NULL COMMENT '會(huì)員ID',
  `img_pass_count` int(11) DEFAULT '0' COMMENT '照片通過的數(shù)量',
  `img_verify_count` int(11) DEFAULT '0' COMMENT '照片審核中的數(shù)量',
  `img_fail_count` int(11) DEFAULT '0' COMMENT '照片未通過的數(shù)量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會(huì)員照片數(shù)量表';

測(cè)試表數(shù)據(jù)如下：

會(huì)員相冊(cè)表：

會(huì)員相冊(cè)數(shù)量表：

很明顯，會(huì)員相冊(cè)數(shù)量表中的數(shù)據(jù)是不對(duì)的，例如會(huì)員ID為10024的照片有3張，而在會(huì)員相冊(cè)數(shù)量表中顯示的是0張。

2. 存儲(chǔ)過程

-- 建立存儲(chǔ)過程之前需要判斷該存儲(chǔ)過程是否存在，若存在則刪除
DROP PROCEDURE IF EXISTS update_album_count;
-- 創(chuàng)建存儲(chǔ)過程，update_album_count為存儲(chǔ)過程名
CREATE PROCEDURE update_album_count()
-- 標(biāo)識(shí)存儲(chǔ)過程開始
BEGIN
	-- 定義變量
	DECLARE s int DEFAULT 0;
	DECLARE memberId int;
	DECLARE count int;
	-- 定義游標(biāo)，并將sql結(jié)果集賦值到游標(biāo)中，report為游標(biāo)名
	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;
	-- 聲明當(dāng)游標(biāo)遍歷完后將標(biāo)志變量置為某個(gè)值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打開游標(biāo)
	OPEN report;
	-- 將游標(biāo)中的值賦值給變量，注意：變量名不要與sql返回的列名相同，變量順序要和sql結(jié)果列的順序一致
	FETCH report INTO memberId, count;
	-- 當(dāng)s不等于1時(shí)，也就是未遍歷完時(shí)，會(huì)一直循環(huán)
	WHILE s <> 1 DO
	-- 執(zhí)行業(yè)務(wù)邏輯
	UPDATE member_album_count t SET t.img_pass_count = count WHERE t.member_id = memberId;
	-- 當(dāng)s等于1時(shí)代表遍歷已完成，退出循環(huán)
	FETCH report INTO memberId, count;
	END WHILE;
	-- 關(guān)閉游標(biāo)
	CLOSE report;
-- 標(biāo)識(shí)存儲(chǔ)過程結(jié)束
END;

執(zhí)行存儲(chǔ)過程：

CALL update_album_count();

此時(shí)再來看會(huì)員相冊(cè)數(shù)量表數(shù)據(jù)：

已經(jīng)正常了?。?！

3. 關(guān)于存儲(chǔ)過程的SQL補(bǔ)充

-- 顯示存儲(chǔ)過程的狀態(tài)
show procedure status;
-- 查詢指定數(shù)據(jù)庫(kù)的存儲(chǔ)過程名稱
select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'