How to Insert a Row if Not Exists in MySQL

1. Use REPLACE to Insert if Not Exists in MySQL
2. Use INSERT IGNORE to Insert if Not Exists in MySQL
3. Use INSERT ... ON DUPLICATE KEY UPDATE to Insert if Not Exists in MySQL

This tutorial shows you how to insert a row into a table if it doesn’t exist yet in mySQL.

There are three simple ways to accomplish this problem, by using REPLACE, INSERT IGNORE, or INSERT ... ON DUPLICATE KEY UPDATE.

First, let’s imagine we have a table person structured as such:

CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
address VARCHAR(128) 
);

With the following existing records:

Now let’s get into the actual solutions.

Use REPLACE to Insert if Not Exists in MySQL

What REPLACE does is it overwrites existing records when found; if it doesn’t exist yet, it will insert a new row. It is essentially like UPDATE but with INSERT capabilities.

Let’s provide a sample query to execute REPLACE into John Doe.

REPLACE INTO 'person'
SET id = 1,
name = 'John Doe'`,
age = 28,
address = 'Los Angeles, California'

If John Doe exists, he will be overwritten with the new data; if he doesn’t, it will create a new entry for John Doe.

However, this method isn’t efficient for inserting as it overwrites existing records instead of just skipping it if it is found. Although it gets the job done, it isn’t the best way to do it.

The new result when query selecting John Doe will be as such:

John Doe’s age and address have been modified or updated by the REPLACE script.

Use INSERT IGNORE to Insert if Not Exists in MySQL

We’ll use the same unedited data on the above table for this as well.

On INSERT IGNORE, if the record’s primary key is already present in the database, it will quietly be ignored or skipped.

If we take away the IGNORE keyword, inserting a record with an id or primary key existing will abort the query and show an error message saying that the record with the primary key already exists.

After the INSERT, the IGNORE is a substitute for the abortion because of the error, and instead just continues the query although not inserting a new record.

INSERT IGNORE person
SET 'id' = 4,
age = 1000,
address = 'Chicago'

Based on the table we declared above, a record with an id or primary key 4 already exists (Thor Odinson). Therefore, this query is ignored by the script and thus not updating or inserting a record in the database.

The result if we query Thor in person would be:

The age and the address in the query did not affect the row with id 4 at all.

Use INSERT ... ON DUPLICATE KEY UPDATE to Insert if Not Exists in MySQL

If you use the ON DUPLICATE KEY UPDATE clause and the row you want to insert would is a duplicate in a UNIQUE index or primary key, the row will execute an UPDATE.

This essentially does the same thing REPLACE does.

Let’s say we want to insert a record with id 2

INSERT INTO person (id, name, age, address)
VALUES (1, 'Jane Deer', '33', 'Tallahassee Florida')
ON DUPLICATE KEY UPDATE id = id+1;

The id is incremented to generate a new record instead of updating the existing one.

If we query all the rows of person, it would then result to this:

Jane Deer, instead of replacing Jane Doe, is added as a new record in the table.

The most efficient way to deal with inserting and checking its existence is definitely by using INSERT IGNORE.

However, the two other keywords are also usable on a case-to-case basis, if ever you want to update if a row you want to insert exists, use REPLACE. If you don’t want the row to be modified but really want to insert the new record, use INSERT ... ON DUPLICATE KEY UPDATE.