$ go run q1717.go\nlast insert id: 1\nlast insert id: 2\nid: 1, name: Bob\nid: 2, name: Alice\nid: 3, name: Mary\nid: 4, name: Jane\nid: 5, name: Lisa\n\n\nHowever, when I run the queries using this driver and call result.LastInsertId(), the correct ID is returned even if I supplied it myself.
\n
This driver doesn't query SELECT LAST_INSERT_ID(). This driver just returns what MySQL protocol returns.
\nSo you should read the mysql_insert_id, not LAST_INSERT_ID().
\n","upvoteCount":1,"url":"https://github.com/go-sql-driver/mysql/discussions/1717#discussioncomment-13176590"}}}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.
\n
\nhttps://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_last-insert-id
Confusion about LastInsertId() #1717
-
|
Hello @methane can you kindly clarify how this driver handles Note: I have confirmed that my database works as documented below when I run the queries directly. 1. Bulk Inserts
However, when I run the queries using this driver, the 2. When the ID is supplied
However, when I run the queries using this driver and call |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 1 reply
-
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)
}
}
This driver doesn't query
|
Beta Was this translation helpful? Give feedback.
-
|
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.
REALLY?