表tb_journal_metric有主键自增id字段和jguid,type,year三个字段组成的联合唯一索引。
- 删除重复数据，只保留一条
DELETE
FROM
tb_journal_metric
WHERE
id NOT IN ( SELECT max( id ) FROM tb_journal_metric GROUP BY jguid, col_type, col_year );
- 插入冲突则更新
INSERT INTO tb_journal_metric ( jguid, col_type, col_year, col_value )
VALUES
( "0004fb96-44be-4dae-98f9-bcb1180c784e", "H5", 2018, 0.02 ),(
"0007188f-f7e1-4e02-a00f-49dd3acb03f3",
"H5",
2018,
0.03
)
ON DUPLICATE KEY UPDATE col_value =
VALUES
( col_value );
