本篇文章给大家分享的是有关MySQL 中如何删除单表重复记录，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。



1、创建表test001

点击(此处)折叠或打开

CREATE TABLE `test001` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2、随机写入数据导表test001中
insert into test001 (name) values('A');
insert into test001 (name) values('B');
insert into test001 (name) values('C');
insert into test001 (name) values('d');

3、查询整表数据

点击(此处)折叠或打开

select * from test001;

+----+------+

| id | name |

+----+------+

| 1 | A |

| 2 | A |

| 3 | A |

| 4 | A |

| 5 | A |

| 6 | A |

| 7 | A |

| 8 | A |

| 9 | B |

| 10 | B |

| 11 | B |

| 12 | B |

| 13 | B |

| 14 | B |

| 15 | C |

| 16 | C |

| 17 | C |

| 18 | C |

| 19 | d |

| 20 | d |

| 21 | d |

| 22 | d |

| 23 | d |

+----+------+

23 rows in set (0.00 sec)

4、执行SQL删除重复记录，只保留id最小的记录

DELETE FROM Test001 WHERE id NOT IN (

SELECT minid FROM

(SELECT min(id) AS minidFROM Test001

GROUP BYname) b

);

点击(此处)折叠或打开

>DELETE

-> FROM

-> Test001

-> WHERE

-> id NOT IN (

-> SELECT

-> minid

-> FROM

-> (

-> SELECT

-> min(id) AS minid

-> FROM

-> Test001

-> GROUP BY

-> name

-> ) b

-> );

Query OK, 19 rows affected (0.00 sec)


(root@localhost:mysql.sock) [test]>select * from test001;

+----+------+

| id | name |

+----+------+

| 1 | A |

| 9 | B |

| 15 | C |

| 19 | d |

+----+------+

4 rows in set (0.00 sec)

5、执行后的结果，重复记录都删除了

点击(此处)折叠或打开

(root@localhost:mysql.sock) [test]>select * from test001;

+----+------+

| id | name |

+----+------+

| 1 | A |

| 9 | B |

| 15 | C |

| 19 | d |

+----+------+

4 rows in set (0.00 sec)

以上就是MySQL 中如何删除单表重复记录，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。