MySQL upsert: when INSERT hits a UNIQUE/PRIMARY conflict, update specified columns instead—common for counters and idempotent sync.
Upsert pattern
CREATE TABLE stats (
metric VARCHAR(64) PRIMARY KEY,
value BIGINT NOT NULL DEFAULT 0
);
INSERT INTO stats (metric, value) VALUES ('page_views', 1)
ON DUPLICATE KEY UPDATE value = value + 1;Practice: Run on database practice in mysql client.
Requires unique key
Conflict detection uses PRIMARY or UNIQUE index—design keys intentionally.
Compare Postgres
Postgres uses ON CONFLICT … DO UPDATE—same idea, different syntax on PostgreSQL.
Important interview questions and answers
- Q: ON DUPLICATE KEY vs REPLACE?
A: REPLACE deletes+inserts—triggers and FKs may suffer; upsert is gentler. - Q: Affected rows count?
A: Can be 1 or 2 on upsert—ORMs may interpret differently.
Self-check
- What index does upsert need?
- Increment counter on duplicate metric name.
Pitfall: Upsert without UNIQUE key silently inserts duplicates.
Interview prep
- Requires?
PRIMARY or UNIQUE key conflict to trigger update clause.
- Postgres equivalent?
ON CONFLICT DO UPDATE on PostgreSQL.