SoFunction
Updated on 2025-04-14

Several methods to implement MySQL INSERT statement to insert when the record does not exist

In MySQL, there are several ways to implement the "insert when the record does not exist". Here are a few commonly used solutions:

Use INSERT IGNORE

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Features:

1. If the record already exists (based on primary key or unique index), the insert operation is ignored

2. No error will be reported, but a warning will be returned

3. Simple and easy to use, but it is impossible to know whether the insertion is successful or ignored

Use ON DUPLICATE KEY UPDATE

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2);

Features:

1. If the record already exists, perform the UPDATE operation

2. Can specify which fields to update

3. Return the number of affected rows (1 means insertion, 2 means update)

Using REPLACE INTO

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Features:

1. If the record already exists, delete the old record first and then insert the new record

2. The two operations DELETE and INSERT will be triggered

3. The auto-increasing ID will change (scenarios where the ID needs to be kept unchanged are not recommended)

Use NOT EXISTS subquery

INSERT INTO table_name (column1, column2, ...)
SELECT value1, value2, ...
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM table_name 
    WHERE unique_column = unique_value
);

Features:

1. More flexible judgment conditions

2. Can be judged based on non-unique keys

3. The syntax is slightly complicated

Example comparison

Suppose there is a user table users, the primary key is id, and username has unique constraints:

-- method1: INSERT IGNORE
INSERT IGNORE INTO users (username, email) VALUES ('john', 'john@');

-- method2: ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email) VALUES ('john', 'john@')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- method3: REPLACE INTO
REPLACE INTO users (username, email) VALUES ('john', 'john@');

-- method4: NOT EXISTS
INSERT INTO users (username, email)
SELECT 'john', 'john@'
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE username = 'john'
);

Practical advice

  • If you just avoid repeated insertions, use INSERT IGNORE
  • If you need to update existing records, use ON DUPLICATE KEY UPDATE
  • Avoid using REPLACE INTO unless you have special requirements (because it deletes the records first)
  • Use the NOT EXISTS method when judging complex conditions Note: These methods rely on primary keys or unique constraints on the table to work properly.

Summarize

This is the article about several methods of inserting MySQL INSERT statement when implementing records without exists. For more related content to insert MySQL INSERT when records do not exist, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!