MySQL c连接器如何从插入查询中检索自动增量密钥

Question

我用的是mysql C连接器，我有一个表：

CREATE TABLE some_table 
(
    id INT NOT NULL AUTO_INCREMENT, 
    col1 INT, 
    col2 INT,
    PRIMARY KEY ( id )
);

要在查询中插入多条记录，我使用：

INSERT INTO some_table
    (col1, col2)
VALUES
    (0, 1),
    (2, 3),
    (4, 5);

我的问题是：在插入之后，我想检索所有自动生成的ID。我是否可以在不创建另一个查询的情况下使用c连接器中的函数？

例如，在JDBC中，可以使用以下方法检索自动增量列值。

stmt.executeUpdate(
        "INSERT INTO autoIncTutorial (dataField) "
        + "values ('Can I Get the Auto Increment Field?')",
        Statement.RETURN_GENERATED_KEYS);

//
// Example of using Statement.getGeneratedKeys()
// to retrieve the value of an auto-increment
// value
//

int autoIncKeyFromApi = -1;

rs = stmt.getGeneratedKeys();

if (rs.next()) {
    autoIncKeyFromApi = rs.getInt(1);
} else {

    // throw an exception from here
}

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-last-insert-id.html

任何c连接器替代品？

谢啦

Answer 1

去年我也遇到了同样的问题。解决方案是使用内置的LAST_INSERT_ID（）。接下来，我更改了入门示例2，以演示如何使用它：

    //previous variable declarations and initialisation similar to the original example
    driver = get_driver_instance();
    con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
    con->setSchema("test_schema");

    con->setAutoCommit(false);

    stmt = con->createStatement();
    stmt->execute("DROP TABLE IF EXISTS tbl__test1");
    stmt->execute("DROP TABLE IF EXISTS tbl_test2");

    const string createTbl1Statement = "CREATE TABLE `tbl__test1` ("
            "`id` int(11) NOT NULL AUTO_INCREMENT,"
            "`col_value` varchar(45) DEFAULT NULL,"
            "PRIMARY KEY (`id`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";

    const string createTbl2Statement = "CREATE TABLE `tbl_test2` ("
            "`id` int(11) NOT NULL AUTO_INCREMENT,"
            "`tbl_test1_id` int(11) NOT NULL,"
            "`col_value` varchar(45) DEFAULT NULL,"
            "PRIMARY KEY (`id`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";

    stmt->execute(createTbl1Statement);
    stmt->execute(createTbl2Statement);

    pstmt = con->prepareStatement(
            "INSERT INTO tbl__test1(col_value) VALUES ('abcde')");
    pstmt->executeUpdate();
    delete pstmt;

    stmt->execute("SET @lastInsertId = LAST_INSERT_ID()");
    delete stmt;

    const string insertTbl2 = "INSERT INTO tbl_test2(tbl_test1_id, col_value)" 
            " VALUES (@lastInsertId, '1234')";

    pstmt = con->prepareStatement(insertTbl2);
    pstmt->executeUpdate();
    delete pstmt;

    con->commit();

    delete con;
    //remain code is like the example 2 from mysql site

关于调用LAST_INSERT_ID（）的安全性，如mysql文档中所述：

生成的ID按每个连接在服务器中维护。这意味着函数返回给给定客户端的值是为影响该客户端AUTO_INCREMENT列的最近语句生成的第一个AUTO_INCREMENT值。该值不能被其他客户端影响，即使他们自己生成AUTO_INCREMENT值。此行为确保每个客户端都可以检索自己的ID，而无需担心其他客户端的活动，也无需锁或事务。

编辑：

正如这里给出的：

LAST_INSERT_ID（）不带任何参数，返回一个64位的值，该值表示作为最近执行的INSERT语句的结果，为自动增量列成功插入的第一个自动生成的值。

因此，LAST_INSERT_ID返回最后生成的ID，而不管插入新行的是哪个表。如果需要插入几行，只需在插入每一行后立即调用LAST_insert_ID，这样就可以获得密钥。

在下面的代码中，它被插入表1中的一行，获取生成的键（返回'1'），而该键用于关联表2中的插入新闻2行。然后在表1中再次插入一个新行，再次获取生成的键（返回“2”），并在表2中再次插入两个新闻行：

#include <stdlib.h>
#include <iostream>

#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

using namespace std;

int main(void) {
    cout << endl;
    cout << "Let's have MySQL count from 10 to 1..." << endl;

    try {
        sql::Driver *driver;
        sql::Connection *con;
        sql::Statement *stmt;
        sql::PreparedStatement *pstmt1;
        sql::PreparedStatement *pstmt2;

        driver = get_driver_instance();
        con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
        con->setSchema("test_schema");

        con->setAutoCommit(false);

        stmt = con->createStatement();
        stmt->execute("DROP TABLE IF EXISTS tbl__test1");
        stmt->execute("DROP TABLE IF EXISTS tbl_test2");

        const string createTbl1Statement = "CREATE TABLE `tbl__test1` ("
            "`id` int(11) NOT NULL AUTO_INCREMENT,"
            "`col_value` varchar(45) DEFAULT NULL,"
            "PRIMARY KEY (`id`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";

        const string createTbl2Statement = "CREATE TABLE `tbl_test2` ("
            "`id` int(11) NOT NULL AUTO_INCREMENT,"
            "`tbl_test1_id` int(11) NOT NULL,"
            "`col_value` varchar(45) DEFAULT NULL,"
            "PRIMARY KEY (`id`)"
            ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";

        stmt->execute(createTbl1Statement);
        stmt->execute(createTbl2Statement);

        pstmt1 = con->prepareStatement(
            "INSERT INTO tbl__test1(col_value) VALUES (?)");

        pstmt1->setString(1, "abcde");
        pstmt1->executeUpdate();

        stmt->execute("SET @lastInsertId = LAST_INSERT_ID()");

        const string insertTbl2 =
            "INSERT INTO tbl_test2(tbl_test1_id, col_value)"
                    " VALUES (@lastInsertId, ?)";
        pstmt2 = con->prepareStatement(insertTbl2);

        pstmt2->setString(1, "child value 1");
        pstmt2->executeUpdate();

        pstmt2->setString(1, "child value 2");
        pstmt2->executeUpdate();

        pstmt1->setString(1, "xpto");
        pstmt1->executeUpdate();

        stmt->execute("SET @lastInsertId = LAST_INSERT_ID()");

        pstmt2->setString(1, "child value 3");
        pstmt2->executeUpdate();

        pstmt2->setString(1, "child value 4");
        pstmt2->executeUpdate();

        con->commit();

        delete stmt;
        delete pstmt1;
        delete pstmt2;

        delete con;

    } catch (sql::SQLException &e) {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
        cout << "# ERR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    }

    cout << endl;

    return EXIT_SUCCESS;
}

结果是表1中的2行：

和表2中的4行，每一行都与表1中的键正确关联：

因此，关键点是在插入新行后调用最后一个_INSERT_ID（），插入所需的生成键。