如何使用PHP在mysql表中查找重复项? [英] How to find duplicates in mysql table using PHP?

问题描述

我有一张包含客户信息的表格.通常，PHP 在插入新行之前检查重复项.但是，我不得不手动转储很多旧行，现在它们都在我的表中，我需要检查重复项.

I have a table with customer info. Normally, the PHP checks for duplicates before they new rows are inserted. However, I had to dump a lot of older rows manually and now that they are all in my table, I need to check for duplicates.

示例行:

身份证、姓名、电子邮件、电话、传真

id, name, email, phone, fax

我想做一个 mysql 查询，它会显示所有带有匹配电子邮件的 ID.我可以稍后修改电话、传真等查询.

I would like to do a mysql query that will show all ID's with matching emails. I can modify the query later for phone, fax, etc.

我有一种感觉，我将使用 DISTINCT，但我不太确定它是如何完成的.

I have a feeling I will be using DISTINCT, but I am not quite sure how it's done.

推荐答案

您可以使用 HAVING COUNT(*) >GROUP BY email1 查找所有重复的电子邮件地址，然后将生成的重复电子邮件与您的表连接以获取 ID:

You can GROUP BY email with HAVING COUNT(*) > 1 to find all duplicate email addresses, then join the resulting duplicate emails with your table to fetch the ids:

SELECT id FROM my_table NATURAL JOIN (
  SELECT email FROM my_table GROUP BY email HAVING COUNT(*) > 1
) t

这篇关于如何使用PHP在mysql表中查找重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！