友情目录
1.具体需求
num
= 1
ids
=(sourceSql: select id from table)
for id in ids
{
targetSql: update table2 set age = num
where key_id = id;
};
num
++;
这是大概逻辑,主要是通过一段sql查出数据源,然后再遍历查出来的集合做一些其他sql操作,
中间还需要一些自增的变量。
🆗,接下来就是具体sql实现,我们是通过mysql的存储过程实现
2.代码实现
-- 是否存在名字为exam_paper_media_update的存储过程,存在则删除DROP PROCEDURE IF EXISTS exam_paper_media_update;-- 创建存储过程CREATE PROCEDURE exam_paper_media_update () BEGIN-- 该变量用于标识是否还有数据需遍历DECLARE flag INT DEFAULT 0;-- 声明一个变量用来存储遍历过程中的值DECLARE paperId BIGINT ( 40 );-- 查询出需要遍历的数据集合DECLARE idList CURSOR FOR ( SELECT id FROM exam_student_paper WHERE exam_id = 123 AND del_flag = 0 ORDER BY id);-- 为下面while循环建立一个退出标志,当游标遍历完后将flag的值设置为1DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;-- 打开游标OPEN idList;-- 取值设置到临时变量中FETCH idList INTO paperId;-- 遍历未结束就一直执行SET @num = 1;WHILE flag != 1 DO-- 你想要执行的目标功能,这里可以写多个SQLUPDATE paper_media SET media_url = CONCAT('exam-media-test-bucket/standard-video-fuzzy/',@num, '_TOP.mp4' ) WHERE paper_id = paperId AND tag = 'TOP';UPDATE paper_media SET media_url = CONCAT('exam-media-test-bucket/standard-video-fuzzy/',@num, '_SIDE.mp4') WHERE paper_id = paperId AND tag = 'SIDE';UPDATE paper_media SET media_url = CONCAT('exam-media-test-bucket/standard-video-fuzzy/',@num, '_FRONT.mp4') WHERE paper_id = paperId AND tag = 'LOWER';UPDATE paper_media SET media_url = CONCAT('exam-media-test-bucket/standard-video-fuzzy/',@num, '_FRONT.mp4') WHERE paper_id = paperId AND tag = 'UPPER';SET @num = @num + 1;-- 游标后移一位FETCH idList INTO paperId;END WHILE;-- 关闭游标CLOSE idList;END;-- 执行存储过程CALL exam_paper_media_update ();
3.文章参考
Mysql实现for循环遍历
mysql循环批量建表(表结构相同,表名可以自定义)
来源地址:https://blog.csdn.net/notfound4043/article/details/129175259