英文:
go mysql LAST_INSERT_ID() returns 0
问题
我有一个MySQL数据库，我需要使用Go程序添加记录，并需要检索最后添加记录的ID，以将该ID添加到另一个表中。
当我在MySQL Workbench中运行插入INSERT INTO table1 values("test",1); SELECT LAST_INSERT_ID()时，它会返回最后一个ID，这个ID是自动递增的，没有问题。
然而，如果我运行我的Go代码，它总是打印0。代码如下：
_, 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)
我尝试了这个变化来进一步缩小问题范围：err = db_client.DBClient.QueryRow("SELECT id from table1 where name=\"pleasejustwork\";").Scan(&id)，它完全正常；Go返回了实际的ID。
为什么使用LAST_INSERT_ID()不起作用？
我是Go的新手，所以如果我犯了一些愚蠢的错误导致这个错误，请不要对我太苛刻:D
提前谢谢你。
英文:
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); in MySQL Workbench, it returns the last id, which is auto incremented, with no issues.
SELECT LAST_INSERT_ID()
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 
Thank you in advance.
答案1
得分: 2
MySQL协议在响应INSERT语句时返回LAST_INSERT_ID()的值。而且，golang驱动程序暴露了这个返回值。所以，你不需要额外的往返来获取它。这些ID值通常是无符号的64位整数。
尝试像这样的代码：
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)
我承认我不确定为什么你的代码没有起作用。每当你成功执行一条单行的INSERT语句时，同一数据库连接上的下一条语句总是可以访问到有用的LAST_INSERT_ID()值。这一点无论你是否使用显式事务都是成立的。
但是，如果你的INSERT不成功，你必须将最后插入的ID值视为不可预测的。（这是一个技术术语，意思是“垃圾”，垃圾，废物，basura等）
英文:
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.)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论