How to connect to mysql database and read data in C++

1. The header file of mysql API needs to be included

If you need to connect to the local mysql database, the premise is that the mysql database must have been installed locally. Some mysql APIs are used here, such as connecting to the database, executing query statements and other operations. These interfaces are included in the following header files:

#include <mysql/mysql.h>

2. Specific steps to connect to mysql

Here It can be roughly divided into four main steps:

1. Connect to mysql database

1. Connect to mysql database

Obviously, if you want to obtain the data in mysql data, you must first connect Database, obtain a handle that can operate the database.

2. Execute the query statement, that is, select the data we need.

is to execute the query statement and query the data we need. The queried data will be saved in a place called the result set.

3. Obtain the required data from the result set

Use the relevant interface functions to obtain the data of each row and local field from the result set.

4. Extract the information of each field in each row from the result set

5. Release resources, including the result set mysql handle

The following will explain in detail a few that must be used Key interface functions.

2.1 mysql_real_connect

This function is used to connect to the database engine running on the host. If the connection is successful, a handle that can operate the database will be obtained, otherwise a NULL pointer will be returned.

MYSQL *mysql_real_connect(MYSQL *mysql, 
						const char *host, 
						const char *user, 
						const char *passwd, 
						const char *db, 
						unsigned int port, 
						const char *unix_socket, 
						unsigned long client_flag
						)

This function has many parameters, and the meaning of each parameter is as follows:

• mysql: It is the address of the existing MYSQL structure. Before calling mysql_real_connect(), mysql_init() must be called to initialize the MYSQL structure.

• #host: is the host name or IP address. If "host" is NULL or the string "localhost", the connection will be treated as a connection to localhost.

• #user: The user’s MySQL login ID. If "user" is NULL or the empty string "", the user will be considered the current user.

• passwd: User’s password. If "passwd" is NULL, only entries in the user's user table (that have an empty password field) will be checked for a match.

• db: is the database name. If db is NULL, the connection will set the default database to this value.

• port: If "port" is not 0, its value will be used as the port number for the TCP/IP connection. Note that the "host" parameter determines the type of connection.

• unix_socket: If unix_socket is not NULL, this string describes the socket or named pipe that should be used. Note that the "host" parameter determines the type of connection.

• client_flag: The value is usually 0

2.2 mysql_query or mysql_real_query

This function is used Send a query command to the database and let the database execute it. Returning 0 indicates that the query is successful, otherwise it fails.

int mysql_query(MYSQL *mysql, const char *stmt_str)

Or:

int
mysql_real_query(MYSQL *mysql,
                 const char *stmt_str,
                 unsigned long length)

• mysql: is the mysql operation handle obtained through.

• stmt_str: Indicates the query statement that needs to be executed.

• #length: is the length of the query statement.

The difference between the above two functions is:

• mysql_query() cannot be used to execute binary statements, that is, the parameter stmt_str cannot There is binary data, which will be parsed into characters.

• mysql_query query speed is slightly slower because the length of the query statement needs to be calculated

2.3 Get the result set mysql_store_result

The The function returns the result set if the query is successful. If it fails, it returns NULL

MYSQL_RES *mysql_store_result(MYSQL *mysql)

2.4 Display each row of data in the result set

The input parameter of this function is the result set returned in step (3). Each time it is called, the next row of data in the result set is returned and the pointer is moved backward by one row. If there is no next row of data, NULL is returned.
You can use mysql_num_fields(result) to calculate the number of rows in the result set, and mysql_num_fields(result) to calculate the number of columns. If row is the information of a certain row, then row[0], row[1]. . . Each field information of the row.

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

3. A programming example

The environment here is a linux system. The local database name used is: CrashCourse, and the query table name is products. The following programming example demonstrates querying all items with a price greater than 30 in the products table. The complete content of the products table is as follows:

#include 
#include <mysql/mysql.h>
#include 
using namespace std;
 
MYSQL mysql;  //mysql连接
MYSQL_RES* res; //结果集结构体   
MYSQL_ROW row; //char** 二维数组，存放记录  
 
int main()
{	
	// 步骤1： 初始化并连接数据库，获得操作数据库的句柄
	mysql_init(&mysql);    //初始化
	if (!(mysql_real_connect(&mysql, "localhost", "root", "root", "CrashCourse", 0, NULL, 0))) {
		cout << "Couldn't connect to Database!\n : " << mysql_error(&mysql);
		exit(1);
	}
	else {
		printf("Database connection succeeded. Connected...\n\n");
	}
	// 步骤2： 执行查询语句，查询需要的数据（设置编码格式也相当于执行特殊的查询语句）
	mysql_query(&mysql, "set names gbk"); // 设置编码格式
	mysql_query(&mysql, "SELECT * from products where prod_price > 30");
 
	// 步骤3：获取结果集
	res = mysql_store_result(&mysql);
	// 步骤4：显示结果集中每行数据
    int cols = mysql_num_fields(res); // 计算结果集中，列的个数
	while (row = mysql_fetch_row(res)) {
    
    	for (int i = 0; i < cols; ++i) {
      		cout << row[i] << "\t";
    	}
    	cout << endl;
	}
 	// 步骤5：释放结果集合mysql句柄
	mysql_free_result(res);
	mysql_close(&mysql);
 return 0;
 
}

The query results are as follows:

The above is the detailed content of How to connect to mysql database and read data in C++. For more information, please follow other related articles on the PHP Chinese website!