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 obtained)
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 temporary table for the subquery.
Create a temporary 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`;