在插入mysql数据库时获取上次插入ID的最可靠方法是什么

Question

I have found two way to get the last insert id

Result, err := s.DB.ExecContext(ctx, query, qp)
if err != nil {
    return 0, err
}
ID, err := Result.LastInsertId()

and another way is to use mysql's

select LAST_INSERT_ID();

How different are these two methods and what should i do in-case the first method throws an error, would it mean my insert failed or would it mean just that method failed to return the id ?

Answer 1

Use Result.LastInsertId(). This is always reliable.

Doing your own SELECT is not reliable, due to the way the Go driver handles pools of connection. If you do, say:

INSERT foo INTO bar ...

in one statement, then later:

SELECT LAST_INSERT_ID();

in another statement, you may get different connections to the database for each statement.

This means your manually-selected ID may correspond with some completely unrelated INSERT statement which just happened to occur on that same connection in the past, or you may get nothing at all.