MySQL数据去重

删除重复数据

一次删除一批重复数据，需要多次执行

DELETE FROM TABLE_NAME 
WHERE ID IN(
	(
		SELECT MIN(ID) 
		FROM TABLE_NAME 
		GROUP BY COLUMNA,COLUMNB
		HAVING COUNT(1)> 1
	)TEMPA 
)

REPLACE 插入新行之前删除旧行

• https://dev.mysql.com/doc/refman/5.7/en/replace.html

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...

INSERT INTO ON DUPLICATE KEY UPDATE

• 表需要设置唯一键，当数据重复时，更新该数据
• https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;

• 使用values函数引用表中对应列的值

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

INSERT INTO IGNORE

• 忽略重复值插入

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]