mysql数据库存储过程数据迁移案例与比较_mysql数据库存储过程怎么写

cursor：安全,不会造成死锁,可以在服务运行阶段跑,比较稳定.

数据迁移案例：

首先数据的迁移绝对不是一朝一夕能够快速迁移完成的 ,如果可以很快完成的 dump便可以搞定,没必要大费周折了.

既然不是一朝一夕能完成的,那么有关键的日志记录表能够良好的反应数据迁移的过程

迁移日志表脚本：

DROP TABLE IF EXISTS +cx_delete_log+;
CREATE TABLE +cx_delete_log+ (
  +id+ int(30) NOT NULL,
  +table_name+ varchar(30) DEFAULT NULL,
  +start_tm+ datetime DEFAULT NULL,
  +end_tm+ datetime DEFAULT NULL,
  +status+ int(10) DEFAULT NULL,
  +pro_create_time+ datetime DEFAULT NULL,
  +pro_end_time+ datetime DEFAULT NULL,
  PRIMARY KEY (+id+)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of cx_delete_log
-- ----------------------------
INSERT INTO +cx_delete_log+ VALUES ('1', 'cx_waybill', '2017-01-01 00:00:00', '2017-01-01 00:10:00', '1', '2017-03-21 14:06:21', null);


ALTER TABLE +cx_delete_log+
ADD INDEX +index_end_date+ (+end_tm+) USING BTREE ;

我们插入了一条日志数据,有这个来控制每次数据迁移的间隔时间段,与开始时间

存储过程：

日志写入：

   delimiter | 
drop procedure if exists batch_move_cx_waybill_partition_by_minute;
create procedure batch_move_cx_waybill_partition_by_minute()
begin
    DECLARE proNo INTEGER;
    DECLARE startDate TIMESTAMP;
    DECLARE endDate TIMESTAMP;
    DECLARE nextDate TIMESTAMP;
    DECLARE pro_endDate TIMESTAMP;
    SET pro_endDate='2017-03-20 00:00:00';
        SELECT  ID, end_tm,DATE_ADD(end_tm,INTERVAL '00:10:00' hour_second) INTO proNo,startDate ,endDate from  cx_delete_log  ORDER BY end_tm desc limit 1;
    IF  curtime()>'06:00:00' THEN
        SET  startDate =pro_endDate;
    END IF;
    IF startDate <pro_endDate THEN
        INSERT into cx_delete_log(id,table_name,start_tm,end_tm,status,pro_create_time)VALUES(proNo+1,'cx_waybill',startDate,endDate,'0',SYSDATE());
        update cx_delete_log  set pro_end_time =SYSDATE() , +status+=1 where  id=proNo+1;
        COMMIT;
    END IF;
        UNTIL startDate >=pro_endDate
END REPEAT;
end  |
delimiter ;

迁移操作的进行：

实例为cursor,insert..select较为简单,直接带入就行,就不做示例了

   delimiter ||
drop procedure if exists cursor_move_cx_waybill_partition;
create procedure cursor_move_cx_waybill_partition(IN startDate_tmp TIMESTAMP)
begin
    DECLARE isEXist INTEGER;
    DECLARE WAYBILLNO_TMP VARCHAR(32);
    DECLARE RESOURCECODE_TMP VARCHAR(10);
    DECLARE APPOINTMENTDDELIVERYTIME_TMP TIMESTAMP;
    DECLARE APPOINTMENTDDELIVERYENDTIME_TMP TIMESTAMP;
    DECLARE EXPECTEDDELIVERYTIME_TMP TIMESTAMP;
    DECLARE ORIGINATEID_TMP VARCHAR(32);
    DECLARE DESTINATIONID_TMP VARCHAR(32);
    DECLARE WAYBILLSTATUS_TMP VARCHAR(4);
    DECLARE VERSIONNO_TMP INTEGER;
    DECLARE STATUS_TMP CHAR(1);
    DECLARE FAILSTARTUS_TMP VARCHAR(10);
    DECLARE FAILCAUSEDESC_TMP VARCHAR(500);
    DECLARE MEMBERID_TMP VARCHAR(32);
    DECLARE HIDEFLAG_TMP VARCHAR(4);
    DECLARE CREATEUSERID_TMP VARCHAR(32);
    DECLARE CREATETIME_TMP TIMESTAMP;
    DECLARE UPDATEUSERID_TMP VARCHAR(32);
    DECLARE UPDATETIME_TMP TIMESTAMP;
    DECLARE APPOINTMENTNO_TMP VARCHAR(32);
    DECLARE SOURCECITY_TMP VARCHAR(32);
    DECLARE DESTCITY_TMP VARCHAR(32);
    DECLARE SOURCECITYCODE_TMP VARCHAR(20);
    DECLARE DESTCITYCODE_TMP VARCHAR(20);
    DECLARE EMPCODE_TMP VARCHAR(10);
    DECLARE RECEMPCODE_TMP VARCHAR(10);
    DECLARE RECMEMBERID_TMP VARCHAR(32);
    DECLARE WAYBILLFEE_TMP DOUBLE(10,0);
    DECLARE MOBILE_TMP VARCHAR(20);
    DECLARE RECMOBILE_TMP VARCHAR(20);
    DECLARE RECTIME_TMP TIMESTAMP;
    DECLARE ENDTIME_TMP TIMESTAMP;
    DECLARE EXPECTMEMBERID_TMP VARCHAR(32);
    DECLARE ORIGINATE_TMP VARCHAR(500);
    DECLARE DESTINATION_TMP VARCHAR(500);
    DECLARE PAYTYPE_TMP VARCHAR(4);
    DECLARE SECRECYTYPE_TMP VARCHAR(32);
    DECLARE SEND_EVALUATE_TMP VARCHAR(10);
    DECLARE PRODUCT_TYPE_TMP VARCHAR(32);
    DECLARE SERVICES_PROD_CODE_TMP VARCHAR(200);
    DECLARE CHANGERECORD_TMP VARCHAR(32);
    DECLARE IS_ISSUED_PREFRENCE_TMP VARCHAR(10);
    DECLARE REC_EVALUATE_TMP VARCHAR(10);
    DECLARE done INTEGER;



    DECLARE bill_cursor CURSOR for SELECT  WAYBILLNO, RESOURCECODE , APPOINTMENTDDELIVERYTIME , APPOINTMENTDDELIVERYENDTIME , EXPECTEDDELIVERYTIME ,ORIGINATEID , DESTINATIONID , WAYBILLSTATUS , VERSIONNO , STATUS, FAILSTARTUS, FAILCAUSEDESC, MEMBERID, HIDEFLAG, CREATEUSERID, CREATETIME, UPDATEUSERID, UPDATETIME, APPOINTMENTNO, SOURCECITY, DESTCITY, SOURCECITYCODE, DESTCITYCODE, EMPCODE, RECEMPCODE, RECMEMBERID,WAYBILLFEE, MOBILE, RECMOBILE, RECTIME, ENDTIME, EXPECTMEMBERID, ORIGINATE, DESTINATION, PAYTYPE, SECRECYTYPE, SEND_EVALUATE, PRODUCT_TYPE, SERVICES_PROD_CODE, CHANGERECORD, IS_ISSUED_PREFRENCE, REC_EVALUATE FROM cx_waybill WHERE CREATETIME > startDate_tmp AND CREATETIME <= DATE_ADD(startDate_tmp,INTERVAL '00:10:00' hour_second);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    open bill_cursor;
    IF done = 1 THEN
    END IF;
    FETCH bill_cursor INTO WAYBILLNO_TMP, RESOURCECODE_TMP, APPOINTMENTDDELIVERYTIME_TMP, APPOINTMENTDDELIVERYENDTIME_TMP,EXPECTEDDELIVERYTIME_TMP,ORIGINATEID_TMP, DESTINATIONID_TMP, WAYBILLSTATUS_TMP, VERSIONNO_TMP, STATUS_TMP, FAILSTARTUS_TMP, FAILCAUSEDESC_TMP, MEMBERID_TMP, HIDEFLAG_TMP, CREATEUSERID_TMP, CREATETIME_TMP, UPDATEUSERID_TMP, UPDATETIME_TMP, APPOINTMENTNO_TMP, SOURCECITY_TMP, DESTCITY_TMP, SOURCECITYCODE_TMP, DESTCITYCODE_TMP, EMPCODE_TMP, RECEMPCODE_TMP, RECMEMBERID_TMP,WAYBILLFEE_TMP, MOBILE_TMP, RECMOBILE_TMP, RECTIME_TMP, ENDTIME_TMP, EXPECTMEMBERID_TMP, ORIGINATE_TMP, DESTINATION_TMP, PAYTYPE_TMP, SECRECYTYPE_TMP, SEND_EVALUATE_TMP, PRODUCT_TYPE_TMP, SERVICES_PROD_CODE_TMP, CHANGERECORD_TMP, IS_ISSUED_PREFRENCE_TMP, REC_EVALUATE_TMP;
    IF WAYBILLNO_TMP is NULL THEN
    END IF;
    SELECT count(1) INTO isEXist from cx_waybill_partition w where w.WAYBILLNO=WAYBILLNO_TMP;
    IF isEXist >0 THEN
    ELSE
    INSERT  INTO cx_waybill_PARTITION (WAYBILLNO, RESOURCECODE, APPOINTMENTDDELIVERYTIME, APPOINTMENTDDELIVERYENDTIME,EXPECTEDDELIVERYTIME,ORIGINATEID, DESTINATIONID, WAYBILLSTATUS, VERSIONNO, STATUS, FAILSTARTUS, FAILCAUSEDESC, MEMBERID, HIDEFLAG, CREATEUSERID, CREATETIME, UPDATEUSERID, UPDATETIME, APPOINTMENTNO, SOURCECITY, DESTCITY, SOURCECITYCODE, DESTCITYCODE, EMPCODE, RECEMPCODE, RECMEMBERID,WAYBILLFEE, MOBILE, RECMOBILE, RECTIME, ENDTIME, EXPECTMEMBERID, ORIGINATE, DESTINATION, PAYTYPE, SECRECYTYPE, SEND_EVALUATE, PRODUCT_TYPE, SERVICES_PROD_CODE, CHANGERECORD, IS_ISSUED_PREFRENCE, REC_EVALUATE)VALUES(WAYBILLNO_TMP, RESOURCECODE_TMP, APPOINTMENTDDELIVERYTIME_TMP, APPOINTMENTDDELIVERYENDTIME_TMP,EXPECTEDDELIVERYTIME_TMP,ORIGINATEID_TMP, DESTINATIONID_TMP, WAYBILLSTATUS_TMP, VERSIONNO_TMP, STATUS_TMP, FAILSTARTUS_TMP, FAILCAUSEDESC_TMP, MEMBERID_TMP, HIDEFLAG_TMP, CREATEUSERID_TMP, CREATETIME_TMP, UPDATEUSERID_TMP, UPDATETIME_TMP, APPOINTMENTNO_TMP, SOURCECITY_TMP, DESTCITY_TMP, SOURCECITYCODE_TMP, DESTCITYCODE_TMP, EMPCODE_TMP, RECEMPCODE_TMP, RECMEMBERID_TMP,WAYBILLFEE_TMP, MOBILE_TMP, RECMOBILE_TMP, RECTIME_TMP, ENDTIME_TMP, EXPECTMEMBERID_TMP, ORIGINATE_TMP, DESTINATION_TMP, PAYTYPE_TMP, SECRECYTYPE_TMP, SEND_EVALUATE_TMP, PRODUCT_TYPE_TMP, SERVICES_PROD_CODE_TMP, CHANGERECORD_TMP, IS_ISSUED_PREFRENCE_TMP, REC_EVALUATE_TMP);
    END IF;
    end Loop; 
close bill_cursor;
COMMIT; 
end  ||
delimiter ;

定时器的创建：

每天凌晨一点执行：

   ---轮循删除定时器
   drop event if exists waybill_move_event;
create event waybill_move_event
on schedule every 1 DAY STARTS '2017-01-17 01:00:00'
on completion preserve ENABLE
do call batch_move_cx_waybill_partition_by_minute();


alter event waybill_move_event on completion preserve enable;

其实,在中间踩过一些坑,但是很多都是百度都能解决的 ?.事实证明,这样的数据迁移,其速度并不慢,而且性能相对稳定,在后续的观察中,并未对数据库造成太多的压力,可看作种蚂蚁搬家.

以上就是土嘎嘎小编为大家整理的mysql数据库存储过程数据迁移案例与比较相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！