How To Avoid Inserting Duplicate Records in MySQL

Sometimes you may need to prevent duplicate insert while adding rows to MySQL table. You can easily do this using INSERT IGNORE statement. In this article, we will look at how to avoid inserting duplicate records in MySQL.

How To Avoid Inserting Duplicate Records in MySQL

Here are the steps to avoid inserting duplicate records in MySQL. There are two parts to keep in mind. First, you need to create a UNIQUE column index for your table. Next, you need to insert data using INSERT IGNORE to avoid duplicate records.

Let us say you have the following table sales(id, order_date, amount)

mysql> create table sales(
          id int, 
          order_date date, 
          amount int
          );

Also read : How to Get Multiple Counts in MySQL

1. Create Unique Index

We add a UNIQUE index for id column using ALTER TABLE statement.

Here is the syntax to create UNIQUE index in MySQL.

alter table table_name ADD UNIQUE INDEX(
        column_name1, 
        column_name2,
        ...
        column_nameN
        );

Here is the SQL query to add UNIQUE index to id column in sales table.

mysql> alter table sales ADD UNIQUE INDEX(id);

Also read : How to Convert datetime to UTC in MySQL

2. Insert data using INSERT IGNORE

Next, we will insert data using INSERT IGNORE.

Before we proceed, let us look at what is INSERT IGNORE in MySQL and how does INSERT IGNORE work.

What is INSERT IGNORE?

INSERT IGNORE is a command that forces MySQL to ignore errors when you insert data into a MySQL table.

Also read : How to Get Current Date and Time in MySQL

How does INSERT IGNORE work?

In our case, we have already created a UNIQUE index for id column. If we use INSERT statement to add data with duplicate rows, MySQL will throw an error and stop query execution after the first duplicate row.

Instead, when we use INSERT IGNORE, MySQL will silently discard the insertion of duplicate row without generating an error, and continue query execution.

So if a record does not contain duplicate data, MySQL will insert it as usual. If it is a duplicate record, then MySQL will simply ignore it.

Also read : How to Group By Month in MySQL

Here is the SQL query to insert data without duplicates in sales table.

mysql> mysql> insert ignore into sales(id,order_date,amount)
     values(1, '2021-01-01', 250),
     (1, '2021-01-01', 250),
     (2, '2021-01-02', 200),
     (3, '2021-01-03', 150),
     (2, '2021-01-02', 200);
Query OK, 3 rows affected (0.10 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2021-01-01 |    250 |
|    2 | 2021-01-02 |    200 |
|    3 | 2021-01-03 |    150 |
+------+------------+--------+

As you can see, MySQL avoids inserting duplicate rows, without generating any error.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!