服裝批發(fā)做哪個網(wǎng)站好呢上海網(wǎng)站建設(shè)制作
由于數(shù)據(jù)庫增量同步軟件PanguSync初始化最大超時時間為600s,如果初始數(shù)據(jù)量很大,第一次部署時可能會超時,可以先停止任務(wù),使用以下Sql語句進(jìn)行初始化,以下語句可以分步執(zhí)行,初始化完成后,后續(xù)無需再執(zhí)行耗時的初始化。
Sqlserver
源操作:1.創(chuàng)建字段
alter table 表名 Add C_PanguSyncSourceTimestamp datetime2(6) not null default sysdatetime()2.設(shè)置初始化分批時間
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.創(chuàng)建索引
CREATE INDEX I_PanguSyncSourceTimestamp on 表名(C_PanguSyncSourceTimestamp ) WITH (ONLINE = ON)
--------------------------------------------------------------------------------------------------------
目標(biāo)操作:1.創(chuàng)建字段
alter table 表名 Add C_PanguSyncTargetTimestamp datetime2(6) not null default '2000-01-01 00:00:00.000000'2.設(shè)置初始化分批時間如果存在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.創(chuàng)建索引
CREATE INDEX I_PanguSyncTargetTimestamp on 表名(C_PanguSyncTargetTimestamp ) WITH (ONLINE = ON)
Mysql
源操作:1.創(chuàng)建字段
alter table `源表` Add column `C_PanguSyncSourceTimestamp` timestamp(6) not null default current_timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6);2.設(shè)置分批時間
SET @row_number = 0;
UPDATE `源表` SET C_PanguSyncSourceTimestamp = TIMESTAMPADD(MICROSECOND, -(@row_number:=@row_number + 1), CAST(current_timestamp(6) AS DATETIME(6)))3.創(chuàng)建索引
CREATE INDEX I_PanguSyncSourceTimestamp on `源表` (`C_PanguSyncSourceTimestamp`)
--------------------------------------------------------------------------------------------------------------------------------------
目標(biāo)操作:1.創(chuàng)建字段
alter table `目標(biāo)表` Add column C_PanguSyncTargetTimestamp timestamp(6) NOT NULL DEFAULT '2000-01-01 00:00:00.000000'2.設(shè)置分批時間如果存在C_PanguSyncSourceTimestamp字段:SET @row_number = 0;
UPDATE `目標(biāo)表` 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 `目標(biāo)表` SET C_PanguSyncTargetTimestamp = TIMESTAMPADD(MICROSECOND, -(@row_number:=@row_number + 1), CAST('2000-01-01 00:00:00.000000' AS DATETIME(6)))3.創(chuàng)建索引
CREATE INDEX I_PanguSyncTargetTimestamp on `目標(biāo)表` (`C_PanguSyncTargetTimestamp`)