MySQL RowsAffected is not "matched rows"
UPDATE reports rows changed, not rows matched. The upsert shape if affected == 0 { INSERT } is a duplicate-key bomb, and here is what to do instead.
An UPDATE that writes the same value reports 0 rows, so the "row missing" branch fires INSERT and hits Error 1062.
The shape that breaks looks harmless:
res, _ := db.Exec("UPDATE sites SET site_domain = ? WHERE account_id = ?", domain, accountID)
if n, _ := res.RowsAffected(); n == 0 {
db.Exec("INSERT INTO sites (account_id, site_domain) VALUES (?, ?)", accountID, domain)
// Error 1062 (23000): Duplicate entry '42' for key 'uk_account_id'
}
The row exists. The value simply did not change, so MySQL reports zero affected rows, and the code takes the “row is missing” branch.
Why
RowsAffected() returns rows actually changed. MySQL’s own C API documentation says so: without CLIENT_FOUND_ROWS, the value counts changed rows; with it, matched rows. Go’s driver exposes that flag as clientFoundRows=true in the DSN.
Four fixes
| Fix | Right when | Cost |
|---|---|---|
SELECT COUNT(*) first |
You must tell “missing” from “unchanged” | One extra round trip |
INSERT … ON DUPLICATE KEY UPDATE |
Pure upsert, nothing else | Needs the unique key |
Add updated_at = NOW() |
“Touched” semantics are fine | Writes on every call |
clientFoundRows=true |
Service-wide meaning should be “matched” | Changes every caller, silently |
The fourth is the one that bites twice: it fixes your branch and quietly changes every other RowsAffected() in the process.
Try it
The repo has a four-statement repro with the expected output in the file header. If the first UPDATE reports affected_rows = 1 instead of 0, your session already has CLIENT_FOUND_ROWS on — which is worth knowing before you debug anything else.
What we verified
Repro executed on MySQL 8.4.13 (Ubuntu 26.04, WSL) on 2026-11-18; affected_rows 0/1 and the 1062 error are pasted from that run.
Install
Copy SKILL.md into skills/<category>/mysql-rowsaffected-behavior/, or clone the repo.