去年我遇到了同样的问题。解决方法是使用内置函数
LAST_INSERT_ID()。接下来,我将
getting start example 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
关于mysql文档中提到的call LAST_INSERT_ID()的安全性:
生成的ID在服务器上以每个连接为基础进行维护。这意味着函数返回给指定客户端的值是该客户端最近影响AUTO_INCREMENT列的大多数最新语句生成的第一个AUTO_INCREMENT值。即使其他客户端生成自己的AUTO_INCREMENT值,该值也不会受到影响。此行为确保每个客户端都可以检索其自己的ID,而无需担心其他客户端的活动,也无需锁定或事务。
编辑:
如此处所述:
如果没有参数,LAST_INSERT_ID()将返回一个64位值,表示由于最近执行的INSERT语句而成功插入AUTO_INCREMENT列的第一个自动生成的值。
因此,LAST_INSERT_ID返回最后生成的id,而不管新行插入的表格。如果您需要插入多行,请在每次插入行后立即调用LAST_INSERT_ID,以便获取密钥。
在下面的代码中,向表1插入1行,获取生成的键(返回“1”),然后使用该键插入与之关联的表2中的2行新闻。然后再次向表1插入1行,再次获取生成的键(返回“2”),然后再次向表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 rows inserted in table 1](https://istack.dev59.com/HSUgz.webp)
在表格2中有4行,每一行都与表格1中的关键字正确关联:
![4 rows inserted in table 2](https://istack.dev59.com/NzEWT.webp)
因此,关键点是在插入具有所需生成键的新行后调用LAST_INSERT_ID()。