Confusion about LastInsertId()

[stephenafamo]

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.

[methane]

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

[stephenafamo]

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.