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?

VonC_6309 · Answer

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 Answers