MySQL数据去重

最新推荐文章于 2025-02-07 16:41:50 发布

还是要努力啊

最新推荐文章于 2025-02-07 16:41:50 发布

阅读量655

点赞数 2

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/JavaCoderWolf/article/details/121559991

版权

mysql 专栏收录该内容

2 篇文章

订阅专栏

删除重复数据

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

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]