← All skills

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.

REPO gengyue-ai/mysql-rowsaffected-behaviorLICENSE MITWHAT WE VERIFIED Reproduced · 2026-11-18
The problem

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.

Repository ↗

Sources