Posted on May 20, 2017 at 10:23 am

Fastest ways to find duplicate records in MySQL.

If name is the column you want to find duplicates in:

SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1;

Is also possible to support multiple columns, like this:

SELECT col1, col2, COUNT(*) c FROM table GROUP BY col1, col2 HAVING c > 1;

Get the IDs of the duplicate rows with GROUP_CONCAT:

SELECT GROUP_CONCAT(id), name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Other Posts

• PHP Generate Secure Random Passwords
• Google Cloud SQL and Public IP Address Security
• How to use Highlight.js to highlight code
• Nginx HTTP to HTTPS and non-www to www behind Google Load Balancer
• Quota 'IN_USE_ADDRESSES' exceeded. Limit: 8.0 in region
• Only allow Google Load Balancer traffic (Firewall Rules)
• Lost connection during Debian apt-get upgrade
• Bootstrap 3 Navbar with Logo Image

Updated Posts

• PHP StrToTime() Returns Incorrect Timestamp '-1 MONTH'
• PHP Correctly Get Last 12 Months (Month per Month)
• Nginx Redirect From a Website to Another Website
• PHP Multi-cURL to Run Parallel cURL Requests (Example)
• Let's Encrypt on Nginx and SSL Grade A+
• Vertically align Facebook and Twitter share buttons
• Download Windows 10 ISO without Media Creation Tool
• DEB.SURY.ORG Updated the Signing Key (March 2019)