MySQL doesn’t have a statement to insert a new row if the data do not exist.
This is because when you have table column(s) with
PRIMARY KEY or
UNIQUE constraint, then MySQL will throw an error each time your query inserts a new row with duplicate values for those columns.
Still, if you want to perform an insertion only when the data doesn’t already exist, then you need to create a work-around solution with the statements provided by MySQL.
There are three ways you can perform an “insert if not exists” query in MySQL:
- Using the
- Using the
ON DUPLICATE KEY UPDATEclause
- Or using the
Keep in mind that before you create an insert if not exists query, the MySQL table in use must already have one or more column(s) with
PRIMARY KEY or
Without putting a constraint on the column that you want to be distinct, then the queries below won’t work as intended.
Insert a new row if not exists using INSERT IGNORE
INSERT IGNORE statement is commonly used to change the error that MySQL throws when you use a normal
INSERT statement into a warning so that the execution of your query isn’t interrupted.
For example, suppose you have a
users table with the following data:
+---------+------------+-----------+ | user_id | first_name | last_name | +---------+------------+-----------+ | 202 | Thor | Odinson | | 204 | Loki | Laufeyson | +---------+------------+-----------+ 2 rows in set (0.00 sec)
user_id column above is a
PRIMARY KEY field, so MySQL will throw an error if you try to insert a new row with one of the existing
The query above uses the
user_id value of
INSERT INTO users (user_id, first_name, last_name) VALUES (202, "Tony", "Smith");
So MySQL will produce the following error:
ERROR 1062 (23000): Duplicate entry '202' for key 'users.PRIMARY'
INSERT IGNORE statement will cause MySQL to do nothing when the insertion throws an error. If there’s no error, then a new row will be added to the table.
When you run the following query:
INSERT IGNORE INTO users (user_id, first_name, last_name) VALUES (202, "Tony", "Smith");
MySQL will produce the following output:
Query OK, 0 rows affected, 1 warning (0.00 sec)
INSERT IGNORE statement, MySQL will insert a new row only if the values don’t exist in the table.
Update existing row with unique value using ON DUPLICATE KEY UPDATE clause
ON DUPLICATE KEY UPDATE clause allows you to update the row with the new values when a duplicate value is found in the
UNIQUE index or
PRIMARY KEY column.
The clause can be added by the end of a MySQL
INSERT query as shown below:
INSERT INTO users (user_id, first_name, last_name) VALUES (202, "Tony", "Smith") ON DUPLICATE KEY UPDATE first_name = "Tony", last_name = "Smith";
First, MySQL will execute the regular insert query above. When a duplicate value is found, then MySQL will perform an update instead of an insert.
This query is useful if you still want the row to be updated when the unique value already exists.
Using the REPLACE statement for when a unique value already exists.
REPLACE statement is an alternative to the
ON DUPLICATE KEY UPDATE clause.
The statement essentially does two things:
- MySQL will check the existing record to see if a row with the same unique value already exists
- When a row with the same unique value is found, then the row will be deleted using the
DELETEstatement. MySQL will then run the
INSERTstatement to replace the deleted row.
When there’s no match in the existing record, then the
REPLACE statement works like a regular
Here’s an example of the
REPLACE statement in action:
REPLACE INTO users (user_id, first_name, last_name) VALUES (202, "Tony", "Smith");
The row with the
user_id value of
202 will be replaced with the above values if it already exists.
And those are the three ways you can perform a MySQL insert if not exists query.
Personally, I prefer to just do a normal
INSERT query and handle the error thrown by MySQL from the backend side.
try..catch block that you can use to handle errors from MySQL query execution.
But if you must handle the “insert if not exists” logic from the MySQL side, then the three methods above are your best options.
Feel free to modify the queries above for your project 😉