Shulou(Shulou.com)05/31 Report--

This article to share with you is about MySQL how to delete single table duplicate records, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

1. Create table test001

Click here to fold or open

CREATE TABLE `test001` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2. Random write data guide table 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. Query the whole table data

Click here to fold or open

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. Execute SQL to delete duplicate records and keep only the records with the smallest id

DELETE FROM Test001 WHERE id NOT IN (

SELECT minid FROM

(SELECT min(id) AS minidFROM Test001

GROUP BYname) b

);

Click here to fold or open

>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. After execution, duplicate records are deleted.

Click here to fold or open

(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)

The above is how to delete duplicate records in a single table in MySQL. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.