mysql备忘SQL

表tb_journal_metric有主键自增id字段和jguid,type,year三个字段组成的联合唯一索引。

1. 删除重复数据，只保留一条

DELETE 
FROM
    tb_journal_metric 
WHERE
    id NOT IN ( SELECT max( id ) FROM tb_journal_metric GROUP BY jguid, col_type, col_year );

2. 插入冲突则更新

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 );