Choosing the Right MySQL Upsert: ON DUPLICATE KEY UPDATE, INSERT IGNORE, or REPLACE

When to Upsert and Which Syntax to Use

When you need to insert data that might already exist, MySQL offers three primary syntaxes: INSERT ... ON DUPLICATE KEY UPDATE, INSERT ... IGNORE, and REPLACE. Each behaves differently under conflict and has distinct performance and data‑consistency implications. This guide helps you pick the right one for a given scenario.

Decision Constraints

• Unique Constraint Presence: All three require a UNIQUE or PRIMARY KEY on the table.
• Need to Update Existing Row? If you must modify columns when a duplicate key is found, ON DUPLICATE KEY UPDATE is the only option.
• Silent Skip? If you simply want to ignore duplicates without touching the row, use INSERT IGNORE.
• Full Row Replacement? When you need to delete the old row and insert a brand‑new one (e.g., resetting AUTO_INCREMENT), REPLACE is appropriate.
• Trigger & Auto‑Increment Behavior:
• Triggers fire on INSERT and UPDATE but not on REPLACE (which issues a delete + insert).
• Using REPLACE can reset the AUTO_INCREMENT counter in some storage engines.

Compact Comparison Table

Feature	INSERT ... ON DUPLICATE KEY UPDATE	INSERT ... IGNORE	REPLACE
Conflict Handling	Update specified columns	Skip row, no update	Delete old row, insert new one
Triggers	Fired on UPDATE	Fired on INSERT (but no row added)	Fired on DELETE + INSERT
AUTO_INCREMENT	Preserved	Preserved	May reset counter (depends on engine)
Performance (typical)	Fastest for updates	Fastest for silent skips	Slowest due to delete + insert
Data Loss Risk	None (updates only)	None	Possible loss if delete fails or triggers misbehave
Use Case Example	Upsert inventory quantities	Log unique events without duplicates	Replace configuration rows that must be fully refreshed

Trade‑Offs Explained

Performance: ON DUPLICATE KEY UPDATE is generally the most efficient because it performs a single UPDATE or INSERT operation. REPLACE forces a DELETE followed by an INSERT, which incurs extra I/O and can trigger cascading deletes if foreign keys exist.

Data Integrity: REPLACE can inadvertently delete rows with foreign key references, leading to constraint violations. It also resets triggers that might enforce business rules on the old row.

Auto‑Increment Behavior: In InnoDB, REPLACE does not reset the counter, but in MyISAM it can. If you rely on a monotonic counter, avoid REPLACE.

Semantic Clarity: Using INSERT IGNORE clearly signals that duplicates are acceptable and should be ignored, improving maintainability.

Concrete Implementation & Validation

1. Create a Test Table

# Run as a user with CREATE privilege
CREATE TABLE inventory (
    sku VARCHAR(20) NOT NULL,
    qty INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (sku)
) ENGINE=InnoDB;

2. Insert a Row

# Insert initial stock
INSERT INTO inventory (sku, qty) VALUES ('ABC123', 100);

3. Upsert with ON DUPLICATE KEY UPDATE

# Increase quantity or insert new SKU
INSERT INTO inventory (sku, qty) VALUES ('ABC123', 50)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

Expected Result: qty becomes 150. Verify with:

SELECT sku, qty FROM inventory WHERE sku = 'ABC123';
-- Should return 150

4. Insert Duplicate with INSERT IGNORE

# Attempt to insert the same SKU again
INSERT IGNORE INTO inventory (sku, qty) VALUES ('ABC123', 200);

Result: No new row is added; qty remains 150. Verify by counting rows:

SELECT COUNT(*) FROM inventory WHERE sku = 'ABC123';
-- Should return 1

5. Replace Existing Row

# Replace the row entirely
REPLACE INTO inventory (sku, qty) VALUES ('ABC123', 300);

Result: The old row is deleted and a new one inserted with qty = 300. Verify that triggers fired (if any) and that the last_updated timestamp has changed.

How to Verify Correctness

1. Run SHOW WARNINGS; after each statement to ensure no errors.
2. Use SELECT COUNT(*) to check row counts before and after.
3. Inspect last_updated to confirm whether an UPDATE or REPLACE occurred.
4. If foreign keys exist, run SELECT * FROM information_schema.key_column_usage WHERE referenced_table_name = 'inventory'; to ensure no violations.

When to Avoid REPLACE

• Tables with many foreign key references (risk of cascading deletes).
• Scenarios where you need to preserve the original AUTO_INCREMENT value.
• When you want to maintain audit trails that rely on UPDATE timestamps.

Practical Checklist

• [ ] Confirm a UNIQUE or PRIMARY KEY exists on the conflict column.
• [ ] Decide if you need to update columns (ON DUPLICATE KEY UPDATE), skip duplicates (IGNORE), or replace entire rows (REPLACE).
• [ ] Consider trigger logic and auto‑increment behavior.
• [ ] Test in a staging environment using the validation steps above.
• [ ] Monitor performance with EXPLAIN to ensure the chosen syntax uses the intended index.

Conclusion

Choosing the correct upsert syntax in MySQL hinges on the conflict resolution you need and the performance impact you can tolerate. For most use cases where you need to modify existing rows, INSERT ... ON DUPLICATE KEY UPDATE is the safest and fastest choice. Use INSERT IGNORE when duplicates are truly acceptable and you want to avoid any update. Reserve REPLACE for rare cases where a full row refresh is required and you can handle its delete+insert semantics.