Environment Configuration
Installing MySQL Development Libraries
To use C++ to operate MySQL, you need to install the MySQL development libraries (including header files and link libraries) first.
Windows System:
- Download MySQL Connector/C++ (choose the installation package that matches the MySQL server version).
- During installation, select “Complete” mode to ensure that header files (
mysql.h,mysql_driver.h, etc.) and library files (libmysql.lib,mysqlcppconn.lib) are installed correctly. - Record the installation path (e.g.,
C:\Program Files\MySQL\MySQL Connector C++ 8.0).
Linux System (Ubuntu as an example):
# Install MySQL server (if not already installed)
sudo apt-get install mysql-server
# Install development libraries (including header files and link libraries)
sudo apt-get install libmysqlclient-dev
Configuring the Compiler
When compiling a C++ program, you need to specify the paths to the MySQL header files and library files.
Windows (Visual Studio):
- Open Project Properties → C/C++ → General → Additional Include Directories, add the MySQL header file path (e.g.,
C:\Program Files\MySQL\MySQL Connector C++ 8.0\include). - Open Linker → General → Additional Library Directories, add the library file path (e.g.,
C:\Program Files\MySQL\MySQL Connector C++ 8.0\lib64\vc15). - Open Linker → Input → Additional Dependencies, add the library names (e.g.,
mysqlcppconn.liborlibmysql.lib, depending on the connection library type).
Linux (g++):
When compiling, you need to link the MySQL client library (-lmysqlclient) or Connector/C++ library (-lmysqlcppconn):
g++ your_code.cpp -o output -lmysqlcppconn # Using Connector/C++
# or
g++ your_code.cpp -o output -lmysqlclient # Using native C API
Connecting to MySQL Database
Using MySQL Connector/C++ (Recommended)
MySQL Connector/C++ is the official C++ wrapper library provided by MySQL, supporting object-oriented programming and simplifying the connection and operation process.
Example Code: Establishing a Connection
#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/resultset.h>
#include <cppconn/exception.h>
#include <iostream>
int main() {
sql::mysql::MySQL_Driver* driver = nullptr;
sql::Connection* conn = nullptr;
try {
// 1. Get MySQL driver instance
driver = sql::mysql::get_mysql_driver_instance();
// 2. Connect to the database (parameters: host, user, password, database name, port)
conn = driver->connect(
"tcp://127.0.0.1:3306", // Host and port
"root", // Username
"your_password", // Password
"test_db" // Database name
);
std::cout << "Connection successful!" << std::endl;
// 3. Execute a simple query (example: query MySQL version)
sql::Statement* stmt = conn->createStatement();
sql::ResultSet* res = stmt->executeQuery("SELECT VERSION()");
// 4. Process the result set
if (res->next()) {
std::cout << "MySQL version: " << res->getString(1) << std::endl;
}
// 5. Release resources
delete res;
delete stmt;
delete conn; // Note: Connection must be manually released!
} catch (sql::SQLException& e) {
std::cerr << "SQL error: " << e.what()
<< " (error code: " << e.getErrorCode()
<< ", SQL state: " << e.getSQLState() << ")" << std::endl;
} catch (std::exception& e) {
std::cerr << "Error: " << e.what() << std::endl;
}
return 0;
}
Key Steps Explained:
• Driver acquisition: get_mysql_driver_instance() returns a globally unique driver instance (singleton pattern).
• Connection parameters: The connect() method supports multiple connection methods (e.g., tcp://, unix_socket://).
• Resource release: ResultSet, Statement, and Connection objects must be manually released to avoid memory leaks.
Using Native C API (mysql.h)
For lower-level control (e.g., high-performance scenarios), you can use the native C API (mysql.h) provided by MySQL.
Example Code: Establishing a Connection
#include <mysql.h>
#include <iostream>
#include <string>
int main() {
MYSQL* conn = mysql_init(nullptr); // Initialize connection handle
if (!conn) {
std::cerr << "mysql_init failed" << std::endl;
return 1;
}
// Connect to the database
if (!mysql_real_connect(
conn, // Connection handle
"127.0.0.1", // Hostname
"root", // Username
"your_password", // Password
"test_db", // Database name
3306, // Port
nullptr, // Unix socket (optional)
0 // Flags (optional)
)) {
std::cerr << "Connection failed: " << mysql_error(conn) << std::endl;
mysql_close(conn);
return 1;
}
std::cout << "Connection successful!" << std::endl;
// Execute query
if (mysql_query(conn, "SELECT VERSION()")) {
std::cerr << "Query failed: " << mysql_error(conn) << std::endl;
} else {
MYSQL_RES* res = mysql_store_result(conn); // Get result set
if (res) {
MYSQL_ROW row = mysql_fetch_row(res); // Fetch first row
if (row) {
std::cout << "MySQL version: " << row[0] << std::endl;
}
mysql_free_result(res); // Free result set
}
}
mysql_close(conn); // Close connection
return 0;
}
Key Steps Explained:
• Connection handle: MYSQL* is the core handle for the connection; all operations are performed through it.
• Error handling: Use mysql_error() to get specific error messages.
• Result set handling: mysql_store_result() loads the result into memory (suitable for small result sets), mysql_fetch_row() reads row by row.
Executing SQL Operations (CRUD)
Inserting Data (INSERT)
Use the INSERT INTO statement to insert data. It is recommended to use prepared statements to avoid SQL injection.
Example (Connector/C++):
#include <cppconn/prepared_statement.h>
// ... (connection code same as above)
try {
// Create prepared statement
sql::PreparedStatement* pstmt = conn->prepareStatement(
"INSERT INTO users (name, age, email) VALUES (?, ?, ?)"
);
// Bind parameters (? placeholders)
pstmt->setString(1, "Zhang San"); // Replace first ? with "Zhang San"
pstmt->setInt(2, 25); // Replace second ? with 25
pstmt->setString(3, "zhangsan@example.com"); // Replace third ? with email
// Execute insertion
int affected_rows = pstmt->executeUpdate();
std::cout << "Insertion successful, affected rows: " << affected_rows << std::endl;
delete pstmt; // Release prepared statement
} catch (sql::SQLException& e) {
std::cerr << "Insertion failed: " << e.what() << std::endl;
}
Querying Data (SELECT)
Query data using the SELECT statement to retrieve result sets and iterate through them.
Example (Connector/C++):
sql::PreparedStatement* pstmt = conn->prepareStatement(
"SELECT id, name, age FROM users WHERE age > ?"
);
pstmt->setInt(1, 18); // Bind age condition (>18 years old)
sql::ResultSet* res = pstmt->executeQuery();
// Iterate through result set
while (res->next()) {
int id = res->getInt("id"); // Get by column name
std::string name = res->getString(2); // Get by column index (starting from 1)
int age = res->getInt(3);
std::cout << "User: " << name << " (ID: " << id << ", Age: " << age << ")" << std::endl;
}
delete res;
delete pstmt;
Updating Data (UPDATE)
Use the UPDATE statement to modify existing data.
Example (Native C API):
const char* update_sql = "UPDATE users SET age = age + 1 WHERE name = 'Zhang San'";
if (mysql_query(conn, update_sql)) {
std::cerr << "Update failed: " << mysql_error(conn) << std::endl;
} else {
std::cout << "Update successful, affected rows: " << mysql_affected_rows(conn) << std::endl;
}
Deleting Data (DELETE)
Use the DELETE statement to delete data.
Example (Connector/C++):
sql::PreparedStatement* pstmt = conn->prepareStatement(
"DELETE FROM users WHERE id = ?"
);
pstmt->setInt(1, 100); // Delete user with ID 100
int affected_rows = pstmt->executeUpdate();
std::cout << "Deletion successful, affected rows: " << affected_rows << std::endl;
delete pstmt;
Error Handling
- Catch exceptions
- Output error messages
try {
// Execute SQL statement
} 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;
}
Closing the Connection
- Close result set
- Close Statement object
- Close connection
// Close resources
res->close();
stmt->close();
conn->close();
Parameterized Queries
- Use PreparedStatement
- Set parameter values
// Create PreparedStatement object
shared_ptr<sql::PreparedStatement> pstmt = conn->prepareStatement("INSERT INTO students (name, age) VALUES (?, ?)");
// Set parameters
pstmt->setString(1, "Jane Doe");
pstmt->setInt(2, 22);
// Execute SQL statement
pstmt->execute();
// Close PreparedStatement
pstmt->close();
Complete Example
#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/resultset.h>
#include <string>
#include <iostream>
using namespace std;
using namespace sql; // MySQL C++ Connector namespace
int main() {
// Set connection parameters
string url = "jdbc:mysql://localhost:3306/testdb";
string user = "root";
string password = "your_password";
try {
// Get driver
sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
// Open connection
shared_ptr<sql::Connection> conn(driver->connect(url, user, password));
// Create Statement object
shared_ptr<sql::Statement> stmt(conn->createStatement());
// Insert data
stmt->execute("INSERT INTO students (name, age) VALUES ('John Doe', 20)");
// Query data
stmt->execute("SELECT * FROM students");
// Get result set
shared_ptr<sql::ResultSet> res = stmt->getResultSet();
// Process result set
while (res->next()) {
int id = res->getInt("id");
string name = res->getString("name");
int age = res->getInt("age");
cout << "ID: " << id << ", Name: " << name << ", Age: " << age << endl;
}
// Close resources
res->close();
stmt->close();
conn->close();
} 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;
}
return 0;
}
Error Handling
When handling MySQL operations in C++, various errors often occur, such as connection failures, query failures, etc. It is very important to understand how to handle these errors correctly.
Use exception handling mechanisms to catch and handle SQL exceptions.
try {
// Execute SQL statement
stmt->execute("SELECT * FROM non_existent_table");
} 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;
}
Debugging Tips
- Use breakpoint debugging to locate issues.
- Output SQL statements and result set information to help understand the execution flow.
cout << "Executing query: " << stmt->getSQL();
Index Usage
- Create indexes for frequently queried fields.
- Use the EXPLAIN command to check the query plan.
stmt->execute("EXPLAIN SELECT * FROM students WHERE name = 'John Doe'");
Batch Insertion
- Use batch insertion to reduce network round trips.
- Use the executemany() method.
// Create PreparedStatement object
shared_ptr<sql::PreparedStatement> pstmt = conn->prepareStatement("INSERT INTO students (name, age) VALUES (?, ?)");
// Set multiple parameters
pstmt->setString(1, "Alice");
pstmt->setInt(2, 23);
pstmt->addBatch();
pstmt->setString(1, "Bob");
pstmt->setInt(2, 24);
pstmt->addBatch();
// Execute batch insertion
pstmt->executeBatch();
// Close PreparedStatement
pstmt->close();
Transaction Processing (Transaction)
Transactions are used to ensure the atomicity of a group of SQL operations (either all succeed or all roll back), suitable for scenarios requiring data consistency (e.g., bank transfers).
Example (Connector/C++):
try {
// Start transaction (disable auto-commit)
conn->setAutoCommit(false);
// Operation 1: Debit from account
sql::PreparedStatement* pstmt1 = conn->prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ?"
);
pstmt1->setDouble(1, 100.0);
pstmt1->setInt(2, 1);
pstmt1->executeUpdate();
delete pstmt1;
// Operation 2: Credit to account
sql::PreparedStatement* pstmt2 = conn->prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?"
);
pstmt2->setDouble(1, 100.0);
pstmt2->setInt(2, 2);
pstmt2->executeUpdate();
delete pstmt2;
// Commit transaction
conn->commit();
std::cout << "Transfer successful!" << std::endl;
} catch (sql::SQLException& e) {
// Roll back transaction
conn->rollback();
std::cerr << "Transfer failed, rolled back: " << e.what() << std::endl;
} catch (...) {
conn->rollback();
std::cerr << "Unknown error, rolled back" << std::endl;
}
// Restore auto-commit (optional)
conn->setAutoCommit(true);
Advanced Techniques
Batch Operations (Batch Insert)
Use addBatch() and executeBatch() to insert data in batches, reducing network I/O overhead.
Example (Connector/C++):
sql::PreparedStatement* pstmt = conn->prepareStatement(
"INSERT INTO users (name, age) VALUES (?, ?)"
);
// Add batch data
pstmt->setString(1, "Li Si");
pstmt->setInt(2, 30);
pstmt->addBatch();
pstmt->setString(1, "Wang Wu");
pstmt->setInt(2, 28);
pstmt->addBatch();
// Execute batch insertion
int* affected_rows = pstmt->executeBatch();
std::cout << "Batch insertion completed, affected rows: " << affected_rows[0] << ", " << affected_rows[1] << std::endl;
delete[] affected_rows;
delete pstmt;
Stored Procedure Calls
Call MySQL stored procedures, supporting input and output parameters.
Example (Connector/C++):
// Assume stored procedure: CREATE PROCEDURE GetUserAge(IN name VARCHAR(50), OUT age INT)
sql::CallableStatement* cstmt = conn->prepareCall(
"{CALL GetUserAge(?, ?)}"
);
// Set input parameter
cstmt->setString(1, "Zhang San");
// Register output parameter (type is INTEGER)
cstmt->registerOutParameter(2, sql::DataType::INTEGER);
// Execute stored procedure
cstmt->execute();
// Get output parameter
int age = cstmt->getInt(2);
std::cout << "Zhang San's age: " << age << std::endl;
delete cstmt;
Connection Pool
- Use a connection pool to manage database connections and improve performance.
- You can use third-party libraries like Poco or implement a simple connection pool yourself.
class ConnectionPool {
public:
// Get connection
shared_ptr<sql::Connection> getConnection() {
if (!m_freeConnections.empty()) {
shared_ptr<sql::Connection> conn = m_freeConnections.front();
m_freeConnections.pop();
return conn;
}
return nullptr;
}
// Return connection
void releaseConnection(shared_ptr<sql::Connection> conn) {
m_freeConnections.push(conn);
}
private:
queue<shared_ptr<sql::Connection>> m_freeConnections;
};
Security
SQL Injection Protection
- Use parameterized queries to avoid SQL injection attacks.
- Validate and sanitize input data.
// Parameterized query
stmt->execute("SELECT * FROM students WHERE name = ?");
stmt->setString(1, "John Doe");
Data Encryption
- Encrypt sensitive data for storage.
- Use SSL/TLS protocol to protect data security during transmission.
// Enable SSL when creating connection
shared_ptr<sql::Connection> conn(driver->connect(url, user, password, "ssl_ca=/path/to/ca.pem"));
ORM Framework
- Use ORM frameworks to simplify database operations.
- Recommended: SOCI or cppdb.
#include <soci/soci.h>
#include <soci/mysql/soci-mysql.h>
soci::session sql(soci::mysql, "root", "your_password", "localhost", 3306, "testdb");
struct Student {
int id;
std::string name;
int age;
};
void insert_student(soci::session &sql, const Student &student) {
sql << "INSERT INTO students (name, age) VALUES (:name, :age)", use(student.name), use(student.age);
}
std::vector<Student> get_students(soci::session &sql) {
std::vector<Student> students;
sql << "SELECT id, name, age FROM students", into(students);
return students;
}
Concurrent Programming
- Use multithreading to execute database operations concurrently.
- Pay attention to transaction isolation levels and lock usage.
#include <thread>
void insert_data(shared_ptr<sql::Connection> conn) {
// Create Statement object
shared_ptr<sql::Statement> stmt(conn->createStatement());
stmt->execute("INSERT INTO students (name, age) VALUES ('Concurrent User', 25)");
}
int main() {
// Create multiple threads to insert data concurrently
std::vector<std::thread> threads;
for (int i = 0; i < 10; ++i) {
threads.emplace_back(insert_data, conn);
}
// Wait for all threads to complete
for (auto &t : threads) {
t.join();
}
return 0;
}



