2020-12-16 MySQL存在则更新不存在则新增方法
假设：dw.Bridge_cbs_crm的唯一Key定义为：student_id（主键、UNIQUE均可）
/*存在则更新*/
INSERT INTO dw.Bridge_cbs_crm (
student_id,
student_name,
serial,
`status`,
data_type,
lastSyncTime
)
SELECT
student_id,
student_name,
serial,
`status`,
data_type,
@UpdateDate as lastSyncTime
FROM tmp_bridge_cbs_crm
WHERE data_type = 0
ON DUPLICATE KEY
UPDATE lastSyncTime=@UpdateDate;
/*存在则替换*/
REPLACE INTO dw.Bridge_cbs_crm (
student_id,
student_name,
serial,
`status`,
data_type,
lastSyncTime
)
SELECT
student_id,
student_name,
serial,
`status`,
data_type,
@UpdateDate as lastSyncTime
FROM tmp_bridge_cbs_crm
WHERE data_type = 0;
/*插入新的差异数据,避免重复插入*/
INSERT IGNORE INTO dw.Bridge_cbs_crm (
student_id,
student_name,
serial,
`status`,
data_type,
lastSyncTime
)
SELECT
student_id,
student_name,
serial,
`status`,
data_type,
@UpdateDate as lastSyncTime
FROM tmp_bridge_cbs_crm
WHERE data_type = 0;
