How To Find Duplicate Values in MySQL

March 4, 2020

Introduction

MySQL is a database application that stores data in rows and columns of different tables to avoid duplication. Duplicate values can occur, which can impact MySQL performance.

This guide will show you how to find duplicate values in a MySQL database.

tutorial on how to check duplicates in MySQL Database

Prerequisites

  • An existing installation of MySQL
  • Root user account credentials for MySQL
  • A command line / terminal window

Setting up a Sample Table (Optional)

This step will help you create a sample table to work with. If you already have a database to work on, skip to the next section.

Open a terminal window, and switch to the MySQL shell:

mysql –u root –p

Note: If you get ‘ERROR 1698’, try opening MySQL using sudo mysql instead. This error occurs on some Ubuntu installations and is caused by authentication settings.

List existing databases:

SHOW databases;
The list of MySQL databases when using the show databases command

Create a new database that doesn’t already exist:

CREATE database <em>sampledb</em>;

Select the table you just created:

USE <em>sampledb</em>;

Create a new table with the following fields:

CREATE TABLE dbtable (
  id INT PRIMARY KEY AUTO_INCREMENT,
  date_x VARCHAR(10) NOT NULL,
  system_x VARCHAR(50) NOT NULL,
  test VARCHAR(50) NOT NULL
);

Insert rows into the table:

INSERT INTO dbtable (date_x,system_x,test)
VALUES ('01/03/2020','system1','hard_drive'),
  ('01/04/2020','system2','memory'),
  ('01/10/2020','system2','processor'),
  ('01/14/2020','system3','hard drive'),
  ('01/10/2020','system2','processor'),
  ('01/20/2020','system4','hard drive'),
  ('01/24/2020','system5','memory'),
  ('01/29/2020','system6','hard drive'),
  ('02/02/2020','system7','motherboard'),
  ('02/04/2020','system8','graphics card'),
  ('02/02/2020','system7','motherboard'),
  ('02/08/2020','system9','hard drive');

Run the following SQL query:

SELECT * FROM dbtable
ORDER BY date_x;
MySQL output for the select from table command

Finding Duplicates in MySQL

Find Duplicate Values in a Single Column

Use the GROUP BY function to identify all identical entries in one column.  Follow up with a COUNT() HAVING function to list all groups with more than one entry.

SELECT
  test,
  COUNT(test)
FROM 
  dbtable
GROUP BY test
HAVING COUNT(test) > 1;

Find Duplicate Values in Multiple Columns

You may want to list exact duplicates, with the same information in all three columns.

SELECT
  date_x, COUNT(date_x),
  system_x, COUNT(system_x),
  test, COUNT(test)
FROM
  dbtable
GROUP BY
  date_x,
  system_x,
  test
HAVING COUNT(date_x)>1
  AND COUNT(system_x)>1
  AND COUNT(test)>1;
Select multiple columns and find exact duplicates in MySQL database

This query works by selecting and testing for the >1 condition on all three columns. The result is that only rows with duplicate values are returned in the output.

Check for Duplicates in Multiple Tables With INNER JOIN

Use the INNER JOIN function to find duplicates that exist in multiple tables.

Sample syntax for an INNER JOIN function looks like this:

SELECT <em>column_name</em>
  FROM <em>table1</em>
  INNER JOIN <em>table2</em>
  ON <em>table1.column_name</em> = <em>table2.column name</em>;

To test this example, you need a second table that contains some information duplicated from the sampledb table we created above.

SELECT dbtable.<em>date_x</em>
  FROM dbtable
  INNER JOIN <em>new_table</em>
  ON dbtable.date_x = new_table.date_x;

This will display any duplicate dates that exist between the existing data and the new_table.

output of the MySQL inner join command to check for duplicate values

Note: The DISTINCT command can be used to return results while ignoring duplicates. Also, newer versions of MySQL use a strict mode, which can affect operations that attempt to select all columns. If you get an error, make sure that you’re selecting specific individual columns.

Conclusion

Now you can check for duplicates in MySQL data in one or multiple tables and understand the INNER JOIN function. Make sure you created the tables correctly and that you select the right columns.

Now that you have found duplicate values, learn how to remove MySQL duplicate rows.

Was this article helpful?
YesNo
Goran Jevtic
Goran combines his passions for research, writing and technology as a technical writer at phoenixNAP. Working with multiple departments and on a variety of projects, he has developed extraordinary understanding of cloud and virtualization technology trends and best practices.
Next you should read
How to Install MySQL on CentOS 8
February 17, 2020

MySQL, the most widely used relational database management system can be installed on CentOS 8 from the...
Read more
How to Improve MySQL Performance With Tuning
January 15, 2020

The performance of MySQL databases is an essential factor in the optimal operation of your server. Make sure...
Read more
How To Remove or Delete a MySQL User Account
December 1, 2019

This article covers the basics of using the DROP USER statement used to delete MySQL user account. Follow the...
Read more
How to Install MySQL 8.0 in Ubuntu 18.04
December 12, 2018

MySQL is an open-source relational database server tool for Linux operating systems. It is widely used in...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.