go mysql LAST_INSERT_ID() returns 0-CodePudding

I have this MySQL database where I need to add records with a go program and need to retrieve the id of the last added record, to add the id to another table.

When i run insert INSERT INTO table1 values("test",1); SELECT LAST_INSERT_ID() in MySQL Workbench, it returns the last id, which is auto incremented, with no issues.

If I run my go code however, it always prints 0. The code:


    _, err := db_client.DBClient.Query("insert into table1 values(?,?)", name, 1)
    var id string
    err = db_client.DBClient.QueryRow("SELECT LAST_INSERT_ID()").Scan(&id)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println("id: ", id)

I tried this variation to try to narrow down the problem scope further: err = db_client.DBClient.QueryRow("SELECT id from table1 where name=\"pleasejustwork\";").Scan(&id), which works perfectly fine; go returns the actual id.

Why is it not working with the LAST_INSERT_ID()?

I'm a newbie in go so please do not go hard on me if i'm making stupid go mistakes that lead to this error :D

Thank you in advance.

CodePudding user response：

The MySQL protocol returns LAST_INSERT_ID() values in its response to INSERT statements. And, the golang driver exposes that returned value. So, you don't need the extra round trip to get it. These ID values are usually unsigned 64-bit integers.

Try something like this.

    res, err := db_client.DBClient.Exec("insert into table1 values(?,?)", name, 1)
     if err != nil {
        panic (err.Error())
    }
    id, err := res.LastInsertId()
    if err != nil {
        panic (err.Error())
    }
    fmt.Println("id: ", id)

I confess I'm not sure why your code didn't work. Whenever you successfully issue a single-row INSERT statement, the next statement on the same database connection always has access to a useful LAST_INSERT_ID() value. This is true whether or not you use explicit transactions.

But if your INSERT is not successful, you must treat the last insert ID value as unpredictable. (That's a technical term for "garbage", trash, rubbish, basura, etc.)