C++ MySQL 常用接口（基于 MySQL Connector/C++）
1. 数据库连接
接口：
sql::mysql::MySQL_Driver *driver;
sql::Connection *con;
作用：
用于创建 MySQL 连接对象。
示例：
driver = sql::mysql::get_mysql_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "user", "password");
释放资源：
delete con;
2. 选择数据库
接口：
con->setSchema("database_name");
作用：
指定要操作的数据库。
示例：
con->setSchema("test_db");
3. 创建 SQL 语句对象
接口：
sql::Statement *stmt;
stmt = con->createStatement();
作用：
创建一个 SQL 语句执行对象。
示例：
stmt = con->createStatement();
stmt->execute("CREATE TABLE IF NOT EXISTS users (id INT, name VARCHAR(50))");
delete stmt;
4. 执行 SQL 语句
接口：
stmt->execute("SQL语句");
stmt->executeQuery("SQL查询语句");
stmt->executeUpdate("SQL更新语句");
作用：
execute()用于执行不返回结果集的 SQL 语句（如CREATE TABLE）。executeQuery()用于执行SELECT查询，返回ResultSet结果集。executeUpdate()用于INSERT、UPDATE、DELETE，返回影响的行数。
示例：
stmt = con->createStatement();
stmt->execute("INSERT INTO users (id, name) VALUES (1, 'Tom')");
sql::ResultSet *res = stmt->executeQuery("SELECT * FROM users");
int rows = stmt->executeUpdate("UPDATE users SET name='Jerry' WHERE id=1");delete res;
delete stmt;
5. 获取查询结果
接口：
sql::ResultSet *res;
res->next();
res->getInt("column_name");
res->getString("column_name");
作用：
获取查询结果，并读取列值。
示例：
sql::ResultSet *res = stmt->executeQuery("SELECT * FROM users");
while (res->next()) {std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name") << std::endl;
}
delete res;
6. 使用预处理语句
接口：
sql::PreparedStatement *pstmt;
pstmt = con->prepareStatement("SQL语句");
pstmt->setInt(参数索引, 整数值);
pstmt->setString(参数索引, 字符串值);
pstmt->execute();
pstmt->executeQuery();
pstmt->executeUpdate();
作用：
prepareStatement()预编译 SQL，提高执行效率并防止 SQL 注入。setInt()/setString()设置 SQL 语句中的参数值。
示例：
sql::PreparedStatement *pstmt = con->prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
pstmt->setInt(1, 2);
pstmt->setString(2, "Alice");
pstmt->executeUpdate();
delete pstmt;
7. 事务处理
自动提交（Auto Commit）
MySQL 默认启用 Auto Commit，每条 INSERT、UPDATE、DELETE 语句都会 立即生效，即使程序崩溃，数据也已经修改了。
示例（默认自动提交）
sql::Statement *stmt = con->createStatement();
stmt->execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
delete stmt; // 即使程序崩溃，数据库的修改依然有效
手动事务（关闭自动提交）
如果希望多个 SQL 语句 全部成功或全部失败，需要 关闭自动提交 并手动 COMMIT 或 ROLLBACK。
接口：
con->setAutoCommit(false);
con->commit();
con->rollback();
作用：
setAutoCommit(false)：关闭自动提交，进入事务模式。commit()：提交事务，所有 SQL 语句的更改生效。rollback()：回滚事务，撤销所有未提交的更改。
示例（手动控制事务）
try {con->setAutoCommit(false); // 关闭自动提交，开启事务模式sql::PreparedStatement *pstmt = con->prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");pstmt->setDouble(1, 100.0);pstmt->setInt(2, 1);pstmt->executeUpdate();delete pstmt;pstmt = con->prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?");pstmt->setDouble(1, 100.0);pstmt->setInt(2, 2);pstmt->executeUpdate();delete pstmt;con->commit(); // **手动提交事务，所有修改一起生效**
} catch (sql::SQLException &e) {con->rollback(); // **出错时回滚，保证数据一致性**std::cerr << "Transaction failed: " << e.what() << std::endl;
}
事务的使用场景
适用于需要保证 数据一致性 的情况：
- 银行转账（A 账户扣钱，B 账户加钱，必须同时成功）
- 库存管理（购买商品时，需要同时更新库存和订单信息）
- 订单处理（下单时，必须同时修改多个表的数据）
总结
- MySQL 默认
Auto Commit = true，每条 SQL 语句都会立即提交。 - 关闭
Auto Commit后，可以 手动提交或回滚，确保数据一致性。 - 使用
commit()确保更改生效，使用rollback()处理失败情况。
8. 关闭连接
接口：
delete res;
delete stmt;
delete pstmt;
delete con;
作用：
释放资源，避免内存泄漏。
示例：
delete res;
delete stmt;
delete pstmt;
delete con;
总结
| 类别 | 接口 | 作用 |
|---|---|---|
| 数据库连接 | sql::mysql::MySQL_Driver *driver;sql::Connection *con;driver = sql::mysql::get_mysql_driver_instance();con = driver->connect(...); | 连接 MySQL 数据库 |
| 选择数据库 | con->setSchema("database_name"); | 选择数据库 |
| 执行 SQL | stmt->execute("SQL语句");stmt->executeQuery("SQL查询语句");stmt->executeUpdate("SQL更新语句"); | 执行 SQL 语句 |
| 获取查询结果 | res->next();res->getInt("column_name");res->getString("column_name"); | 获取查询结果 |
| 预处理语句 | pstmt = con->prepareStatement("SQL语句");pstmt->setInt(1, value);pstmt->setString(2, "value");pstmt->executeUpdate(); | 预编译 SQL，防止 SQL 注入 |
| 事务管理 | con->setAutoCommit(false);con->commit();con->rollback(); | 控制事务提交和回滚 |
| 释放资源 | delete res;delete stmt;delete pstmt;delete con; | 释放内存，避免泄漏 |
代码示例
/* Copyright 2008, 2010, Oracle and/or its affiliates.This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.There are special exceptions to the terms and conditions of the GPL
as it is applied to this software. View the full text of the
exception in file EXCEPTIONS-CONNECTOR-C++ in the directory of this
software distribution.This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*//* Standard C++ includes */
#include <stdlib.h>
#include <iostream>/*Include directly the differentheaders from cppconn/ and mysql_driver.h + mysql_util.h(and mysql_connection.h). This will reduce your build time!
*/
#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::ResultSet *res;sql::PreparedStatement *pstmt;/* Create a connection */driver = get_driver_instance();con = driver->connect("tcp://127.0.0.1:3306", "root", "root");/* Connect to the MySQL test database */con->setSchema("test");stmt = con->createStatement();stmt->execute("DROP TABLE IF EXISTS test");stmt->execute("CREATE TABLE test(id INT)");delete stmt;/* '?' is the supported placeholder syntax */pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");for (int i = 1; i <= 10; i++) {pstmt->setInt(1, i);pstmt->executeUpdate();}delete pstmt;/* Select in ascending order */pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");res = pstmt->executeQuery();/* Fetch in reverse = descending order! */res->afterLast();while (res->previous())cout << "\t... MySQL counts: " << res->getInt("id") << endl;delete res;delete pstmt;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;
}
