mysql删除重复数据只保留一条记录

MySQL重复数据处理
最新推荐文章于 2024-07-11 10:27:20 发布
原创 最新推荐文章于 2024-07-11 10:27:20 发布 · 874 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #删除重复数据 #mysql

本文介绍了一种使用SQL在MySQL中查找并删除重复记录的方法，同时保留最小ID的记录。通过分组统计和子查询实现，避免了直接在FROM子句中更新目标表的错误。

从mysql查找重复数据的sql比较简单，只需要对某个字段进行分组，并统计大于1的结果：

SELECT fieldName,COUNT(*) FROM tableName GROUP BY fieldName HAVING COUNT(*) > 1

但很多时候我们不仅需要查找出来重复的数据，还得删除他们，并且需要保留最小(或最大)id的那一条，sql如下：

DELETE
FROM tableName
WHERE fieldName IN 
    (SELECT * FROM 
        (SELECT fieldName FROM tableName GROUP BY fieldName HAVING COUNT(*) > 1) 
    a )
AND id NOT IN 
    (SELECT * FROM 
        (SELECT MIN(id) FROM tableName GROUP BY fieldName HAVING COUNT(*) > 1)
    b );

注意点：

1.查询出重复数据的sql外面得再包裹一层select * from再进行操作，不然会报错：1093 - You can't specify target table 'env' for update in FROM clause

2.不添加a/b表别名会报错：Every derived table must have its own alias

MySQL有两条以上重复数据时只保留一条
LDC，公众号【轻松学编程】
04-10 3015
MySQL有两条以上重复数据时只取其中一条数据，其他的过滤掉
如何使用MySQL删除重复的记录并保留一条唯一的记录
一方的博客
05-31 751
将source表中所有source_id分组，并查询出分组中id最小的一个，比如id为1、2的source_id重复了，那么这个子查询将查询出id为1的记录。想要了解更多相关知识，可以查看我以往的文章，其中有许多精彩内容。外层的delete将会删除id不是1、3、5的，这样就可以将重复项删除，并保留分组中唯一的一条记录。我的微信公众号：【xdub】，欢迎大家订阅，我会同步文章到公众号上。所以子查询里查询的结果id为：1、3、5.
mysql 删除重复数据只保留一条
感受微风
01-04 437
DELETE FROM 表名 WHERE ID IN ( SELECT * FROM (SELECT MAX(ID) FROM 表名 WHERE ... GROUP BY MSG HAVING COUNT(MSG) > 1) AS b); 注意事项：MSG为重复字段 ID一定要是 唯一字段 ，如果ID代表的重复字段 那么就删除了所有的数据，BE CARE 上面这句SQL：有一...
MySQL中删除重复数据只保留一条
Johnny_bai的博客
09-05 330
用SQL语句,删除掉重复项只保留一条 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢 1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 SELECT * FROM people WHERE peopleId IN ( SELECT peopleId FROM ...
mysql 删除重复数据只保留一条
Stirve
07-22 266
删除重复数据只保留id最大的那一条记录 DELETE FROM table1 WHERE ( sid,name,age ) IN ( SELECT sid,name,age FROM ( SELECT sid,name,age FROM table1 GROUP BY sid,name,age HAVING count( * ) > 1 ) t ) AND id...
mysql 删除重复数据，只保留一条
闲明苑的博客
04-30 720
mysql 删除重复数据，只保留一条1.初始化库和数据表2.删除语句 1.初始化库和数据表 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for channel_daily_report -- ---------------------------...
mysql查找删除重复数据并只保留一条实例详解
10-21
本文将详细介绍如何在MySQL中查找并删除重复数据，同时只保留其中一条，具体以实例进行解析。 首先，假设我们有一个名为`test`的表格，包含以下字段：`school_id`、`school_name`、`total_student`和`test_takers`...
MySQL查询重复数据（删除重复数据保留id最小的一条为唯一数据）
09-09
本文主要探讨如何查询并删除MySQL中的重复数据，同时保留每个重复组中ID最小的一条记录。 首先，我们需要找到表中的重复数据。假设我们有一个名为`brand`的表，其中包含品牌名称`brandName`和其他字段，而我们想要...
mysql 过滤重复数据以及删除表中的重复数据保留一条数据的方法
junjiahuang的博客
02-28 1634
从前面查询重复的数据，加上排除最小的数据，就是我们要删除的多余的重复的数据。
MySql删除重复数据只保留一条
花满天下的博客
11-20 491
最近导入数据时多导了几次导致出现重复的厂商信息 研究了下需要删除重复的厂商信息 首先创建一个临时表 CREATE TABLE firm_temp SELECT * FROM firm; 之后写sql 根据厂商名称删除重复数据保留第一个 #删除厂商中姓名重复的数据保留第一个 delete from firm where firm_name in (select firm_name from fir...
mysql删除重复数据只保留一条
ITlyng的博客
02-19 889
mysql删除重复数据只保留一条; 关键代码(标红):同oracle中不同 DELETE book from book ,    (      SELECT id FROM (           SELECT id FROM book WHERE name IN(SELECT  name FROM book  GROUP BY name HAVING count(name)
MySql删除重复数据(只保留一条)
热门推荐
木子
07-23 2万+
MySql删除重复数据 1. 问题引入 前一段遇到MySql数据重复的问题。由于重复向同一张表导入同一批数据，导致前台展示的数据重复，唯一方便快捷的方法莫过于利用delete操作删除重复数据(已经封板发布)，只保留其中一条数据。但真的是书到用时方恨少，技术也如此。当时只好在网上搜索资源，最终解决了燃眉之急。但是对找到的delete语句并不是太理解，后来抽时间自己研究了一下。现在稍微理解了一点，分享给大家，希望对大家有帮助。 2.数据准备 创建表 CREATE TABLE test_del_repeti
Mysql删除重复数据只保留一条
怪咖@的博客
10-15 2万+
以这张表为例，表里有两条数据，然后名字是相同的，但是id是不同的，现在要求是只留一条数据
mysql 重复数据删除只保留一条
qq_36608622的博客
05-16 3058
插入去重数据：使用INSERT INTO ... SELECT语句结合GROUP BY或DISTINCT将去重后的数据插入临时表。直接在原表上操作，通过自连接删除重复项，但需确保你有足够的方式确定哪一行是需要保留的（例如，通过日期、ID或其他唯一条件）。对于MySQL 8.0及以上版本，可以使用窗口函数ROW_NUMBER()来标记重复行，然后删除标记为非1（即重复的）行。将临时表数据移回原表：将临时表中的数据插入回原表。清理临时表：最后，如果不需要了，可以删除临时表。删除原表数据：清空原表数据。
关于mysql中删除重复记录，并保留重复数据中的一条数据的SQL语句理解
dsiori的博客
10-13 1万+
正好想写一条删除重复语句并保留一条数据的SQL，网上查了一部分资料写的很详细，但还是在这里写下自己的理解，以遍后续学习 。如下： 表字段和数据：  SQL语句：  DELETE FROM `user` WHERE id NOT IN(SELECT * FROM(SELECT id FROM `user` GROUP BY username)AS b) 理解： 先从里面
MySQL 中删除重复的数据并只保留一条
龚小帅的博客
07-11 1万+
要在 MySQL 中删除重复的数据并只保留一条，可以使用下面的方法。
MySQL删除表中多余的重复记录，只保留一条记录
时光沙漏
12-22 2457
1、查询重复记录SELECT * FROM 表名 WHERE 重复字段 IN (SELECT 重复字段 FROM 表名 GROUP BY 重复字段 HAVING COUNT(重复字段)>1);2、删除重复记录，但保留一条DELETE FROM 表名 WHERE 重复字段 IN (SELECT 重复字段 FROM 表名 GROUP BY 重
mysql 删除重复的数据只保留一条
barton的博客
06-12 470
DELETE C FROM auth_user_team C JOIN (    SELECT MIN(Id) Id,user_id,team_id FROM auth_user_team    GROUP BY user_id,team_id    HAVING COUNT(1) >  1) TMP ON C.team_id = TMP.team_id AND c.user_id = TM...
mysql删除重复数据，保留一条数据
最新发布
07-23
<think>我们正在处理一个MySQL数据库问题：删除重复数据并保留每组重复数据中的一条记录。 根据引用[2]和引用[4]，有多种方法可以实现。这里我将总结两种常用且有效的方法，并给出详细的步骤和SQL语句。 假设我们有一个表`your_table`，其中包含重复的数据。我们需要根据某些列（例如，列`col1, col2`）来判断重复，并且我们想保留每组重复中的一条（通常保留id最小或最大的那条）。 方法一：使用临时表（引用[2]中提到的临时表法） 步骤： 1. 创建一个临时表，用于存储去重后的数据。 2. 将去重后的数据（使用GROUP BY按重复列分组，并选择每组中最小或最大的id）插入临时表。 3. 删除原表中的数据。 4. 将临时表的数据插入原表。 5. 删除临时表。 但是，这种方法在数据量大时可能需要谨慎，因为会删除整个表的数据再重新插入。如果表有外键约束或触发器，可能不适用。 方法二：使用DELETE JOIN（引用[4]中提供的方法） 我们可以通过自连接的方式，删除那些在重复组中但不是保留记录的行。 具体步骤（以保留id最小的记录为例）： 1. 首先，我们通过分组找出每组重复数据中id最小的记录（即我们要保留的记录）。 2. 然后，删除那些重复的且id不在保留记录id列表中的记录。 但是，在MySQL中，不能直接在同一语句中同时查询和更新同一张表。因此，我们需要使用中间表（如子查询）来绕过这个限制。 参考引用[4]中的例子： ```sql DELETE FROM your_table WHERE id NOT IN ( SELECT * FROM ( SELECT MIN(id) FROM your_table GROUP BY col1, col2 -- 根据哪些列判断重复 HAVING COUNT(*) > 1 -- 重复组 ) AS temp ) AND (col1, col2) IN ( SELECT col1, col2 FROM your_table GROUP BY col1, col2 HAVING COUNT(*) > 1 ); ``` 但是上面的语句可能效率不高，且逻辑复杂。更常见的做法是使用连接： 另一种写法（使用自连接）： ```sql DELETE t1 FROM your_table t1 INNER JOIN your_table t2 WHERE t1.id > t2.id -- 保留较小的id，删除较大的id（也可以改为保留较大的） AND t1.col1 = t2.col1 AND t1.col2 = t2.col2; ``` 注意：这个连接会删除所有重复组中除了最小id以外的所有记录。但是，这种方法会删除所有重复组，包括只有两条重复的。而且，如果重复组有多个（超过2条），它会将重复组中除了最小id的记录都删除。 但是，上面的自连接方法有一个问题：它会将每个重复组中除了最小id的记录都删除，但可能也会删除非重复组吗？不会，因为非重复组不会满足连接条件（因为只有一条，所以t1和t2不会同时存在且满足t1.col1=t2.col1和t1.col2=t2.col2且t1.id>t2.id）。所以，非重复组不会被删除。 然而，引用[4]中提供了另一种使用LEFT JOIN的方法（保留每组中gi_id最小的记录）： ```sql DELETE FROM chz_goods_img WHERE gi_id NOT IN ( SELECT gi_id FROM ( SELECT gi1.gi_id FROM chz_goods_img gi1 LEFT JOIN chz_goods_img gi2 ON gi1.gi_g_id = gi2.gi_g_id AND gi1.gi_type = gi2.gi_type AND gi1.gi_id > gi2.gi_id -- 这样，如果存在更小的gi_id，则gi2不为空，那么gi1就不是最小的 WHERE gi2.gi_id IS NULL -- 这样只选出最小的gi_id（因为对于最小的记录，找不到比它更小的，所以gi2为NULL） ) AS t ); ``` 这个语句可以保留每组（按gi_g_id和gi_type分组）中gi_id最小的记录，删除其他重复记录。注意，这里没有显式地判断重复，所以它会删除所有非最小id的记录（包括非重复组）。因此，这个语句会删除所有重复组中不是最小id的记录，同时也会保留非重复组（因为非重复组中，该记录本身也是最小id，且没有更小的，所以会被保留）。 但是，我们通常只想删除重复的记录，而保留非重复的记录。因此，上面的语句实际上也可以工作，因为它保留了每个分组（重复或非重复）的最小id记录，删除了其他记录。这样，非重复组只有一条记录，所以不会被删除（因为保留的就是它自己），重复组则只保留最小id的那条。 然而，我们可能只想处理重复数据，而不想影响非重复数据。上面的语句实际上也处理了非重复数据（虽然结果是非重复数据被保留，但执行了判断），所以是安全的。 但是，根据用户需求，我们只需要删除重复数据并保留一条。因此，我们可以使用这种方法。 总结：推荐两种方法 方法1（简单，但需要复制表结构，适用于数据量不大且无外键约束的情况）： ```sql -- 步骤1：创建临时表，存储去重后的数据 CREATE TEMPORARY TABLE temp_table AS SELECT * FROM your_table WHERE 1 GROUP BY col1, col2; -- 注意：这样分组，如果MySQL的sql_mode设置严格，可能会报错，因为非分组列可能有多个值。所以应该用聚合函数或者选择最小id的记录。 -- 更好的做法是明确指定保留哪条记录（例如，保留id最小的记录）： CREATE TEMPORARY TABLE temp_table AS SELECT MIN(id) AS id, col1, col2, ... (其他列) FROM your_table GROUP BY col1, col2; -- 但是，如果其他列有多个值，我们可能需要使用子查询或者聚合函数来获取对应最小id的其他列值？或者，如果其他列在重复组中相同，那么直接取任意一行都可以（因为重复）。或者，我们只根据重复列分组，然后通过id来获取整行？ -- 更安全的做法：使用子查询获取每个重复组的最小id，然后通过id来获取整行 CREATE TEMPORARY TABLE temp_table AS SELECT * FROM your_table WHERE id IN ( SELECT MIN(id) FROM your_table GROUP BY col1, col2 ); -- 步骤2：删除原表数据 DELETE FROM your_table; -- 步骤3：将临时表的数据插入原表 INSERT INTO your_table SELECT * FROM temp_table; -- 步骤4：删除临时表 DROP TEMPORARY TABLE temp_table; ``` 方法2（使用DELETE JOIN，更高效，但需要谨慎）： 我们使用自连接来删除重复记录，保留最小id的记录： ```sql DELETE t1 FROM your_table t1, your_table t2 WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.id > t2.id; ``` 这个语句将删除所有重复组中id不是最小的记录。注意：这个语句会删除重复组中除了最小id的所有记录，非重复组不会被删除（因为非重复组只有一条，不会满足t1.id>t2.id，因为t1和t2是同一组，但非重复组只有一条，所以t1和t2是同一行，而t1.id>t2.id不成立，所以不会删除）。 但是，这个语句有一个问题：如果重复组有3条记录，id分别为1,2,3。那么当比较id=2和id=1时，满足条件（2>1），所以id=2会被删除；当比较id=3和id=1时，满足条件，id=3也会被删除。所以最终保留id=1。这符合预期。 但是，如果重复组中有两条记录，id为1和2，那么id=2会被删除，保留id=1。 所以，这个语句可以达到目的，而且简单高效。 但是，需要注意的是，这种方法可能会因为表数据量大而执行较慢，因为它需要自连接。另外，确保在操作前备份数据。 因此，我们推荐使用第二种方法（DELETE JOIN）来删除重复记录并保留最小id的记录。如果希望保留最大id的记录，只需将`t1.id > t2.id`改为`t1.id < t2.id`。 步骤： 1. 备份数据（重要！） 2. 执行DELETE语句。 示例SQL（假设表名为`ceshi`，重复列为`name, class`，主键为`id`）： ```sql DELETE t1 FROM ceshi t1, ceshi t2 WHERE t1.name = t2.name AND t1.class = t2.class AND t1.id > t2.id; ``` 但是，请注意：如果重复组中，除了重复列外，其他列的值可能不同，那么这种方法只根据重复列（name,class）来分组，删除重复。如果其他列不同，但重复列相同，也会被视为重复。所以，请确保重复列的选择正确。 另外，如果重复组中，有部分记录某些列不同，但重复列相同，那么它们也会被删除（只保留一条）。所以，请根据业务需求选择合适的重复列。 最后，如果表中有大量重复数据，自连接可能会产生很大的中间结果，所以建议在非高峰时段执行，并确保有索引（在重复列上建立索引可以提高效率）。 总结步骤： 1. 确定重复列（例如，name, class）。 2. 确定保留规则（保留最小id还是最大id）。 3. 备份表（重要！）。 4. 执行DELETE语句。 示例：删除重复记录并保留最小id的记录 ```sql DELETE t1 FROM your_table t1 INNER JOIN your_table t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 WHERE t1.id > t2.id; ``` 注意：如果重复列中有NULL值，那么NULL与NULL在比较时，在MySQL中NULL=NULL返回未知，所以不会匹配。因此，这种方法不会将两个都为NULL的记录视为重复。如果需要将NULL视为相等，那么需要特殊处理（例如，使用<=>运算符，但JOIN条件中不支持<=>，所以可以改为使用IS NULL和IS NOT NULL的组合条件）。不过，通常NULL值不会被视为重复，所以这个删除操作不会影响包含NULL的记录。 如果重复列中可能有NULL，并且希望将NULL视为相等，那么可以这样修改条件： ```sql ON (t1.col1 = t2.col1 OR (t1.col1 IS NULL AND t2.col1 IS NULL)) AND (t1.col2 = t2.col2 OR (t1.col2 IS NULL AND t2.col2 IS NULL)) ``` 但是，这样会使条件复杂，且可能影响性能。因此，根据实际需求决定。 最后，如果表非常大，我们可以先找出重复的数据，然后分批删除。 以上是两种常用的方法，请根据实际情况选择。</think>### MySQL 删除重复记录并保留一条记录的方法 在 MySQL 中删除重复数据并保留每组重复记录中的一条，可以通过以下两种常用方法实现。假设表名为 `your_table`，重复依据列为 `col1, col2`，唯一标识列为 `id`： #### 方法一：使用临时表（推荐） ```sql -- 步骤1：创建临时表存储去重数据 CREATE TEMPORARY TABLE temp_table AS SELECT MIN(id) AS id, col1, col2 FROM your_table GROUP BY col1, col2; -- 步骤2：删除原表重复数据 DELETE t FROM your_table t LEFT JOIN temp_table tmp ON t.id = tmp.id WHERE tmp.id IS NULL; -- 步骤3：清理临时表 DROP TEMPORARY TABLE temp_table; ``` **原理**： 1. 通过 `GROUP BY` 分组找出每组重复数据中需保留的记录（通常保留最小/最大 ID） 2. 使用左连接删除不在临时表中的记录 3. 保留数据完整性，避免全表删除[^2] #### 方法二：直接删除（高效） ```sql DELETE t1 FROM your_table t1 INNER JOIN your_table t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.id > t2.id; -- 保留较小ID（改为 < 则保留较大ID） ``` **原理**： 通过自连接定位重复组中需要删除的记录（保留每组中 ID 最小/最大的记录），执行效率较高[^4] #### 操作注意事项 1. **备份数据**：执行前务必备份 ```sql CREATE TABLE your_table_backup AS SELECT * FROM your_table; ``` 2. **添加索引**（大表优化）： ```sql ALTER TABLE your_table ADD INDEX idx_dup (col1, col2, id); ``` 3. **验证重复数据**： ```sql SELECT col1, col2, COUNT(*) FROM your_table GROUP BY col1, col2 HAVING COUNT(*) > 1; ``` #### 特殊场景处理 **需保留非最小/最大 ID 记录**： ```sql DELETE FROM your_table WHERE id NOT IN ( SELECT * FROM ( SELECT MAX(id) -- 或自定义选择逻辑 FROM your_table GROUP BY col1, col2 ) AS tmp ); ``` > **重要提示**：在 MySQL 8.0+ 中可使用窗口函数 `ROW_NUMBER()` 更高效处理，但需注意版本兼容性[^4]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值