MySQL Connector/C++

MySQL Connector/C++ 是 MySQL 官方 C++ 驱动，支持传统 C API 和现代 X DevAPI，提供完整的 MySQL 访问能力。

安装

# Ubuntu
sudo apt install libmysqlcppconn-dev

# vcpkg
vcpkg install libmysql

# CMake
find_package(MySQL REQUIRED)
target_link_libraries(myapp PRIVATE MySQL::MySQL)

基本连接与查询

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

int main() {
    try {
        sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();

        std::unique_ptr<sql::Connection> conn(
            driver->connect("tcp://127.0.0.1:3306", "root", "password"));
        conn->setSchema("mydb");

        // 普通查询
        std::unique_ptr<sql::Statement> stmt(conn->createStatement());
        std::unique_ptr<sql::ResultSet> res(
            stmt->executeQuery("SELECT id, name, age FROM users"));

        while (res->next()) {
            int id = res->getInt("id");
            std::string name = res->getString("name");
            int age = res->getInt("age");
            std::cout << id << " " << name << " " << age << "\n";
        }

    } catch (const sql::SQLException& e) {
        std::cerr << "MySQL 错误: " << e.what() << "\n";
        std::cerr << "错误码: " << e.getErrorCode() << "\n";
        std::cerr << "SQL 状态: " << e.getSQLState() << "\n";
    }
}

预处理语句

// 预处理语句：防 SQL 注入 + 性能更好
std::unique_ptr<sql::PreparedStatement> pstmt(
    conn->prepareStatement(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"));

pstmt->setString(1, "Alice");
pstmt->setString(2, "alice@example.com");
pstmt->setInt(3, 30);
pstmt->execute();

// 查询
std::unique_ptr<sql::PreparedStatement> select_stmt(
    conn->prepareStatement(
        "SELECT * FROM users WHERE age > ? AND name LIKE ?"));

select_stmt->setInt(1, 18);
select_stmt->setString(2, "%Alice%");

std::unique_ptr<sql::ResultSet> res(select_stmt->executeQuery());
while (res->next()) {
    std::cout << res->getString("name") << "\n";
}

// 获取最后插入的 ID
std::unique_ptr<sql::Statement> stmt(conn->createStatement());
stmt->execute("INSERT INTO users (name) VALUES ('Bob')");
std::unique_ptr<sql::ResultSet> id_res(
    stmt->executeQuery("SELECT LAST_INSERT_ID()"));
if (id_res->next()) {
    std::cout << "新 ID: " << id_res->getInt(1) << "\n";
}

事务

conn->setAutoCommit(false);  // 关闭自动提交

try {
    std::unique_ptr<sql::PreparedStatement> debit(
        conn->prepareStatement(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?"));
    debit->setDouble(1, 100.0);
    debit->setInt(2, 1);
    debit->execute();

    std::unique_ptr<sql::PreparedStatement> credit(
        conn->prepareStatement(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?"));
    credit->setDouble(1, 100.0);
    credit->setInt(2, 2);
    credit->execute();

    conn->commit();
    std::cout << "转账成功\n";

} catch (const sql::SQLException& e) {
    conn->rollback();
    std::cerr << "转账失败，已回滚: " << e.what() << "\n";
}

conn->setAutoCommit(true);

批量插入

conn->setAutoCommit(false);

std::unique_ptr<sql::PreparedStatement> pstmt(
    conn->prepareStatement(
        "INSERT INTO users (name, age) VALUES (?, ?)"));

for (int i = 0; i < 10000; ++i) {
    pstmt->setString(1, "User" + std::to_string(i));
    pstmt->setInt(2, 20 + i % 50);
    pstmt->addBatch();  // 添加到批次

    if (i % 1000 == 999) {
        pstmt->executeBatch();  // 每 1000 条提交一次
        conn->commit();
    }
}
pstmt->executeBatch();
conn->commit();

连接池（简单实现）

class MySQLPool {
    sql::mysql::MySQL_Driver* driver_;
    std::vector<std::unique_ptr<sql::Connection>> pool_;
    std::queue<sql::Connection*> available_;
    std::mutex mtx_;
    std::condition_variable cv_;

public:
    MySQLPool(const std::string& host, const std::string& user,
              const std::string& pass, const std::string& db, size_t size) {
        driver_ = sql::mysql::get_mysql_driver_instance();
        for (size_t i = 0; i < size; ++i) {
            auto conn = std::unique_ptr<sql::Connection>(
                driver_->connect(host, user, pass));
            conn->setSchema(db);
            available_.push(conn.get());
            pool_.push_back(std::move(conn));
        }
    }

    sql::Connection* acquire() {
        std::unique_lock lock(mtx_);
        cv_.wait(lock, [this] { return !available_.empty(); });
        auto* conn = available_.front();
        available_.pop();
        return conn;
    }

    void release(sql::Connection* conn) {
        std::lock_guard lock(mtx_);
        available_.push(conn);
        cv_.notify_one();
    }
};

关键认知

MySQL Connector/C++ 的预处理语句（prepareStatement）是防 SQL 注入的标准方式。批量插入时关闭自动提交（setAutoCommit(false)），每批次手动 commit，性能提升 10-100x。生产环境必须使用连接池。