c++ - last_insert_id 的返回总是正确的吗？

Question

如果我有以下两个功能:

int AccessDb::InsertColValue(string tableName, string col, string val)
{
    try
    {
        sql::Statement *stmt;
        bool ret;

        if ((nomTable != "") && (col != "") && (val != ""))
        {
            string query = "INSERT INTO " + tableName + "(" + col + ") values (";
                    query += val + ");";

            stmt = con->createStatement();
            ret = stmt->execute(query);
        }

        delete stmt;

        return 0;
    }
    catch (sql::SQLException &e)
    {
        return -1;
    }
}

和

long AccessDb::LastInsertId()
{
    try
    {
        sql::Statement *stmt;
        sql::ResultSet *res;

        string query = "SELECT LAST_INSERT_ID() AS LAST_ID";

        stmt = con->createStatement();
        res = stmt->executeQuery(query);

        delete stmt;

        long lastId;
        while (res->next())
        {
            lastId = res->getInt("LAST_ID");
        }

        return lastId;
    }
    catch (sql::SQLException &e)
    {
      return -1;
    }
}

如果我编写以下行并且该 ID 是由数据库自动生成的，我能否确定 LastInsertId() 的返回将始终为我提供正确的 ID？

AccessDb adb; // initialize the connexion with the db
int ret = adb.InsertColValue("people", "name", "John");
if (ret == 0)
    long lastId = adb.LastInsertId();

如果之前的代码同时在其他地方调用，我的 lastId 变量中的值是否会错误？如果是，我是否必须在我的 table 上使用锁定和解锁来避免这种情况或其他解决方案？

Answer 1

文档是这样说的:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function 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.

因此，除非您自己的客户端代码在多个线程之间共享连接(看起来您不是，因为您的代码中没有互斥锁或锁)，您可以确定 SELECT LAST_INSERT_ID() 不是'不要与任何其他连接或客户端混淆。

我找不到 C++ mysql 库的文档，但验证 ret = stmt->execute(query); 在你的 InsertColValue() 中的返回值是什么> function 意味着，这样你就可以确定你无法插入任何东西的唯一可能方法是抛出异常。