mysql 存储过程 同步数据_mysql 存储过程 实现数据同步

数据库 表 发生变化，需要把2.0的表数据 同步到3.0库中去；

-- 同步数据存储过程执行

-- 更新留言旧表数据到新表数据中

/*DEFINER：Vector*/

drop procedure if exists p_for_feedback_message;

create procedure p_for_feedback_message()

begin

DECLARE v_done INT DEFAULT 0;

DECLARE v_id INT DEFAULT 0;

DECLARE v_feedback VARCHAR(50);

DECLARE v_user_phone VARCHAR(50);

DECLARE v_system_time VARCHAR(50);

DECLARE v_status VARCHAR(50);

DECLARE v_requDatas CURSOR FOR (SELECT f.id as id,f.feedback as feedback,f.user_phone as user_phone,f.system_time as system_time,f.`status` as status FROM feedback_message as f ORDER BY system_time DESC);

/*游标异常后捕捉并设置变量，done 为 1 跳出循环*/

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

START TRANSACTION;

/*打开游标*/

OPEN v_requDatas;

out_loop:

LOOP

/*游标向下走一步，将查询出来的值付给定义的变量*/

FETCH v_requDatas into v_id,v_feedback,v_user_phone,v_system_time,v_status;

IF v_done = 1 THEN

LEAVE out_loop;

END IF;

BEGIN

DECLARE v_user_id VARCHAR(50);

SELECT user_id into v_user_id FROM user_info WHERE user_phone = v_user_phone LIMIT 0,1 ;

INSERT INTO feedback_message2 (id,user_id,feedback,system_time,`status`)VALUES(v_id,v_user_id,v_feedback,v_system_time,v_status);

if(v_user_phone is not null) then

select v_user_phone,v_user_id;

end if;

END;

SET v_done = 0;

END LOOP out_loop;

/*关闭游标*/

CLOSE v_requDatas;

/*事务提交*/

COMMIT;

END;