-
Notifications
You must be signed in to change notification settings - Fork 2.3k
Confusion about LastInsertId() #1717
-
Hello @methane can you kindly clarify how this driver handles LastInsertId since it seems to work differently from how MySQL documents it.
Note: I have confirmed that my database works as documented below when I run the queries directly.
1. Bulk Inserts
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.
However, when I run the queries using this driver, the LastInsertId() returns THE LAST inserted row.
2. When the ID is supplied
The value of LAST_INSERT_ID() is not changed if you set the AUTO_INCREMENT column of a row to a non-"magic" value (that is, a value that is not NULL and not 0).
However, when I run the queries using this driver and call result.LastInsertId(), the correct ID is returned even if I supplied it myself.
Beta Was this translation helpful? Give feedback.
All reactions
However, when I run the queries using this driver, the
LastInsertId()returns THE LAST inserted row.
REALLY?
// https://github.com/go-sql-driver/mysql/discussions/1717 package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" ) // Use example in https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id func main() { db, err := sql.Open("mysql", "root:my-secret-pw@tcp(127.0.0.1:3306)/test") if err != nil { panic(err) } res, err := db.Exec("INSERT INTO t VALUES (NULL, 'Bob')") if err != nil { panic(err) } last, err := res.LastInsertId() fmt.Printf("last insert id: %d\n", last) res, err = db.Exec("INSERT INTO t VALU...
Replies: 1 comment 1 reply
-
However, when I run the queries using this driver, the
LastInsertId()returns THE LAST inserted row.
REALLY?
// https://github.com/go-sql-driver/mysql/discussions/1717 package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" ) // Use example in https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id func main() { db, err := sql.Open("mysql", "root:my-secret-pw@tcp(127.0.0.1:3306)/test") if err != nil { panic(err) } res, err := db.Exec("INSERT INTO t VALUES (NULL, 'Bob')") if err != nil { panic(err) } last, err := res.LastInsertId() fmt.Printf("last insert id: %d\n", last) res, err = db.Exec("INSERT INTO t VALUES (NULL, 'Alice'),(NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa')") if err != nil { panic(err) } last, err = res.LastInsertId() fmt.Printf("last insert id: %d\n", last) rows, err := db.Query("SELECT id, name FROM t") if err != nil { panic(err) } defer rows.Close() for rows.Next() { var id int var name string if err := rows.Scan(&id, &name); err != nil { panic(err) } fmt.Printf("id: %d, name: %s\n", id, name) } if err := rows.Err(); err != nil { panic(err) } }
$ go run q1717.go
last insert id: 1
last insert id: 2
id: 1, name: Bob
id: 2, name: Alice
id: 3, name: Mary
id: 4, name: Jane
id: 5, name: Lisa
However, when I run the queries using this driver and call result.LastInsertId(), the correct ID is returned even if I supplied it myself.
This driver doesn't query SELECT LAST_INSERT_ID(). This driver just returns what MySQL protocol returns.
So you should read the mysql_insert_id, not LAST_INSERT_ID().
Another difference from mysql_insert_id() is that LAST_INSERT_ID() is not updated if you set an AUTO_INCREMENT column to a specific nonspecial value.
https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_last-insert-id
Beta Was this translation helpful? Give feedback.
All reactions
-
You're correct. Bulk inserts return the first inserted row. I messed up with my testing code in transactions.
Thanks for pointing me to the correct documentation, it all makes sense now.
Beta Was this translation helpful? Give feedback.