SQL Last Insert ID always return 0

Question

query, err := dbSQL.Prepare(`
        INSERT INTO class (
            subject_id,
            class_name,
            createAt,
            updatedAt,
        )
        VALUES (?, ?, NOW(), NOW())
    `)

checkError(err)

result, err := query.Exec(
    subjectID,
    className,
)

checkError(err)

returnID, err := result.LastInsertId()

checkError(err)

dbSQL.Commit()

returnID always return 0

Note: my auto increment is disable.

Whats wrong? Is it because dbSQL.Commit() is below result.LastInsertId() or auto increment off making chaos?

*"With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first **automatically generated value successfully inserted for an AUTO_INCREMENT column** as a result of the most recently executed INSERT statement. "* -- If *no* value was generated for an AUTO_INCREMENT column during the insert then LAST_INSERT_ID() will return 0. — mkopriva, Oct 04 '21 at 06:06

score 1 · Accepted Answer · answered Oct 04 '21 at 06:23

It was mentioned in this thread, with, as advice:

It is better idea to use an SP and in this SP return the last id

(SP: Stored Procedure)
Example in Go.

Example: "How to get lastInsertID when executing a Stored Procedure on a mySQL Server"

That would be safer than relying on a direct call, as explained here.

SQL Last Insert ID always return 0

1 Answers1

Linked