How can I do ‘insert if not exists’ in MySQL?

To perform an “insert if not exists” operation in MySQL, you can use several methods depending on your requirements. Below are the most common approaches with detailed explanations and examples:

1. Using INSERT IGNORE

This method ignores duplicate entries if a unique constraint (e.g., primary key or unique index) is violated.

Steps:

1. Ensure a unique constraint exists on the column(s) you want to check for duplicates.
2. Use INSERT IGNORE to add the record. If a duplicate exists, MySQL skips the insertion silently.

Example:

-- Create a table with a unique constraint on `email`
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255)
);

-- Insert a new user only if the email doesn't exist
INSERT IGNORE INTO users (email, name) 
VALUES ('john@example.com', 'John Doe');

Pros:

• Simple syntax.
• Fast for tables with unique constraints.

Cons:

• Requires a unique constraint.
• Silently ignores other errors (e.g., data truncation).

2. Using ON DUPLICATE KEY UPDATE

This method inserts a new record or updates an existing one if a duplicate key is found. To mimic “insert if not exists,” set the values to their current state.

Example:

-- Insert or do nothing on duplicate
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON DUPLICATE KEY UPDATE email = email, name = name;

Pros:

• Works with unique constraints.
• Allows updating existing data if needed.

Cons:

• Slightly more complex syntax.
• Requires a unique constraint.

3. Using WHERE NOT EXISTS Subquery

This method checks for the existence of a record before inserting, without relying on unique constraints (though constraints are still recommended for data integrity).

Example:

-- Insert only if the email doesn't exist
INSERT INTO users (email, name)
SELECT 'john@example.com', 'John Doe'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM users 
  WHERE email = 'john@example.com'
);

Pros:

• No strict need for a unique constraint (but recommended).
• Flexible conditions in the WHERE clause.

Cons:

• Slower for large tables due to subquery evaluation.
• Race conditions may occur without a unique constraint.

Key Notes:

1. Unique Constraints Are Critical: To reliably prevent duplicates, define a UNIQUE constraint or primary key on the column(s) being checked (e.g., email). Without this, all methods may fail under concurrent writes.
2. Performance: INSERT IGNORE and ON DUPLICATE KEY UPDATE are faster than WHERE NOT EXISTS for large datasets.
3. Error Handling: INSERT IGNORE suppresses all errors, not just duplicates. Use with caution.

When to Use Each Method:

Method	Use Case
INSERT IGNORE	Simple inserts where duplicates are rare and a unique constraint exists.
ON DUPLICATE KEY UPDATE	Inserts or updates (upsert) based on duplicates.
WHERE NOT EXISTS	Conditional inserts without unique constraints (not recommended for high concurrency).

By combining these methods with proper database design (e.g., unique constraints), you can efficiently handle “insert if not exists” scenarios in MySQL.