c# - MySQL 的 LAST_INSERT_ID() 问题

Question

我在使用 LAST_INSERT_ID() 时遇到问题。

每次使用它时，我都需要打开和关闭一个新连接。这大大减慢了我的应用程序。我需要做很多插入语句并为每个他插入的 id 获取都在同一个连接上!如何获取同一连接上最后插入的 ID对于来自特定表的 MySQl、ODBC 和 C# 没有并发问题？

这是我的代码:

#region Private Variables
private static DataAccess _DataAccess = null;
private static object _SyncLock = new object();
private OdbcConnection myConnection = null;
#endregion

#region Instance Method
public static DataAccess Instance
{
    get
    {
        lock (_SyncLock)
        {
            if (_DataAccess == null)
                _DataAccess = new DataAccess();
            return _DataAccess;
        }
    }
}
#endregion

#region Constractors
public DataAccess()
{
    myConnection = new OdbcConnection(stringConnDB);
    myConnection.Open();
}
#endregion

Answer 1

Every time I use it i need to open and close a new connection

不，您不需要新的连接。您应该在插入语句之后的同一连接上使用它。

来自manual :

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by [LAST_INSERT_ID] to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.