MySQL 中如何删除单表重复记录
本篇文章给大家分享的是有关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 中如何删除单表重复记录，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。
