Duplication data or duplicate records (value) in MySQL can happens because of many reasons. Let’s learn and practice how find them.
Find Duplicate Records In One Column
The following query will display a duplicate username from users table :
SELECT
username,
COUNT(*) duplicate
FROM
users
GROUP BY username HAVING duplicate > 1;
Also, you can using HAVING COUNT to find duplicate records as follows :
SELECT
username,
COUNT(*) duplicate
FROM
users
GROUP BY username HAVING COUNT(duplicate) > 1;
Find Duplicate Records In Multiple Column
To find duplicate records based on multiple columns, you can use the following query :
SELECT
first_name, COUNT(first_name),
last_name, COUNT(last_name),
email, COUNT(email)
FROM
users
GROUP BY
first_name ,
last_name ,
email
HAVING
COUNT(first_name) > 1
AND COUNT(last_name) > 1
AND COUNT(email) > 1;
Hopefully useful.