Visual Studio2022+Connector/C++ 8.3.0和C++连接与操作Mysql数据库

Hacker

本帖最后由 Hacker 于 2024-1-28 09:09 编辑

（1）开发环境：

1. windows11
2. Visual Studio community 2022
3. Connector/C++ 8.3.0 （x64），安装包

（2）安装过程

安装Connector C++ 8.0、MySQL Server 8.0，并设置地址、端口，用户名，密码

（3）配置过程（所有配置需要设置成release x64模式下）：

①添加Include目录

②添加Library目录

③添加依赖库mysqlcppconn.lib与libmysql.lib

（4）调试过程（需要自行创建testdb数据库与test库表）：

添加库到release可执行文件目录下

#include <iostream>
#include <mysql.h>
#include <iomanip>
#include <string>

/**
* description:
*   This program is used to connect to a MySQL database and print out the data in the table.
*       author: lichen
*   date: 2024-01-28
*   version: 1.0
*   environment: win11, vs2022, Connector/C++ 8.3.0
*/
int main()
{
    MYSQL* conn;
    MYSQL_ROW row;
    MYSQL_RES* res;
    int qstate;
    conn = mysql_init(0);
    conn = mysql_real_connect(conn, "localhost", "root", "lichen1234", "testdb", 3307, NULL, 0);
    if (conn)
    {
        std::cout << "Connected to database" << std::endl;

        std::string q = "INSERT INTO test (Name, Score) VALUES ('Tom', 87)";
        const char* q_str = q.c_str();
        mysql_query(conn, q_str);

        qstate = mysql_query(conn, "select * from test");
        if (!qstate)
        {
            res = mysql_store_result(conn);
            while (row = mysql_fetch_row(res))
            {
                std::cout << "ID: " << std::left << std::setw(5) << row[0] << " Name: " << std::left << std::setw(10) << row[1] << " Score: " << row[2] << std::endl;
            }
        }
        else
        {
            std::cout << "Query failed: " << mysql_error(conn) << std::endl;
        }
    }
    else
    {
        std::cout << "Connection failed: " << mysql_error(conn) << std::endl;
    }
    return 0;
}

Hacker

执行结果：

Hacker

数据库与库表结构

admin

To connect to a MySQL database in C++ on Ubuntu using Connector/C++, you will need to install the following dependencies:

• MySQL server
• MySQL Connector/C++
• OpenSSL library
• Boost library

To install MySQL server, run the following command:

sudo apt-get install mysql-server

To install MySQL Connector/C++, run the following command:

sudo apt-get install libmysqlcppconn-dev

To install OpenSSL library, run the following command:

sudo apt-get install libssl-dev

To install Boost library, run the following command:

sudo apt-get install libboost-dev

Once you have installed all of the dependencies,

you can cofigure CMakeLists.txt;

target_link_libraries(connectMysql -lmysqlcppconn)

you can create a new C++ project in CLion and add the following header file to your project:

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

To connect to a MySQL database, you can use the following code:

Driver *driver;
    Connection *conn;
    driver = get_driver_instance();
    conn = driver->connect("tcp://localhost:3306", "root", "Your Mysql Password");
    conn->setSchema("testdb");
    Statement *stmt;
    stmt = conn->createStatement();
    stmt->execute("DROP TABLE IF EXISTS test");
    stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");
    stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");
    stmt->execute("INSERT INTO test(id, label) VALUES (2, 'b')");
    stmt->execute("INSERT INTO test(id, label) VALUES (3, 'c')");
    stmt->execute("INSERT INTO test(id, label) VALUES (4, 'd')");
    stmt->execute("INSERT INTO test(id, label) VALUES (5, 'e')");
    ResultSet *result = stmt->executeQuery("SELECT * FROM test");
    while (result->next()) {
        cout << "Label: " << result->getString("label") << endl;
    }
    delete result;
    delete stmt;
    delete conn;

The provided code is written in C++ and uses the Connector/C++ library to interact with a MySQL database.

The first part of the code initializes the MySQL driver and establishes a connection to the MySQL server. The get_driver_instance() function is used to get a driver instance, and the connect() function is used to establish a connection to the MySQL server. The setSchema() function is used to select the database to use.

Driver *driver;
Connection *conn;
driver = get_driver_instance();
conn = driver->connect("tcp://localhost:3306", "root", "Bruce006!");
conn->setSchema("testdb");

Next, a Statement object is created using the createStatement() function. This object is used to execute SQL statements.

Statement *stmt;
stmt = conn->createStatement();

The execute() function is used to execute SQL statements. In this case, it's used to drop a table if it exists, create a new table, and insert data into the table.

stmt->execute("DROP TABLE IF EXISTS test");
stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");
stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");

// ... more insert statements ...

A ResultSet object is created by executing a query using the executeQuery() function. This object contains the result of the query.

ResultSet *result = stmt->executeQuery("SELECT * FROM test");

The next() function is used to iterate over the rows in the result set, and the getString() function is used to retrieve the value of the 'label' column for each row.

while (result->next()) {
    cout << "Label: " << result->getString("label") << endl;
}

Finally, the ResultSet, Statement, and Connection objects are deleted to free up resources.

delete result;
delete stmt;
delete conn;