❤️[c++ link mysql series tutorial 1] Introduction to MySQL connection process and MySQL API (with simple implementation of additions, deletions, changes, and check) ❤️

table of Contents

One, C++ link MySQL process

The detailed process is as follows:

Insert picture description here

Two, MySQL API

1. Common API data structure

Structure nameParsing
MYSQLThis structure represents a database connection handler. It is used in almost all MySQL functions. Don't try to copy the MYSQL structure. There is no guarantee that such copies will be available.
MYSQL_RESThis structure represents the query results that return rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from the query is called the result set.
MYSQL_ROWThis is a type-safe representation of a row of data. It is currently implemented as an array of counted byte strings. (If the field values ​​may contain binary data, they cannot be treated as null-terminated strings, because such values ​​may contain null bytes internally.) Obtained by calling mysql_fetch_row().
MYSQL_FIELDThis structure contains metadata: information about the field, such as the name, type, and size of the field. You can repeatedly call MYSQL_FIELD through mysql_fetch_field() to get the structure of each field. The field values ​​are not part of this structure; they are contained in a MYSQL_ROW structure.

2. Commonly used API functions

mysql_init()

Description:
Allocate or initialize a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function will allocate, initialize and return a new object. Otherwise, initialize the object and return the address of the object. If mysql_init() allocates a new object, it will be released when mysql_close() is called to close the connection.

Function declaration:

MYSQL *
mysql_init(MYSQL *mysql);

Return value:
Successfully returns an initialized MYSQL handler; if there is not enough memory to allocate a new object, it is NULL.

For example:

MYSQL *mysql;
mysql_init(mysql);

mysql_real_connect()

Description:
mysql_real_connect() attempts to establish a connection to the MySQL server running on the host. The client program must successfully connect to the server before executing any other API functions that require a valid MYSQL connection handler structure.

Function declaration:

MYSQL *
mysql_real_connect(MYSQL *mysql,						//初始化的数据库
                   const char *host,					//主机IP
                   const char *user,					//用户名
                   const char *passwd,					//用户密码
                   const char *db,						//数据库名称
                   unsigned int port,					//端口号(3306)
                   //如果unix_socket不是 NULL,则字符串指定要使用的套接字或命名管道
                   const char *unix_socket,			
                   //client_flag通常为 0
                   unsigned long client_flag);			

Return value:
If the connection is successful, it is a MYSQL* connection handler; if the connection is unsuccessful, it is NULL. For a successful connection, the return value is the same as the value of the first parameter.

For example:

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",3306,NULL,0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));
}

mysql_query()

Description:
Execute SQL statement. Normally, the string must consist of a SQL statement without a semicolon .

mysql_query() cannot be used for statements containing binary data.

Function description:

int
mysql_query(MYSQL *mysql,			//MYSQL 对象指针
            const char *stmt_str);	//sql语句

Return value:
Return 0 on success, non-zero on error.

mysql_store_result()

Description:
After calling mysql_real_query() or mysql_query(), for each statement that successfully generates a result set (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, etc.), you must call mysql_store_result() or mysql_use_result(). After processing the result set, you must also call mysql_free_result().

For other statements, you do not need to call mysql_store_result() or mysql_use_result(), but if you call mysql_store_result() in all cases, it will not cause any damage or cause any significant performance degradation. You can check whether the statement has a result set by checking whether mysql_store_result() returns a non-zero value (more on that later).

To determine whether the statement returns a result set, call mysql_field_count(). See Section 5.4.23, "mysql_field_count()".

mysql_store_result() reads the entire result of the query to the client, allocates a MYSQL_RES structure, and puts the result into this structure.
mysql_store_result() returns NULL if the statement does not return a result set (for example, if it is an INSERT statement), or an error occurs, and the result set fails to be read.

If no rows are returned, an empty result set is returned. (The empty result set is different from the null pointer as the return value.)

After calling mysql_store_result() and getting a result that is not a null pointer, you can call mysql_num_rows() to find out how many rows there are in the result set.

Function declaration:

MYSQL_RES *
mysql_store_result(MYSQL *mysql);	

Return value:
Pointer to the MYSQL_RES result structure with the result. If the statement does not return a result set or an error occurs, it is NULL. To determine whether an error occurred, check whether mysql_error() returns a non-empty string, mysql_errno() returns non-zero, or mysql_field_count() returns zero.

mysql_close()

Description:
Close the previously opened connection. If the handler is automatically allocated by mysql_init() or mysql_connect(), Mysql_close() will also release the connection handler pointed to by mysql. Do not use it after closing the handler.

Function declaration:

void
mysql_close(MYSQL *mysql);		

Return value:
None

3. Examples of commonly used mysql additions, deletions, modifications, and checking operations

1. Add data to the database

int main()
{
	......
	
	// 增加数据测试
	const char* sql1 = "insert into user values (NULL, 'song', '99')";
	mysql.ExecuteSql(sql1);

	......
	return 0;
}

// 执行sql语句, 包括增加、删除、更新数据
bool MySQLManager::ExecuteSql(const char * sql)
{
	if (mysql_query(&m_mysql, sql))
	{
		std::cout << "执行sql语句失败,错误信息为: " << mysql_error(&m_mysql) << endl;
		return false;
	}
	else
	{
		std::cout << "执行sql语句成功!" << endl;
	}

	return true;
}

2. Delete data in the database

int main()
{
	......
	
	// 删除数据测试
	const char* sql2 = "delete from user where name = 'sun'";
	mysql.ExecuteSql(sql2);

	......
	return 0;
}

3. Modify the data in the database

int main()
{
	......
	
	// 修改数据测试
	const char* sql3 = "update user set grade='22' where name = 'wu'";
	mysql.ExecuteSql(sql3);

	......
	return 0;
}

4. Query data in the database

int main()
{
	......
	
	// 查询数据测试
	const char* sql4 = "select * from user";
	mysql.QueryData(sql4);

	......
	return 0;
}

// 查询数据
MYSQL_RES* MySQLManager::QueryData(const char* sql)
{
	if (mysql_query(&m_mysql, sql))
	{
		std::cout << "查询语句执行失败,错误信息为: " << mysql_error(&m_mysql) << endl;
		return nullptr;
	}
	else
	{
		std::cout << "查询语句执行成功!" << endl;
	}
	// 存储查询结果
	m_res = mysql_store_result(&m_mysql);
	return m_res;
}

5. Print database query results

// 遍历结果集
void MySQLManager::PrintQueryRes()
{
	if (nullptr == m_res || NULL == m_res)
	{
		return;
	}

	// 字段列数组
	MYSQL_FIELD* field = nullptr;
	//存字段名二维数组
	char fieldName[64][32];

	// 获取字段名
	for (int i = 0; field = mysql_fetch_field(m_res); ++i)
	{
		strcpy_s(fieldName[i], field->name);
	}
	// 获取列数
	int columns = mysql_num_fields(m_res);
	for (int i = 0; i < columns; ++i)
	{
		printf("%10s\t", fieldName[i]);
	}
	cout << endl;
	//获取行数
	MYSQL_ROW row;
	while (row = mysql_fetch_row(m_res))
	{
		for (int i = 0; i < columns; ++i)
		{
			printf("%10s\t", row[i]);
		}
		cout << endl;
	}
}

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------
If This article is helpful to you, please don't forget to like and bookmark it! ! !
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------

Reference documents

For more details, please refer to the official document, the URL is as follows:
MySQL official website: https://dev.mysql.com/doc/c-api/8.0/en/mysql-close.html