How To Insert Row if Not Exists in MySQL?

How to Insert Row if Not Exists in MySQL?

When working with MySQL insert if not exists, it's often necessary to insert a new row into a table only if the row doesn't already exist. It is particularly important when dealing with tables that have unique constraints on one or more columns.

Syntax:

This situation arises in various use cases, such as adding a new user to a users table, where we want to ensure that there isn't already a user with the same username or email address. In such a scenario, inserting a duplicate user can lead to data inconsistency, which is not desirable.

Use the INSERT IGNORE Statement

The use of the INSERT statement is obvious in most of the scenarios but there's no way to handle the errors with it. Therefore, if a duplicate key violation occurs (primary key or unique key constraint violation), then MySQL returns an error and the insertion fails. The entire statement rolls back and no rows will be inserted in response to our query.

Query: Suppose we have a table name students which has been created with the following statement:

Output:

Now, let us try to insert some new data into the table but with an id that already exists in the table.

Query:

Output:

And indeed, the insertion failed with a duplicate key violation error.

Contrarily, the use of the INSERT IGNORE statement helps us avoid duplicate key violations. If we try to insert a row with an already existing key, the INSERT IGNORE statement skips the insertion of the row without returning an error and continues inserting the remaining rows, if available.

Query:
Now, what if try the same thing with the INSERT IGNORE statement:

Output:

Since there was already a record with the given id in the statement, MySQL simply skipped the statement and none of the rows were affected.

Query: Let's try to do the same by inserting multiple records:

Output:

Here, the insertion of the row with a duplicate key is ignored and the insertion of other rows will continue.

Use the ON DUPLICATE KEY UPDATE Clause

To apply MySQL INSERT if not exists, the ON DUPLICATE KEY UPDATE is also an alternative. It works by updating a row in a table if it already exists, or inserting a new row if it doesn't exist already. It is commonly used if you want to update certain columns of an existing row or insert a new row with default values if the row doesn't already exist.

The ON DUPLICATE KEY UPDATE clause is added at the end of the INSERT statement. If any row with the duplicate key exists, the specified columns will be updated with the new values. Alternatively, a new row will be inserted with the specified values if MySQL cannot find an already existing row with the same key.

For instance, in the above-created table students we'll try to insert a new row with an already existing id. The ON DUPLICATE KEY UPDATE clause will update the name and age if it finds a record with the same id.

Query:

Output:

Output:

Since there was already an id 1, so it was updated with a new name and age.

Use the REPLACE Statement

In MySQL, the REPLACE statement is utilized to insert a fresh row into a table or to update an existing row with the same primary key or unique index. Essentially, if a row with identical key values already exists, it gets removed first, and the new row is inserted. On the other hand, if there is no row with the same key values, the new row is simply inserted into the table without any deletion.

By simply replacing the INSERT with REPLACE during the insertion of any row, MySQL will be performing the following operations:

• It first tries to find out if there is any existing row in the table. 
• If there's already a row, then it deletes matching data with the DELETE statement and uses a normal INSERT statement to insert a new row. 
• If no matching data exists, the simple INSERT statement will be performed.

Query:

Let's insert a new record that has been already existing in the table.

Output:

As expected, the row with an id of 1 was updated with the new values specified in the REPLACE statement, as a row with this key already existed in the table. A new row would have been inserted with the specified values if the row didn't exist.