由于数据库增量同步软件PanguSync初始化最大超时时间为200s,如果数据量很大,初次部署时可能会超时,可以先停止任务,使用以下Sql语句进行初始化,以下语句可以分步执行,初始化完成后,后续无需再执行耗时的初始化。
Sqlserver
源操作:
1.创建字段
alter table 表名 Add C_PanguSyncSourceTimestamp datetime2(6) not null default sysdatetime()
2.设置初始化分批时间
WITH NumberedRows AS (
SELECT
C_PanguSyncSourceTimestamp ,
ROW_NUMBER() OVER (ORDER BY [主键] desc) AS RowNum
FROM
表名
)
UPDATE Nr
SET Nr.C_PanguSyncSourceTimestamp = DATEADD(MILLISECOND, -Nr.RowNum, SYSDATETIME())
FROM
NumberedRows Nr;
3.创建索引
CREATE INDEX I_PanguSyncSourceTimestamp on 表名(C_PanguSyncSourceTimestamp ) WITH (ONLINE = ON)
--------------------------------------------------------------------------------------------------------
目标操作:
1.创建字段
alter table 表名 Add C_PanguSyncTargetTimestamp datetime2(6) not null default '2000-01-01 00:00:00.000000'
2.设置初始化分批时间
如果存在C_PanguSyncSourceTimestamp字段:
WITH NumberedRows AS (
SELECT
C_PanguSyncTargetTimestamp,
C_PanguSyncSourceTimestamp ,
ROW_NUMBER() OVER (ORDER BY (select NULL)) AS RowNum
FROM
表名
)
UPDATE Nr
SET Nr.C_PanguSyncSourceTimestamp =Nr.C_PanguSyncSourceTimestamp ,Nr.C_PanguSyncTargetTimestamp = DATEADD(MILLISECOND, -Nr.RowNum, CAST('2000-01-01 00:00:00.000' AS DATETIME))
FROM
NumberedRows Nr;
如果不存在C_PanguSyncSourceTimestamp字段:
WITH NumberedRows AS (
SELECT
C_PanguSyncTargetTimestamp,
ROW_NUMBER() OVER (ORDER BY (select NULL)) AS RowNum
FROM
表名
)
UPDATE Nr
SET Nr.C_PanguSyncTargetTimestamp = DATEADD(MILLISECOND, -Nr.RowNum, CAST('2000-01-01 00:00:00.000' AS DATETIME))
FROM
NumberedRows Nr;
3.创建索引
CREATE INDEX I_PanguSyncTargetTimestamp on 表名(C_PanguSyncTargetTimestamp ) WITH (ONLINE = ON)
Mysql
源操作:
1.创建字段
alter table `源表` Add column `C_PanguSyncSourceTimestamp` timestamp(6) not null default current_timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6);
2.设置分批时间
SET @row_number = 0;
UPDATE `源表` SET C_PanguSyncSourceTimestamp = TIMESTAMPADD(MICROSECOND, -(@row_number:=@row_number + 1), CAST(current_timestamp(6) AS DATETIME(6)))
3.创建索引
CREATE INDEX I_PanguSyncSourceTimestamp on `源表` (`C_PanguSyncSourceTimestamp`)
--------------------------------------------------------------------------------------------------------------------------------------
目标操作:
1.创建字段
alter table `目标表` Add column C_PanguSyncTargetTimestamp timestamp(6) NOT NULL DEFAULT '2000-01-01 00:00:00.000000'
2.设置分批时间
如果存在C_PanguSyncSourceTimestamp字段:
SET @row_number = 0;
UPDATE `目标表` SET C_PanguSyncSourceTimestamp=C_PanguSyncSourceTimestamp,C_PanguSyncTargetTimestamp = TIMESTAMPADD(MICROSECOND, -(@row_number:=@row_number + 1), CAST('2000-01-01 00:00:00.000000' AS DATETIME(6)))
如果不存在C_PanguSyncSourceTimestamp字段:
SET @row_number = 0;
UPDATE `目标表` SET C_PanguSyncTargetTimestamp = TIMESTAMPADD(MICROSECOND, -(@row_number:=@row_number + 1), CAST('2000-01-01 00:00:00.000000' AS DATETIME(6)))
3.创建索引
CREATE INDEX I_PanguSyncTargetTimestamp on `目标表` (`C_PanguSyncTargetTimestamp`)