MySQL 8.0 API 使用STMT简单实例

最近研究MySQL预处理STMT的简单使用,直接上传实例.

#include "mysql.h"
#include <stdio.h>

#define STRING_SIZE 50

static int stmt_write(MYSQL *pMySQL);
static int stmt_read(MYSQL *pMySQL);
static char g_strTableName[] = "stmt_test";

// stmt官方实例
int main()
{
	int iRet = -1;
	MYSQL mydata, *pMySQL = nullptr;

	if (0 != mysql_server_init(0, NULL, NULL)) {
		return -1;
	}

	pMySQL = mysql_init(&mydata);
	if (NULL == pMySQL) {
		return -1;
	}

	if (NULL == mysql_real_connect(pMySQL, "127.0.0.1", "yes", "123456", "test", 3306, NULL, 0)){
		goto _end_fun;
	}

	stmt_write(pMySQL);

	stmt_read(pMySQL);

	iRet = 0;

_end_fun:
	if (iRet != 0) {
		printf("normal-error:%d-%s\n", mysql_errno(pMySQL), mysql_error(pMySQL));
	}
	
	mysql_close(pMySQL);
	mysql_server_end();

	return 0;
}

// stmt write
int stmt_write(MYSQL *pMySQL)
{
	int iRet = -1, iRetFun = -1;
	char strSql[2048] = { 0 };
	
	sprintf_s(strSql, "DROP TABLE IF EXISTS %s", g_strTableName);
	iRet = mysql_query(pMySQL, strSql);
	if (iRet != 0) {
		goto _end_fun;
	}

	sprintf_s(strSql, "CREATE TABLE %s(col1 INT, col2 VARCHAR(40), col3 SMALLINT, col4 TIMESTAMP)", g_strTableName);
	iRet = mysql_query(pMySQL, strSql);
	if (iRet != 0) {
		goto _end_fun;
	}

	// 变量初始化
	MYSQL_STMT		 *pStmt = nullptr;
	MYSQL_BIND		 bind[3];
	my_ulonglong	 affected_rows = 0;
	int				 param_count = 0;
	short			 small_data = 0;
	int				 int_data = 0;
	char			 str_data[STRING_SIZE] = { 0 };
	unsigned long	 str_length = 0;
	bool			 is_null = 0;
	memset(bind, 0, sizeof(bind));

	pStmt = mysql_stmt_init(pMySQL);
	if (pStmt == nullptr) {
		goto _end_fun;
	}

	sprintf_s(strSql, "INSERT INTO %s(col1,col2,col3) VALUES(?,?,?)", g_strTableName);

	iRet = mysql_stmt_prepare(pStmt, strSql, strlen(strSql));
	if (iRet != 0) {
		goto _end_fun;
	}

	param_count = mysql_stmt_param_count(pStmt);   // 获取sql语言中占位符的个数
	if (param_count != 3) {
		goto _end_fun;
	}

	// int
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (char *)&int_data;
	bind[0].is_null = 0;	// 是否为null
	bind[0].length = 0;		// 只有字符串时才有效

							// varchar
	bind[1].buffer_type = MYSQL_TYPE_STRING;
	bind[1].buffer = (char *)str_data;
	bind[1].buffer_length = STRING_SIZE;	// str最大长度
	bind[1].is_null = 0;
	bind[1].length = &str_length;

	// small int
	bind[2].buffer_type = MYSQL_TYPE_SHORT;
	bind[2].buffer = (char *)&small_data;
	bind[2].is_null = &is_null;
	bind[2].length = 0;

	// 绑定参数
	iRet = mysql_stmt_bind_param(pStmt, bind);
	if (iRet != 0) {
		goto _end_fun;
	}

	// insert 1 row
	int_data = 1;
	strncpy(str_data, "first string", STRING_SIZE);
	str_length = strlen(str_data);
	is_null = 1;	// INSERT SMALLINT data as NULL

	iRet = mysql_stmt_execute(pStmt);
	if (iRet != 0) {
		goto _end_fun;
	}

	affected_rows = mysql_stmt_affected_rows(pStmt);  // 获取受影响的行信息
	if (affected_rows != 1) {
		goto _end_fun;
	}

	// insert 2 row
	int_data = 2;
	strncpy(str_data, "second string", STRING_SIZE);
	str_length = strlen(str_data);
	small_data = 1000;         /* smallint */
	is_null = 0;               /* reset */

	iRet = mysql_stmt_execute(pStmt);
	if (iRet != 0) {
		goto _end_fun;
	}

	affected_rows = mysql_stmt_affected_rows(pStmt);  // 获取受影响的行信息
	if (affected_rows != 1) {
		goto _end_fun;
	}

	iRetFun = 0;
	iRet = 0;

_end_fun:
	if (iRetFun != 0) {
		if (pMySQL != nullptr) {
			printf("normal-error:%d-%s\n", mysql_errno(pMySQL), mysql_error(pMySQL));
		}
		if (pStmt != nullptr) {
			printf("stmt-error:%d-%s\n", mysql_stmt_errno(pStmt), mysql_stmt_error(pStmt));
		}
	}
	mysql_stmt_close(pStmt);
	
	return 0;
}

// stmt read
int stmt_read(MYSQL *pMySQL)
{
	int iRet = -1, iRetFun = -1;
	char strSql[2048] = { 0 };

	MYSQL_STMT    *pStmt = nullptr;
	MYSQL_BIND    bind[4];
	MYSQL_RES     *prepare_meta_result = nullptr;
	MYSQL_TIME    ts;
	unsigned long length[4] = { 0 };
	int           param_count = 0, column_count = 0, row_count = 0;
	short         small_data = 0;
	int           int_data = 0;
	char          str_data[STRING_SIZE] = { 0 };
	bool          is_null[4] = { 0 };
	bool          error[4] = { 0 };

	/* Prepare a SELECT query to fetch data from test_table */
	pStmt = mysql_stmt_init(pMySQL);
	if (!pStmt){
		goto _end_fun;
	}

	sprintf_s(strSql, "SELECT col1, col2, col3, col4 FROM %s", g_strTableName);
	if (mysql_stmt_prepare(pStmt, strSql, strlen(strSql))){
		goto _end_fun;
	}

	param_count = mysql_stmt_param_count(pStmt);
	if (param_count != 0) {
		goto _end_fun;
	}

	// 获取结果集元信息
	prepare_meta_result = mysql_stmt_result_metadata(pStmt);
	if (!prepare_meta_result) {
		goto _end_fun;
	}

	// 获取查询中的总列数
	column_count = mysql_num_fields(prepare_meta_result);
	if (column_count != 4) {
		goto _end_fun;
	}

	if (mysql_stmt_execute(pStmt)) {
		goto _end_fun;
	}

	memset(bind, 0, sizeof(bind));

	// 第1列 int
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (char *)&int_data;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];

	// 第1列 varchar
	bind[1].buffer_type = MYSQL_TYPE_STRING;
	bind[1].buffer = (char *)str_data;
	bind[1].buffer_length = STRING_SIZE;	// str最大长度
	bind[1].is_null = &is_null[1];
	bind[1].length = &length[1];
	bind[1].error = &error[1];

	// 第1列 smallint
	bind[2].buffer_type = MYSQL_TYPE_SHORT;
	bind[2].buffer = (char *)&small_data;
	bind[2].is_null = &is_null[2];
	bind[2].length = &length[2];
	bind[2].error = &error[2];

	// 第1列 timestamp
	bind[3].buffer_type = MYSQL_TYPE_TIMESTAMP;
	bind[3].buffer = (char *)&ts;
	bind[3].is_null = &is_null[3];
	bind[3].length = &length[3];
	bind[3].error = &error[3];

	// bind结果集
	if (mysql_stmt_bind_result(pStmt, bind)) {
		goto _end_fun;
	}

	// 将所有结果缓冲到客户端
	if (mysql_stmt_store_result(pStmt)) {
		goto _end_fun;
	}

	// 取得所有行数据
	row_count = 0;
	printf("get all rows...\n");
	while (!mysql_stmt_fetch(pStmt))
	{
		row_count++;
		printf("  row:%02d\n", row_count);

		// column 1
		printf("   column1 (int)  : ");
		if (is_null[0])
			printf(" NULL\n");
		else
			printf(" %d(%ld)\n", int_data, length[0]);

		// column 2
		printf("   column2 (varchar)   : ");
		if (is_null[1])
			printf(" NULL\n");
		else
			printf(" %s(%ld)\n", str_data, length[1]);

		// column 3
		printf("   column3 (smallint) : ");
		if (is_null[2])
			printf(" NULL\n");
		else
			printf(" %d(%ld)\n", small_data, length[2]);

		// column 4
		printf("   column4 (timestamp): ");
		if (is_null[3])
			printf(" NULL\n");
		else
			printf(" %04d-%02d-%02d %02d:%02d:%02d (%ld)\n",
				ts.year, ts.month, ts.day,
				ts.hour, ts.minute, ts.second,
				length[3]);
		printf("\n");
	}

	if (row_count != 2) {
		goto _end_fun;
	}

	iRetFun = 0;
	iRet = 0;

_end_fun:
	if (iRetFun != 0) {
		if (pMySQL != nullptr) {
			printf("normal-error:%d-%s\n", mysql_errno(pMySQL), mysql_error(pMySQL));
		}
		if (pStmt != nullptr) {
			printf("stmt-error:%d-%s\n", mysql_stmt_errno(pStmt), mysql_stmt_error(pStmt));
		}
	}

	if (prepare_meta_result != nullptr) {
		mysql_free_result(prepare_meta_result);
	}
	if (pStmt != nullptr) {
		mysql_stmt_close(pStmt);
	}

	return 0;
}

参考:https://dev.mysql.com/doc/refman/8.0/en/mysql-stmt-execute.html和https://dev.mysql.com/doc/refman/8.0/en/mysql-stmt-fetch.html

若需MySQL官方测试代码,请查看:https://download.csdn.net/download/a1173356881/11265937