TopolearnInsert-or-update is everywhere in web apps, and MySQL's syntax and its side effects differ from other databases.
AUTO_INCREMENT on an integer primary key generates increasing ids. After an insert, LAST_INSERT_ID() returns the id generated in your session (drivers expose it as lastrowid or similar), so concurrent inserts by others don't interfere. For a multi-row insert it returns the first generated id. Ids are not reused after rollbacks or failed inserts, so gaps are normal. Since 8.0 the counter survives restarts.
MySQL's upsert is INSERT ... ON DUPLICATE KEY UPDATE: if the insert would violate a primary or unique key, the existing row is updated instead. Refer to the proposed values with a row alias (8.0.19+); the older VALUES(col) function is deprecated:
INSERT INTO stock (sku, qty) VALUES ('A1', 3) AS new ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;
The affected-rows count tells you what happened: 1 = inserted, 2 = updated, 0 = matched but nothing changed.
Alternatives and their traps:
INSERT IGNOREskips rows that cause errors — but it also turns other errors (bad values, truncation) into warnings, hiding real problems.REPLACEdeletes the old row and inserts a new one: it fires delete triggers, cascades foreign keys and allocates a new AUTO_INCREMENT id. Rarely what you want.
Note that ON DUPLICATE KEY UPDATE can still consume an AUTO_INCREMENT value even when it updates.
Example
CREATE TABLE stock (
id INT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(20) NOT NULL UNIQUE,
qty INT NOT NULL
);
INSERT INTO stock (sku, qty) VALUES ('A1', 5);
SELECT LAST_INSERT_ID(); -- 1
INSERT INTO stock (sku, qty) VALUES ('A1', 3) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty; -- 2 rows affected (update)
INSERT INTO stock (sku, qty) VALUES ('B2', 1) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty; -- 1 row affected (insert)
SELECT id, sku, qty FROM stock ORDER BY id;
-- 1 | A1 | 8
-- 3 | B2 | 1 <- id 2 was consumed by the upsert that updatedRemember
- LAST_INSERT_ID() is per session and safe under concurrency
- ON DUPLICATE KEY UPDATE with a row alias is MySQL's upsert
- Avoid REPLACE and INSERT IGNORE unless you want their side effects
Watch out for
- Using REPLACE as an upsert and silently changing ids and cascading deletes to child rows.