mysql查询重复项

Querying Duplicates in MySQL
1
2
3
To find duplicate records in a MySQL table, you can use the GROUP BY clause along with the HAVING clause. This approach identifies rows where specific column values appear more than once.

Example: Finding Duplicate Records Based on a Single Column

SELECT column_name, COUNT() AS occurrences
FROM table_name
GROUP BY column_name
HAVING COUNT() > 1;
Copy
This query groups rows by column_name and filters groups where the count exceeds 1, indicating duplicates.

Example: Finding Duplicates Across Multiple Columns

If duplicates are determined by multiple columns, you can group by those columns:

SELECT column1, column2, COUNT() AS occurrences
FROM table_name
GROUP BY column1, column2
HAVING COUNT() > 1;
Copy
This identifies rows where the combination of column1 and column2 is repeated.

Deleting Duplicate Records

To remove duplicates while keeping one instance, use a subquery to identify duplicates and delete them:

DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id)
FROM table_name
GROUP BY column_name
);
Copy
Here, MIN(id) ensures only the row with the smallest ID is retained for each duplicate group.

Considerations:

Always back up your data before running delete operations.

Use DISTINCT or GROUP BY to fetch unique records if you only need to query without modifying data.

Learn more:
1 -
blog.csdn.net
2 -
cnblogs.com
3 -
runoob.com