C++使用MySQL官方的C API访问MySQL数据库

这篇文章是一个简单的C++使用MySQL官方的C API访问MySQL数据库的代码示例。

//main.h
#ifndef _H_
#define _H_

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

#pragma comment(lib,"libmysql.lib")

#define insert_prepare "insert into test_tb value(? , ? , ?)"

//菜单函数
void f_printmenu(void);
//新建函数
int f_insert(MYSQL* mysql);
//删除函数
int f_delete(MYSQL* mysql);
//修改函数
int f_modify(MYSQL* mysql);
//查询函数
int f_select(MYSQL* mysql);

#endif

//数据表包含商品编号、名称、价格

//main.cpp
#include"main.h"

int main()
{
#if 1
	enum { _insert = 1, _delete, _modify, _select };
#else
	const int _insert = 1, _delete = 2, _modify = 3, _select = 4;
#endif
	int choose = 0;
	MYSQL* mysql;//mysql句柄

	//初始化mysql句柄
	mysql = mysql_init(NULL);
	if (mysql == NULL)
	{
		printf("mysql_init error\n");
		system("pause");
		return -1;
	}
	printf("mysql_init ok\n");

	//连接mysql数据库
	mysql = mysql_real_connect(mysql, "localhost", "xxx", "xxx", "test_db", 3306, NULL, 0);//返回值和参数mysql是相同值
	if (mysql == NULL)
	{
		printf("mysql_real_connect error\n");
		system("pause");
		return -1;
	}
	printf("mysql_real_connect ok");

	//获取当前进程/会话使用的字符集
	printf("before,字符集:%s\n", mysql_character_set_name(mysql));

	/*设置当前进程/会话的默认字符集
	mysql_set_character_set(mysql,"utf8");
	printf("after,字符集:%s\n", mysql_character_set_name(mysql));*/

	f_printmenu();
	scanf("%d", &choose);
	system("cls");
	while (choose)
	{
		switch (choose)
		{
		case _insert:
			f_insert(mysql);
			break;
		case _delete:
			f_delete(mysql);
			break;
		case _modify:
			f_modify(mysql);
			break;
		case _select:
			f_select(mysql);
			break;
		default:
			printf("输入错误\n");
			break;
		}
		f_printmenu();
		scanf("%d", &choose);
		system("cls");
	}

	//关闭mysql句柄
	mysql_close(mysql);
	system("pause");
	return 0;
}

//菜单函数
void f_printmenu(void)
{
	printf("\
		   ******************\n\
		   *      菜单      *\n\
		   *      1.新建    *\n\
		   *      2.删除    *\n\
		   *      3.修改    *\n\
 		   *      4.查询    *\n\
		   *      0.退出    *\n\
		   ******************\n");
}

//新建函数
int f_insert(MYSQL* mysql)
{
	int number = 0;
	char* tpye = (char*)malloc(20);
	float price = 0;
	int num = 0;
	int iden = 0;
	MYSQL_STMT* stmt = NULL;
	int param_count = 0;
	MYSQL_BIND my_bind[3];
	unsigned long len = 0;

	do
	{
		printf("请输入编号\n");
		scanf("%d", &number);
		printf("请输入类型\n");
		scanf("%s", tpye);
		printf("请输入价格\n");
		scanf("%f", &price);
		len = strlen(tpye);

		//初始化预处理环境句柄mysql_stmt
		stmt = mysql_stmt_init(mysql);
		if (stmt == NULL)
		{
			printf("mysql_stmt_init error:%s\n", mysql_error(mysql));
			system("pause");
			return -1;
		}
		printf("mysql_stmt_init ok\n");

		//向初始化后的环境句柄mysql_stmt添加sql命令
		if (mysql_stmt_prepare(stmt, insert_prepare, strlen(insert_prepare)))
		{
			printf("mysql_stmt_prepare error:%s\n", mysql_stmt_error(stmt));
			system("pause");
			return -1;
		}
		printf("mysql_stmt_prepare ok\n");

		//获取需要绑定变量的个数，即？数量
		param_count = mysql_stmt_param_count(stmt);
		if (param_count != 3)
		{
			printf("mysql_stmt_param_count error:%s\n", mysql_stmt_error(stmt));
			system("pause");
			return -1;
		}
		printf("mysql_stmt_param_count ok\n");

		//设置需要绑定的实参
		memset(my_bind, 0, sizeof(my_bind));
		my_bind[0].buffer_type = MYSQL_TYPE_LONG;
		my_bind[0].buffer = (char*)&number;
		my_bind[0].is_null = 0;
		my_bind[0].length = 0;

		my_bind[1].buffer_type = MYSQL_TYPE_STRING;
		my_bind[1].buffer = tpye;
		my_bind[1].is_null = 0;
		my_bind[1].length = &len;

		my_bind[2].buffer_type = MYSQL_TYPE_FLOAT;
		my_bind[2].buffer = (char*)&price;
		my_bind[2].is_null = 0;
		my_bind[2].length = 0;

		//绑定？对应的实参
		if (mysql_stmt_bind_param(stmt, my_bind))
		{
			printf("mysql_stmt_bind_param error:%s\n", mysql_stmt_error(stmt));
			system("pause");
			return -1;
		}
		printf("mysql_stmt_bind_param ok\n");

		//执行预处理的sql命令
		if (mysql_stmt_execute(stmt))
		{
			printf("mysql_stmt_execute error:%s\n", mysql_stmt_error(stmt));
			system("pause");
			return -1;
		}
		printf("mysql_stmt_execute ok\n");

		//获取影响的行数
		num = mysql_stmt_affected_rows(stmt);
		printf("新建%d行记录\n", num);

		printf("继续新建输入1，返回菜单输入0\n");
		scanf("%d", &iden);
	} while (iden);

	//关闭预处理的环境句柄mysql_stmt
	if (mysql_stmt_close(stmt))
	{
		printf("mysql_stmt_close error:%s\n", mysql_stmt_error(stmt));
		system("pause");
		return -1;
	}
	printf("mysql_stmt_close ok\n");

	return 0;
}

//删除函数
int f_delete(MYSQL* mysql)
{
	int number = 0;
	char* cnumber = (char*)malloc(10);
	char* buffer = (char*)malloc(100);
	int iden = 0;

	do
	{
		printf("请输入编号\n");
		scanf("%d", &number);
		itoa(number, cnumber, 10);
		strcpy(buffer, "delete from test_tb where test_tb.number=");
		strcat(buffer, cnumber);

		//开启事务处理，并且设置手动commit
		mysql_query(mysql, "start transcation");
		mysql_query(mysql, "set autocommit=0");

		//向mysql发送操作命令
		int ret = mysql_query(mysql, buffer);
		if (ret == 0)
		{
			printf("mysql_query ok\n");

			//获取影响的行数
			int num = mysql_affected_rows(mysql);//不能放在commit之后，不然返回的值不对
			printf("删除%d行记录\n", num);

			mysql_query(mysql, "commit");
		}
		else
		{
			printf("mysql_query error:%s\n", mysql_error(mysql));
			mysql_query(mysql, "callback");
		}

		printf("继续新建输入1，返回菜单输入0\n");
		scanf("%d", &iden);
	} while (iden);

	return 0;
}

//修改函数
int f_modify(MYSQL* mysql)
{
	int number = 0;
	char* cnumber = (char*)malloc(10);
	int price = 0;
	char* cprice = (char*)malloc(10);
	char* buffer = (char*)malloc(100);
	int iden = 0;

	do
	{
		printf("请输入编号\n");
		scanf("%d", &number);
		printf("请输入价格\n");
		scanf("%d", &price);
		itoa(number, cnumber, 10);
		gcvt(price, 10, cprice);
		strcpy(buffer, "update test_tb set test_tb.price=");
		strcat(buffer, cprice);
		strcat(buffer, "where test_tb.number=");
		strcat(buffer, cnumber);

		//开启事务处理，并且设置手动commit
		mysql_query(mysql, "start transcation");
		mysql_query(mysql, "set autocommit=0");

		//向mysql发送操作命令
		int ret = mysql_query(mysql, buffer);
		if (ret == 0)
		{
			printf("mysql_query ok\n");

			//获取影响的行数
			int num = mysql_affected_rows(mysql);
			printf("修改%d行记录\n", num);

			mysql_query(mysql, "commit");
		}
		else
		{
			printf("mysql_query error:%s\n", mysql_error(mysql));
			mysql_query(mysql, "callback");
		}

		printf("继续新建输入1，返回菜单输入0\n");
		scanf("%d", &iden);
	} while (iden);

	return 0;
}

//查询函数
int f_select(MYSQL* mysql)
{
	MYSQL_RES* res = NULL;
	MYSQL_ROW row;
	MYSQL_FIELD* field = NULL;
	int number = 0;
	char* buffer = (char*)malloc(100);
	char* cnumber = (char*)malloc(10);
	int iden = 0;

	do
	{
		printf("请输入编号\n");
		scanf("%d", &number);
		itoa(number, cnumber, 10);
		strcpy(buffer, "select * from test_tb where number = ");
		strcat(buffer, cnumber);

		//向mysql发送命令
		int ret = mysql_query(mysql, buffer);
		if (ret == 0)
		{
			printf("mysql_query ok\n");
		}
		else
		{
			printf("mysql_query error:%s\n", mysql_error(mysql));
		}

		//从mysql句柄中获取列数
		//int num = mysql_field_count(mysql);

		//获取结果集
		res = mysql_store_result(mysql);
		if (res == NULL)
		{
			printf("mysql_store_result error: %s\n", mysql_error(mysql));
			system("pause");
			return -1;
		}

		//从结果集中获取列数
		int num = mysql_num_fields(res);

		//获取表头信息
		field = mysql_fetch_fields(res);
		if (field == NULL)
		{
			printf("mysql_fetch_fields error: %s\n", mysql_error(mysql));
		}
		else
		{
			for (int i = 0; i < num; i++)
			{
				printf("%s\t", field[i].name);//my_field[i].name表示列名
			}
			printf("\n");
		}

		//从结果集中获取每一行记录
		while (row = mysql_fetch_row(res))
		{
			for (int i = 0; i < num; i++)//打印每一行记录
			{
				printf("%s\t", row[i]);
			}
			printf("\n");
		}

		//释放结果集
		mysql_free_result(res);

		printf("继续查询输入1，返回菜单输入0\n");
		scanf("%d", &iden);
	} while (iden);

	return 0;
}