Last updated on August 5th, 2024 at 06:32 am
MySQL is a popular database used by millions of websites and apps. It is used for storing and managing transactions. While doing so, often you may need to prevent duplicate insert while adding rows to MySQL table. If you have duplicate records in your table, then it can harm your data quality and reporting. In fact, if you have duplicate rows in your sales table, then it can lead to reporting inflated sales numbers. Therefore, it is essential to prevent inserting duplicate entries in your table. Once a duplicate entry has been created it is very difficult to find and remove duplicate rows. You can easily avoid duplicate rows 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
There are a couple of different ways to prevent inserting duplicate rows in MySQL. We will look at both these methods one by one. Here are the steps to avoid inserting duplicate records in MySQL. These are very useful especially when you insert multiple rows in table.
1. Using INSERT IGNORE
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.
Create Unique Index
Let us say you have the following table sales(id, order_date, amount)
mysql> create table sales(
id int,
order_date date,
amount int
);
We add a UNIQUE index for id column using ALTER TABLE statement. This will ensure that all values of id column are unique. If you try to insert any row with duplicate id value, MySQL will immediately give an error and stop the insertion.
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);
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. So if you try to insert a row with duplicate primary key or duplicate values of unique columns, then MySQL will simply prevent the execution and give a warning instead of showing an error message. This is useful if you are, say, inserting many rows with some of them containing duplicate values. In this case, MySQL will simply drop those rows with duplicate primary key and insert rest of the rows.
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.
Here is the SQL query to insert data without duplicates in sales table.
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.
2. Using On Duplicate Key
In this case, we simply add a ‘on duplicate key update’ clause at the end of plain INSERT statement. This clause tells MySQL what to do in case it encounters a duplicate primary key value during insertion.
Let us say you have the following table sales(id, order_date, amount) with id column as primary key.
mysql> create table sales(
id int primary key,
order_date date,
amount int
);
Here is a simple example. We basically specify that when MySQL encounters a duplicate primary key value, it should simply update it by overwriting its column values, effectively dropping the duplicate row.
mysql> insert 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)
ON DUPLICATE KEY
UPDATE order_date=order_date, amount=amount;
Query OK, 3 rows affected (0.01 sec)
Records: 5 Duplicates: 0 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, in this case too, only 3 unique rows were inserted and the 2 duplicate rows were not inserted.
Conclusion
In this article, we have learnt how to prevent inserting duplicate rows in table using INSERT IGNORE statement. It is much easier than finding duplicate rows in table and deleting them after insertion. This solution basically omits inserting rows with duplicate primary keys or columns with UNIQUE constraint. Instead of throwing an error, it will silently omit the record from insertion and proceed with the next insertion. This is really useful if you have a high transaction database with tons of insertions every moment.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read :
How to Get Multiple Counts in MySQL
How to Convert datetime to UTC in MySQL
How to Get Current Date and Time in MySQL
How to Group By Month in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.