Repository navigation
Add support for accessing LastInsertId and RowsAffected for all statements in a batch #1261
Description
Issue description
When calling Exec() with multiple statements, we'd like to be able to access LastInsertId and RowsAffected for all statements, not just the last one.
The motivating use case is performing batches of UPDATE statements with minimal roundtrips, while determining the result of each separately.
Example code
My PR to implement this exposes additional methods on mysqlResult, which is accessible when querying with sql.Conn.Raw() using a new mysql.Result interface:
conn, _ := db.Conn(ctx)
conn.Raw(func(conn interface{}) error {
ex := conn.(driver.Execer)
res, _ := ex.Exec(`
UPDATE point SET x = 1 WHERE y = 2;
UPDATE point SET x = 2 WHERE y = 3;
`, nil)
log.Print(res.(mysql.Result).AllRowsAffected()) // eg. {5, 10}
})It looks like exposing the same functionality for sql.Stmt isn't possible yet, since it has no equivalent to sql.Conn.Raw().
Reacted by syumai
Activity
mherr-google commentedon Aug 31, 2021
I have a PR for this, will mail shortly.
jaysonhurd commentedon Nov 9, 2021
I am also having the same issue with v1.6.0. Both RowsAffected() and LastInsertId() return 0 even if the update statement succeeds. Is there any workaround for this?
gbenroscience commentedon Jul 18, 2022
I just observed this issue now in
RowsAffected()v1.6.0 also. In my own case, it occurred on a PreparedStatement, and the statements were not batchedmethane commentedon Jun 17, 2023
Fixed by #1309