使用Mysql 存储过程和游标进行同步数据

-- UpdateUserData是同步数据需要具体执行的步骤，AsyncUserData是控制UpdateUserData的条件。

DROP PROCEDURE IF EXISTS UpdateUserData;
DROP PROCEDURE IF EXISTS AsyncUserData; 

DELIMITER //
CREATE PROCEDURE `UpdateUserData`(IN id int(11))
BEGIN
　　DECLARE classidstr, gradeidstr, stageidstr,childidstr VARCHAR(500);
　　select CONCAT("[\"", replace(GROUP_CONCAT(DISTINCT class_id), ',', '\",\"'), "\"]") ,
　　CONCAT("[", GROUP_CONCAT(DISTINCT grade_id), "]") ,
　　CONCAT("[", GROUP_CONCAT(DISTINCT stage_id), "]") ,
　　CONCAT("[", GROUP_CONCAT(DISTINCT child_id), "]") into classidstr,gradeidstr, stageidstr, childidstr
　　from tb_child where patriarch_id=id;

 IF childidstr is NULL
 THEN
 update tb_patriarch set class_ids='' , grade_ids ='' , stage_ids='' , child_ids='' where patriarch_id= id;
 ELSE
    update tb_patriarch set class_ids=classidstr , grade_ids =gradeidstr , stage_ids=stageidstr , child_ids=childidstr where patriarch_id= id;
 END IF;
END
//
DELIMITER ;
call UpdateUserData(4594);


DELIMITER //
CREATE PROCEDURE `AsyncUserData`()
BEGIN
 DECLARE id int(11);
 -- 定义遍历数据结束标志
 DECLARE done BOOLEAN DEFAULT 0;
 DECLARE curid CURSOR FOR select patriarch_id as id  from tb_patriarch where child_ids='[0]' ;
 --  当REPEAT由于没有更多的行供循环而不能继续时出现这个条件，将done设置为1，此时REPEAT终止
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN curid;  
    read_loop:LOOP
  FETCH curid into id;
    IF done THEN
        LEAVE read_loop;
    END IF;
    call UpdateUserData(id);
  END LOOP;
  CLOSE curid;
END
//
call AsyncUserData();

动态拼接生成sql语句

DROP PROCEDURE IF EXISTS AsyncReportDataByOrgId; 
DELIMITER //
CREATE PROCEDURE `AsyncReportDataByOrgId`(IN id int(11))
BEGIN
  
    DECLARE orgName, phone ,userName VARCHAR(128);
    DECLARE clientId bigint(20);
    Set @TableReport = CONCAT("orgs.t_recruit_client_record_report_",id);
    Set @TableRecord = CONCAT("orgs.t_recruit_client_record_",id);
    Set @TableClient = CONCAT("orgs.t_org_client_",id);
  -- 查询机构名
    SELECT `name` into orgName FROM common.t_org_info WHERE orgId=id ;
  -- 执行插入

    Set @STMT=concat('insert ignore into sync_evaluation
    (recordId,clientId,reportId,activityId,orgId,orgName,status,topicId,mobilePhone,name,pdf,simplePdf,stickerStatus,startTime,finishTime,answerTime) 
    select *from 
    (select a.recordId ,a.clientId,a.reportId,b.activityId,',id,',\'',orgName,'\' as orgName , b.status, b.topicId,c.mobilePhone,c.name, a.pdf ,a.simplePdf,1 as stickerStatus,b.startTime,b.finishTime,a.answerTime from ',@TableReport,' as a join ',@TableRecord,' as b on a.recordId= b.recordId join ',@TableClient,' as c 
    on a.clientId = c.clientId
    where activityId>0) as datatable' );
    -- 调试语句是否正确
    -- SELECT @STMT;
  PREPARE STMT FROM @STMT;   
  EXECUTE STMT;   
    
END
// 
-- 测试存储过程 
-- call AsyncReportDataByOrgId(2817360);

DROP PROCEDURE IF EXISTS AsyncReportData; 
DELIMITER //
CREATE PROCEDURE `AsyncReportData`()
BEGIN
 -- 定义一个变量来存放机构id
 DECLARE id int(11);
 -- 定义遍历数据结束标志
 DECLARE done BOOLEAN DEFAULT 0;
 DECLARE curid CURSOR FOR select orgId as id From common.t_org_info where `status`=2;
 --  当REPEAT由于没有更多的行供循环而不能继续时出现这个条件，将done设置为1，此时REPEAT终止
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN curid;  
    read_loop:LOOP
  FETCH curid into id;
    IF done THEN
        LEAVE read_loop;
    END IF;
    call AsyncReportDataByOrgId(id);
  END LOOP;
  CLOSE curid;
END
//
call AsyncReportData();

 

update t_conf_service set resultConf = REPLACE(resultConf,'http://aaaa','https://bbb');

 需要注意 GROUP_CONCAT 默认长度是1024 

SET SESSION group_concat_max_len=4294967295 更改当前链接默认长度