英文:
Does Statement.Close affect mysql's thread-safe LAST_INSERT_ID in golang?
问题
我正在使用go语言将新用户插入到MySQL数据库中。在插入用户之前，我会在msg表中保存一些类似于“日志消息”的内容。msg表和user表都有自增字段。为了获取自增字段选择的id，我使用了MySQL的LAST_INSERT_ID()函数。根据在stackoverflow上的许多其他讨论中指出的，这应该是线程安全的，因为它绑定到单个连接。
我想知道在每次stmt.Exec()之后调用stmt.Close()是否会以任何方式改变MySQL的行为（特别是线程安全性）？
stmt, _ := db.Prepare("INSERT INTO msg (message) VALUES(?)")
stmt.Exec(msg)
stmt.Close()
stmt, _ = db.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&msgid)
stmt.Close()
stmt, _ = db.Prepare("INSERT INTO user (msg_id) VALUES(?)")
stmt.Exec(msgid)
stmt.Close()
stmt, _ = db.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&id)
stmt.Close()
英文:
I'm using go to insert a new user into the mysql database. Before inserting the user I save some kind of "log-message" in the msg table. Both tables (msgand user) have auto-increment. In order to receive the id chosen by auto-increment I use mysql's LAST_INSERT_ID() function. This should be thread-safe as pointed out in lots of other discussions on stack overflow, because it's bound to a single connection.
I asked myself if stmt.Close() after every stmt.Exec() will change mysql's behavior (specially thread-safeness) in any way?
stmt, _ := db.Prepare("INSERT INTO msg (message) VALUES(?)")
stmt.Exec(msg)
stmt.Close()
stmt, _ = db.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&msgid)
stmt.Close()
stmt, _ = db.Prepare("INSERT INTO user (msg_id) VALUES(?)")
stmt.Exec(msgid)
stmt.Close()
stmt, _ = db.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&id)
stmt.Close()
答案1
得分: 1
关闭语句并不会关闭数据库连接。对于LAST_INSERT_ID来说，数据库连接才是重要的。除非调用db.Close()，关闭底层连接，否则连接将保持打开状态。
然而，db是一个连接池，所以无论是否关闭连接，不能保证在后续查询中获得相同的连接，除非使用事务。
因此，总结起来，你应该这样做（当然要添加错误处理）：
tx, _ := db.Begin()
stmt, _ := tx.Prepare("INSERT INTO msg (message) VALUES(?)")
stmt.Exec(msg)
stmt.Close()
stmt, _ = tx.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&msgid)
stmt.Close()
_ = tx.Commit()
然而，需要注意的是，更好的方法是使用Result.LastInsertId：
stmt, _ := tx.Prepare("INSERT INTO msg (message) VALUES(?)")
res, _ := stmt.Exec(msg)
stmt.Close()
msgid := res.LastInsertId()
英文:
Closing the statement, as you're doing, does not close the database connection. The database connection is what's important for LAST_INSERT_ID. Unless you call db.Close(), which closes the underlying connection, your connection remains open.
However, db is a pool of connections, so there is no guarantee that you'll get the same connection on subsequent queries, regardless of closing the connection, unless you use a transaction.
So in summary, you should do this (adding error handling, of course)
tx, _ := db.Begin()
stmt, _ := tx.Prepare("INSERT INTO msg (message) VALUES(?)")
stmt.Exec(msg)
stmt.Close()
stmt, _ = tx.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&msgid)
stmt.Close()
_ = tx.Commit()
However, note that a better way to do this is with Result.LastInsertId:
stmt, _ := tx.Prepare("INSERT INTO msg (message) VALUES(?)")
res, _ := stmt.Exec(msg)
stmt.Close()
msgid := res.LastInsertId()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论