How to query duplicate fields in a MySQL large table

How can I query repeated fields in a MySQL large table? This is a problem that many people have encountered. The following describes how to query duplicate fields in a MySQL large table for your reference.

There is a large table in the database. You need to find a duplicate record id in the name for comparison.

It is easy to find fields with unique names in the database.

 
 

  
  
SELECT min(`id`),`name`   
  
  
FROM `table`   
  
  
GROUP BY `name`;  
 
 

However, the id value of the repeated field cannot be obtained. Only the smallest id value is returned)

It is easy to query which fields are repeated.

 
 

  
  
SELECT `name`,count(`name`) as count   
  
  
FROM `table`   
  
  
GROUP BY `name` HAVING count(`name`) >1   
  
  
ORDER BY count DESC;  
 
 

However, to query the id of a repeated field at a time, you must use the subquery. Therefore, use the following statement to query duplicate fields in a MySQL large table.

 
 

  
  
SELECT `id`,`name`   
  
  
FROM `table`   
  
  
WHERE `name` in (   
  
  
   SELECT `name`   
  
  
   FROM `table`   
  
  
   GROUP BY `name` HAVING count(`name`) >1  
  
  
);  
 
 

However, this statement is too inefficient in mysql, and mysql does not generate a zero-value table for the subquery.

Therefore, create a zero-time table first.

 
 

  
  
create table `tmptable` as (  
  
  
   SELECT `name`   
  
  
   FROM `table`   
  
  
   GROUP BY `name` HAVING count(`name`) >1  
  
  
);  
 
 

Then use multi-Table connection to query

 
 

  
  
SELECT a.`id`, a.`name`   
  
  
FROM `table` a, `tmptable` t   
  
  
WHERE a.`name` = t.`name`;  
 
 

The result is coming soon.

Duplicate with distinct

 
 

  
  
SELECT distinct a.`id`, a.`name`   
  
  
FROM `table` a, `tmptable` t   
  
  
WHERE a.`name` = t.`name`;  
 
 

Mysql query case

How to query duplicate records in MYSQL

Implementation of MySQL random Query

Example of the number of MySQL query results

Optimization of MySQL query Paging