DROP PROCEDURE IF EXISTS sp_data_migration_mas_videostream; DELIMITER $$ CREATE PROCEDURE sp_data_migration_mas_videostream( IN prevMasVideoId BIGINT(20), IN videostreamIdOffset BIGINT(20), IN masVideoIdOffset BIGINT(20)) BEGIN DECLARE isover INT DEFAULT 0; DECLARE migr_table_name varchar(255) DEFAULT 'mas_videostream'; DECLARE f_ID int(11) DEFAULT NULL; DECLARE f_CREATEDTIME bigint(20) DEFAULT NULL; DECLARE f_CREATEDUSER varchar(255) DEFAULT NULL; DECLARE f_CREATEDUSERID int(11) DEFAULT NULL; DECLARE f_CREATEDUSERNICKNAME varchar(255) DEFAULT NULL; DECLARE f_LASTMODIFIEDTIME bigint(20) DEFAULT NULL; DECLARE f_LASTMODIFIEDUSER varchar(255) DEFAULT NULL; DECLARE f_LASTMODIFIEDUSERID int(11) DEFAULT NULL; DECLARE f_ATTACHEDPIC int(11) DEFAULT NULL; DECLARE f_AUDIOBITRATE int(11) DEFAULT NULL; DECLARE f_AUDIOCHANNELS int(11) DEFAULT NULL; DECLARE f_AUDIOCODEC varchar(255) DEFAULT NULL; DECLARE f_AUDIOFORMAT varchar(255) DEFAULT NULL; DECLARE f_AUDIOSAMPLERATE int(11) DEFAULT NULL; DECLARE f_BITRATE int(11) DEFAULT NULL; DECLARE f_DEMUXER varchar(255) DEFAULT NULL; DECLARE f_DURATION int(11) DEFAULT NULL; DECLARE f_FPS int(11) DEFAULT NULL; DECLARE f_FRAMERATE double DEFAULT NULL; DECLARE f_HEIGHT int(11) DEFAULT NULL; DECLARE f_mediaType varchar(255) DEFAULT NULL; DECLARE f_NBFRAMES int(11) DEFAULT NULL; DECLARE f_PIXELFORMAT varchar(255) DEFAULT NULL; DECLARE f_VIDEOCODEC varchar(255) DEFAULT NULL; DECLARE f_VIDEOFORMAT varchar(255) DEFAULT NULL; DECLARE f_VIDEOLEVEL varchar(255) DEFAULT NULL; DECLARE f_VIDEOPROFILE varchar(255) DEFAULT NULL; DECLARE f_WIDTH int(11) DEFAULT NULL; DECLARE f_CONSEQUENT int(11) DEFAULT NULL; DECLARE f_CONSOLEONLY bit(1) DEFAULT NULL; DECLARE f_CONSUMERAPPID int(11) DEFAULT NULL; DECLARE f_CPUTIME double DEFAULT NULL; DECLARE f_ELAPSEDSECONDS int(11) DEFAULT NULL; DECLARE f_FILEEXT varchar(255) DEFAULT NULL; DECLARE f_FILENAME varchar(255) DEFAULT NULL; DECLARE f_FORMAT varchar(255) DEFAULT NULL; DECLARE f_FSROOT varchar(255) DEFAULT NULL; DECLARE f_ISRETRANSCODE bit(1) DEFAULT NULL; DECLARE f_MAXMENKB varchar(255) DEFAULT NULL; DECLARE f_OPERATOR varchar(255) DEFAULT NULL; DECLARE f_PROGRESSIVESTATUS varchar(255) DEFAULT NULL; DECLARE f_REPOSITORYID int(11) DEFAULT NULL; DECLARE f_SIZE bigint(20) DEFAULT NULL; DECLARE f_SUBPATH varchar(255) DEFAULT NULL; DECLARE f_TRANSCODECMD varchar(1024) DEFAULT NULL; DECLARE f_TYPE int(11) DEFAULT NULL; DECLARE f_VIDEOID int(11) DEFAULT NULL; DECLARE cur CURSOR FOR SELECT ID, CREATEDTIME, CREATEDUSER, CREATEDUSERID, CREATEDUSERNICKNAME, LASTMODIFIEDTIME, LASTMODIFIEDUSER, LASTMODIFIEDUSERID, ATTACHEDPIC, AUDIOBITRATE, AUDIOCHANNELS, AUDIOCODEC, AUDIOFORMAT, AUDIOSAMPLERATE, BITRATE, DEMUXER, DURATION, FPS, FRAMERATE, HEIGHT, mediaType, NBFRAMES, PIXELFORMAT, VIDEOCODEC, VIDEOFORMAT, VIDEOLEVEL, VIDEOPROFILE, WIDTH, CONSEQUENT, CONSOLEONLY, CONSUMERAPPID, CPUTIME, ELAPSEDSECONDS, FILEEXT, FILENAME, FORMAT, FSROOT, ISRETRANSCODE, MAXMENKB, OPERATOR, PROGRESSIVESTATUS, REPOSITORYID, SIZE, SUBPATH, TRANSCODECMD, TYPE, VIDEOID FROM mas_videostream ORDER BY ID asc; DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1; OPEN cur; FETCH cur INTO f_ID, f_CREATEDTIME, f_CREATEDUSER, f_CREATEDUSERID, f_CREATEDUSERNICKNAME, f_LASTMODIFIEDTIME, f_LASTMODIFIEDUSER, f_LASTMODIFIEDUSERID, f_ATTACHEDPIC, f_AUDIOBITRATE, f_AUDIOCHANNELS, f_AUDIOCODEC, f_AUDIOFORMAT, f_AUDIOSAMPLERATE, f_BITRATE, f_DEMUXER, f_DURATION, f_FPS, f_FRAMERATE, f_HEIGHT, f_mediaType, f_NBFRAMES, f_PIXELFORMAT, f_VIDEOCODEC, f_VIDEOFORMAT, f_VIDEOLEVEL, f_VIDEOPROFILE, f_WIDTH, f_CONSEQUENT, f_CONSOLEONLY, f_CONSUMERAPPID, f_CPUTIME, f_ELAPSEDSECONDS, f_FILEEXT, f_FILENAME, f_FORMAT, f_FSROOT, f_ISRETRANSCODE, f_MAXMENKB, f_OPERATOR, f_PROGRESSIVESTATUS, f_REPOSITORYID, f_SIZE, f_SUBPATH, f_TRANSCODECMD, f_TYPE, f_VIDEOID ; select concat('开始迁移 ', migr_table_name) info; WHILE isover= 0 DO # 断点续传 IF (f_ID > prevMasVideoId) THEN SET @ID = f_ID; SET f_ID = f_ID + videostreamIdOffset; SET f_VIDEOID = f_VIDEOID + masVideoIdOffset; insert into trs_mas.mas_videostream ( ID, CREATEDTIME, CREATEDUSER, CREATEDUSERID, CREATEDUSERNICKNAME, LASTMODIFIEDTIME, LASTMODIFIEDUSER, LASTMODIFIEDUSERID, ATTACHEDPIC, AUDIOBITRATE, AUDIOCHANNELS, AUDIOCODEC, AUDIOFORMAT, AUDIOSAMPLERATE, BITRATE, DEMUXER, DURATION, FPS, FRAMERATE, HEIGHT, mediaType, NBFRAMES, PIXELFORMAT, VIDEOCODEC, VIDEOFORMAT, VIDEOLEVEL, VIDEOPROFILE, WIDTH, CONSEQUENT, CONSOLEONLY, CONSUMERAPPID, CPUTIME, ELAPSEDSECONDS, FILEEXT, FILENAME, FORMAT, FSROOT, ISRETRANSCODE, MAXMENKB, OPERATOR, PROGRESSIVESTATUS, REPOSITORYID, SIZE, SUBPATH, TRANSCODECMD, TYPE, VIDEOID ) VALUES ( f_ID, f_CREATEDTIME, f_CREATEDUSER, f_CREATEDUSERID, f_CREATEDUSERNICKNAME, f_LASTMODIFIEDTIME, f_LASTMODIFIEDUSER, f_LASTMODIFIEDUSERID, f_ATTACHEDPIC, f_AUDIOBITRATE, f_AUDIOCHANNELS, f_AUDIOCODEC, f_AUDIOFORMAT, f_AUDIOSAMPLERATE, f_BITRATE, f_DEMUXER, f_DURATION, f_FPS, f_FRAMERATE, f_HEIGHT, f_mediaType, f_NBFRAMES, f_PIXELFORMAT, f_VIDEOCODEC, f_VIDEOFORMAT, f_VIDEOLEVEL, f_VIDEOPROFILE, f_WIDTH, f_CONSEQUENT, f_CONSOLEONLY, f_CONSUMERAPPID, f_CPUTIME, f_ELAPSEDSECONDS, f_FILEEXT, f_FILENAME, f_FORMAT, f_FSROOT, f_ISRETRANSCODE, f_MAXMENKB, f_OPERATOR, f_PROGRESSIVESTATUS, f_REPOSITORYID, f_SIZE, f_SUBPATH, f_TRANSCODECMD, f_TYPE, f_VIDEOID ); SET @log_sql = build_migration_log_sql(migr_table_name, @ID, now()); prepare stmt from @log_sql; EXECUTE stmt; deallocate prepare stmt; call setOffset(migr_table_name, masVideoIdOffset, prevMasVideoId, @ID); END IF ; FETCH cur INTO f_ID, f_CREATEDTIME, f_CREATEDUSER, f_CREATEDUSERID, f_CREATEDUSERNICKNAME, f_LASTMODIFIEDTIME, f_LASTMODIFIEDUSER, f_LASTMODIFIEDUSERID, f_ATTACHEDPIC, f_AUDIOBITRATE, f_AUDIOCHANNELS, f_AUDIOCODEC, f_AUDIOFORMAT, f_AUDIOSAMPLERATE, f_BITRATE, f_DEMUXER, f_DURATION, f_FPS, f_FRAMERATE, f_HEIGHT, f_mediaType, f_NBFRAMES, f_PIXELFORMAT, f_VIDEOCODEC, f_VIDEOFORMAT, f_VIDEOLEVEL, f_VIDEOPROFILE, f_WIDTH, f_CONSEQUENT, f_CONSOLEONLY, f_CONSUMERAPPID, f_CPUTIME, f_ELAPSEDSECONDS, f_FILEEXT, f_FILENAME, f_FORMAT, f_FSROOT, f_ISRETRANSCODE, f_MAXMENKB, f_OPERATOR, f_PROGRESSIVESTATUS, f_REPOSITORYID, f_SIZE, f_SUBPATH, f_TRANSCODECMD, f_TYPE, f_VIDEOID ; END WHILE; CLOSE cur; select concat('完成迁移 ', migr_table_name) info; END $$ DELIMITER ;