TRS.WCM.Developer/贵州项目/1_data_migration_offset.sql

-- 建立offset表，记录迁移的次数、上次迁移的id、偏移量用于增量时能够获取到每次偏移量的变化
CREATE  TABLE `data_migration_offset`(
  `id` INT(11) NOT NULL COMMENT '数据表ID',
  `table_name` VARCHAR(100) NOT NULL COMMENT '迁移的表名',
  `offset_num` BIGINT(20) NOT NULL comment '偏移量',
  `start_id` BIGINT(20) NOT NULL comment '起始id',
  `end_id` BIGINT(20) NOT NULL comment '终止ID',
  `crtime` datetime default CURRENT_TIMESTAMP NOT NULL comment '时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE  TABLE `meta_data`(
  `metadat_id` BIGINT(20) NOT NULL comment '元数据ID',
  `channel_id` BIGINT(20) NOT NULL comment '栏目ID',
  PRIMARY KEY (`metadat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE  TABLE `relphoto_doc`(
  `docid` BIGINT(20) NOT NULL comment '文档ID',
  `appendixids` VARCHAR(400) NOT NULL comment '附件字段',
  PRIMARY KEY (`docid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE  TABLE `relvideo_doc`(
  `docid` BIGINT(20) NOT NULL comment '文档ID',
  `appendixids` VARCHAR(400) NOT NULL comment '附件字段',
  PRIMARY KEY (`docid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE
IF
	EXISTS setOffset;

DELIMITER $$
CREATE PROCEDURE setOffset ( IN t_name VARCHAR ( 100 ), IN _offset BIGINT ( 20 ), IN _start_id BIGINT ( 20 ),
IN _end_id BIGINT ( 20 ) 
) BEGIN
	
	SET @id = IFNULL( ( SELECT id FROM data_migration_offset WHERE table_name = t_name AND offset_num = _offset AND start_id = _start_id ), 0 );
	IF
		@id = 0 THEN
			INSERT INTO data_migration_offset ( id, table_name, offset_num, start_id, end_id ) SELECT
			IFNULL( max( id ), 0 ) + 1,
			t_name,
			_offset,
			_start_id,
			_end_id 
			FROM
				data_migration_offset;
		ELSE 
			UPDATE data_migration_offset 
			  SET end_id = _end_id
			WHERE
				id = @id ;
		END IF;

END $$