MySQL通过游标来实现通过查询结果集循环

最新推荐文章于 2025-07-01 18:01:50 发布

原创最新推荐文章于 2025-07-01 18:01:50 发布 · 3.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #函数

数据库专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个使用MySQL存储过程更新数据库记录的具体实例。该存储过程通过游标遍历指定表中的所有记录，并从另一个表中收集相关联的数据，然后更新主表中的字段。此外，还提供了一个将表中的ID更新为UUID格式的存储过程示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

学习
借鉴 http://www.cnblogs.com/silentdoer/p/6274576.html

drop procedure if exists lopp_health; 
create procedure lopp_health()
BEGIN

/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
declare done int default 0;  /*用于判断是否结束循环*/
declare solarNames VARCHAR(1000); /*用于存储结果集S_S的记录（因为我这里S_S的记录只有一列且为bigint类型）*/

/*定义游标*/
declare solarCur cursor for select solar_term from health;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
declare continue handler for not FOUND set done = 1; /*done = true;亦可*/

open solarCur;

REPEAT
FETCH solarCur into solarNames;
if not done THEN

update health 
set staple_ingredients 
= (select group_concat(staple_ingredients) from health_copy where solar_term = solarNames and staple_ingredients <> ''),
dishes_ingredients 
= (select group_concat(dishes_ingredients) from health_copy where solar_term = solarNames and dishes_ingredients <> ''),
breakfast_ingredients 
= (select group_concat(breakfast_ingredients) from health_copy where solar_term = solarNames and breakfast_ingredients <> ''),
tea_ingredients 
= (select group_concat(tea_ingredients) from health_copy where solar_term = solarNames and tea_ingredients <> ''),
disable_ingredients 
= (select group_concat(disable_ingredients) from health_copy where solar_term = solarNames and disable_ingredients <> '')
where solar_term = solarNames;

end if;
until done END repeat;

close solarCur;
END

/** 更新 id 为 UUID **/
drop procedure if exists update_id_uuid; 
create procedure update_id_uuid()
BEGIN

/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
declare done int default 0;  /*用于判断是否结束循环*/
DECLARE my_uuid char(36);   
DECLARE my_uuid_ char(36); 
/*定义游标*/
declare idCur cursor for select id from health;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
declare continue handler for not FOUND set done = 1; /*done = true;亦可*/

open idCur;
REPEAT
FETCH idCur into my_uuid;
if not done THEN
set my_uuid_ = (select replace(my_uuid,'-',''));
update health set id=my_uuid_ where id = my_uuid;

end if;
until done END repeat;
close idCur;
END

/* 执行 */
call lopp_health();