How to insert if not exist in MySQL?

There are several ways to insert a row of data to a table while determining whether that row is new, or already existed.

1. Using INSERT IGNORE

Let’s have a basic insert query:

INSERT INTO companies
    (id, full_name, address, phone_number)
VALUES
    (1, 'Apple', '1 Infinite Loop, Cupertino, California', 18002752273);

In this case, id is the UNIQUE PRIMARY_KEY in the table companies. If this is a new row, so the query above will do the job of adding it to the table.

If it already existed, in which table companies has a record with the id = 1, the query above will return the error:

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

So we can use INSERT IGNORE:

INSERT IGNORE INTO companies
    (id, full_name, address, phone_number)
VALUES
    (1, 'Apple', '1 Infinite Loop, Cupertino, California', 18002752273);

If the row is new, it will be added. Otherwise, the query will ignore the insert attempt and return this result:

Query OK, 0 rows affected (0.00 sec)

2. Using INSERT ... ON DUPLICATE KEY UPDATE

Here is how the query looks like:

INSERT INTO companies
    (id, full_name, address, phone_number)
VALUES
    (1, 'Apple', '1 Infinite Loop, Cupertino, California', 18002752273)
ON DUPLICATE KEY UPDATE
    full_name = 'Apple'
    address = '1 Infinite Loop, Cupertino, California'
    phone_number = 18002752273;

If the row existed already, the INSERT ... ON DUPLICATE KEY UPDATE statement will update the values of the row.

3. Using REPLACE

We can use the REPLACE statement:

REPLACE INTO companies
    (id, full_name, address, phone_number)
VALUES
    (1, 'Apple', '1 Infinite Loop, Cupertino, California', 18002752273);

Using this query, if the row doesn’t exist, it will be created just like how the INSERT statement does, but if the record exists, it will be overwritten. In many cases, this might not be the optimal solution since it involves deleting while it’s better to just skip it. The INSERT ... ON DUPLICATE KEY UPDATE statement only updates the value, not deletes, thus is a little bit safer to use. And INSERT IGNORE is recommended.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 