在C++项目中使用MySQL数据库是一种常见的需求，尤其在涉及数据持久化的应用中。下面讲解如何在C++中通过 MySQL Connector/C++ 操作MySQL数据库，涵盖 连接数据库、增删改查、事务控制、预处理语句、表结构操作等所有常用SQL操作。

---

一、开发环境准备

1. 安装 MySQL Connector/C++

• 官网地址：https://dev.mysql.com/downloads/connector/cpp/

• 推荐使用 8.x 版本，对应 MySQL 8.x，支持现代 C++ 特性。

• 安装后注意包含以下路径：

  • include/

  • lib64/（或 lib/）

  • 连接库如 libmysqlcppconn.so, mysqlcppconn.lib

2. 配置 CMake（示例）

find_package(MySQL REQUIRED)

include_directories(/usr/include/mysql-cppconn-8/)
link_directories(/usr/lib64/)

add_executable(MyApp main.cpp)
target_link_libraries(MyApp mysqlcppconn)

---

二、连接数据库

#include <mysql/jdbc.h>

sql::Driver *driver;
sql::Connection *con;

try {
    driver = get_driver_instance();
    con = driver->connect("tcp://127.0.0.1:3306", "root", "your_password");
    con->setSchema("your_database"); // 设置默认数据库
} catch (sql::SQLException &e) {
    std::cerr << "连接失败: " << e.what() << std::endl;
}

---

三、SQL基本操作

1. 创建表（DDL）

sql::Statement *stmt = con->createStatement();
stmt->execute("CREATE TABLE IF NOT EXISTS users ("
              "id INT AUTO_INCREMENT PRIMARY KEY,"
              "name VARCHAR(100),"
              "age INT,"
              "email VARCHAR(100))");
delete stmt;

---

2. 插入数据（INSERT）

stmt = con->createStatement();
stmt->execute("INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com')");
delete stmt;

---

3. 查询数据（SELECT）

stmt = con->createStatement();
sql::ResultSet *res = stmt->executeQuery("SELECT id, name, age, email FROM users");

while (res->next()) {
    std::cout << "ID: " << res->getInt("id")
              << ", Name: " << res->getString("name")
              << ", Age: " << res->getInt("age")
              << ", Email: " << res->getString("email") << std::endl;
}
delete res;
delete stmt;

---

4. 更新数据（UPDATE）

stmt = con->createStatement();
stmt->execute("UPDATE users SET age = 30 WHERE name = 'Alice'");
delete stmt;

---

5. 删除数据（DELETE）

stmt = con->createStatement();
stmt->execute("DELETE FROM users WHERE name = 'Alice'");
delete stmt;

---

四、预处理语句（PreparedStatement）

sql::PreparedStatement *prep_stmt;
prep_stmt = con->prepareStatement("INSERT INTO users (name, age, email) VALUES (?, ?, ?)");

prep_stmt->setString(1, "Bob");
prep_stmt->setInt(2, 28);
prep_stmt->setString(3, "bob@example.com");

prep_stmt->execute();
delete prep_stmt;

---

五、事务处理（Transaction）

try {
    con->setAutoCommit(false);

    stmt = con->createStatement();
    stmt->execute("UPDATE users SET age = 31 WHERE name = 'Bob'");
    stmt->execute("INSERT INTO users (name, age, email) VALUES ('Carol', 27, 'carol@example.com')");

    con->commit(); // 提交事务
} catch (sql::SQLException &e) {
    con->rollback(); // 回滚事务
    std::cerr << "事务失败，已回滚: " << e.what() << std::endl;
}

---

六、更多 SQL 操作

1. 表结构修改（ALTER TABLE）

stmt = con->createStatement();
stmt->execute("ALTER TABLE users ADD COLUMN address VARCHAR(200)");
delete stmt;

2. 创建索引

stmt = con->createStatement();
stmt->execute("CREATE INDEX idx_email ON users (email)");
delete stmt;

3. 删除表

stmt = con->createStatement();
stmt->execute("DROP TABLE users");
delete stmt;

---

七、关闭连接

delete con;

或者使用智能指针更安全：

std::unique_ptr<sql::Connection> con(driver->connect(...));

---

八、异常处理建议

catch (sql::SQLException &e) {
    std::cerr << "# ERR: SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() << " )" << std::endl;
}

---

九、常见问题排查

问题	可能原因
无法连接 MySQL	IP、端口、用户名或密码错误
连接对象为 nullptr	未正确初始化 Connector 或驱动缺失
编译找不到头文件/库	CMake 路径未设置或版本不匹配
SQL 执行失败	SQL语法错误，注意区分单/双引号