MySQL query and delete duplicate records

Table structure, data as follows:

CREATE TABLE `redis_num_mem` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Self increment id',
  `cluster_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Redis colony id',
  `type` varchar(10) NOT NULL DEFAULT '' COMMENT 'key type',
  `number` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'key Number of',
  `memory` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'key In storage, unit bytes',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Record generation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8 COMMENT='redis Type table'


MariaDB [test]> select * from redis_num_mem;
+-----+------------+--------+--------+--------+---------------------+
| id  | cluster_id | type   | number | memory | create_time         |
+-----+------------+--------+--------+--------+---------------------+
|   1 |          1 | hash   |  47079 |    100 | 2018-10-22 23:48:12 |
|   2 |          1 | set    |  20608 |    100 | 2018-10-22 23:48:12 |
|   3 |          1 | list   |   3938 |    100 | 2018-10-22 23:48:12 |
|   4 |          1 | string |  17662 |    100 | 2018-10-22 23:48:12 |
|   5 |          1 | hash   |  46808 |    100 | 2018-10-22 23:48:12 |
|   6 |          1 | set    |  20854 |    100 | 2018-10-22 23:48:12 |
|   7 |          1 | string |  17710 |    100 | 2018-10-22 23:48:12 |
|   8 |          1 | list   |   3917 |    100 | 2018-10-22 23:48:12 |
|   9 |          1 | list   |   3854 |    100 | 2018-10-22 23:48:12 |
|  10 |          1 | string |  17651 |    100 | 2018-10-22 23:48:12 |
|  11 |          1 | hash   |  46960 |    100 | 2018-10-22 23:48:12 |
|  12 |          1 | set    |  20654 |    100 | 2018-10-22 23:48:12 |
|  14 |          2 |        |      0 |      0 | 2018-10-23 10:34:54 |
|  15 |          2 |        |      0 |      0 | 2018-10-23 10:35:42 |
|  16 |          0 |        |      0 |      0 | 2018-10-23 10:50:39 |
|  20 |          0 |        |      0 |      0 | 2018-10-23 11:05:52 |
| 100 |          0 |        |      0 |      0 | 2018-10-23 11:05:38 |
| 101 |          0 |        |      0 |      0 | 2018-10-23 11:06:09 |
+-----+------------+--------+--------+--------+---------------------+
18 rows in set (0.00 sec)

Find all duplicate records:

SELECT * FROM redis_num_mem a WHERE ((SELECT COUNT(*) FROM redis_num_mem WHERE type = a.type) > 1) ORDER BY type DESC;

I. find duplicate records

1.1 find all duplicate records

Select * From redis_num_mem Where type In (Select type From redis_num_mem Group By type Having Count(*)>1) ;

1.2 filter duplicate records (only one is displayed)

select * From redis_num_mem where id in (Select Max(id) From  redis_num_mem Group By type);

Note: the maximum ID record is shown here

2. Delete duplicate records

2.1 delete all duplicate records (use with caution)

delete from redis_num_mem where type in (select type from (select type from redis_num_mem group by type having count(*)>1)  as t1);

2.2 keep one (this should be what most people need)

delete from redis_num_mem where id not in (select * from (select max(id) from redis_num_mem group by type) as t1);

Three, supplement

1. Redundant duplicate records in the lookup table are judged by a single field (peopleId)

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2. Delete the redundant duplicate records in the table. The duplicate records are judged according to a single field (peopleId). Only the records with the lowest rowid are left

delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3. Redundant duplicate records (multiple fields) in the lookup table

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4. Delete redundant duplicate records (multiple fields) in the table, leaving only the records with the lowest rowid

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5. Redundant duplicate records (multiple fields) in the lookup table, excluding the records with the lowest rowid

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

Posted by s4salman on Tue, 10 Dec 2019 16:00:02 -0800

Programmer Group