C MYSQL 数据库操作Demo

个人博客：www.saoguang.top​​​​​​​

一丶环境：

IDE : Visual Studio 2017

OS : Windows 10 64-bit 1709

Connector C version :  6.1.11 32-bit

 

二丶准备工作：

1.从官网下载好Mysql Connector C 这里提供链接 Mysql Connector/C

2.配置项目设置

(1)Project -> Propertis... -> VC++ Directories -> Include Directories 设置到 下载的Connector 的 include目录。（最好是复制整个include和lib目录到工程目录下）

3.导入 libmysql.lib 以及 mysql.h 头文件

 

/* MySql includes */
#include <mysql.h>
#pragma comment(lib, ".\\lib\\libmysql.lib")

 

注意：在使用#pragma comment 导入时，目录层要用\\隔开。我这使用的是相对目录，.代表当前目录(也就是工程目录)。

 

三丶连接到数据库

1.创建Mysql Connect 实例

 

/* MYSQL Connect instance */
MYSQL mysqlConnect = { 0 };

 

 

2.初始化该实例

 

/* Initilize the MYSQL Connect instance */
mysql_init(&mysqlConnect);

 

 

3.设置字符集(如果不设置，显示中文将乱码,因为Console默认字符集为gbk,如果设置了Console字符集，那就设置成对应的字符集)

 

/* Set character set to gbk, if didn't set this Chinese will show garbled */
mysql_set_character_set(&mysqlConnect, "gbk");

4.连接到数据库实例

 

 

/* Connect to Mysql DB */
if (mysql_real_connect(&mysqlConnect, MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, NULL, MYSQL_PORT, NULL, 0) == NULL)
{
	printf(">数据库连接失败!\n");
	return -1;
}

MYSQL_HOST : mysql服务器地址

 

MYSQL_USER : 用户名MYSQL_PASSWORD : 密码

MYSQL_PORT : mysql 服务器端口

这些都是宏定义，自己设置。

 

/* CONF of the MySql Connect */
#define MYSQL_HOST "0.0.0.0"
#define MYSQL_USER "user"
#define MYSQL_PASSWORD "password"
#define MYSQL_PORT	3306

 

 

5.关闭数据库连接实例

 

mysql_close(&mysqlConnect);

 

 

四丶查询执行SQL语句，查询数据并输出

1.执行SQL

 

if (iReturn = mysql_query(&mysqlConnect, "SELECT * FROM user"))
{
	printf(">数据查询错误!错误代码:%d\n", iReturn);
	return -1;
}

 

 

2.获取结果集并打印数据

 

void showResult(MYSQL * mysql)
{
	MYSQL_RES * mysqlResult = NULL;
	MYSQL_FIELD * mysqlField = NULL;
	MYSQL_ROW mysqlRow;
	int iNumRow = 0,
		iNumField = 0,
		i = 0;
	
	mysqlResult = mysql_store_result(mysql);
	if (mysqlResult == NULL)
	{
		printf(">数据查询失败! %d:%s\n", mysql_errno(mysql), mysql_error(mysql));
		return;
	}

	iNumField = mysql_num_fields(mysqlResult);
	iNumRow = mysql_num_rows(mysqlResult);

	printf(">记录共%d条\n", iNumRow);
	
	while (mysqlField = mysql_fetch_field(mysqlResult))
		printf("%s\t", mysqlField->name);
	printf("\n");

	while (mysqlRow = mysql_fetch_row(mysqlResult))
	{
		for (i = 0; i < iNumField; i++)
		{
			printf("%s\t", mysqlRow[i]);
		}
		printf("\n");
	}
	mysql_free_result(mysqlResult);
}

 

 

 

这里的MYSQL_FIELD 是字段信息结构体

mysql_store_result()函数，将之前的执行数据全部获取到客户端。

mysql_num_fields()函数，获取字段数

 

五丶完整例子

 

 

/* By : SaoGuang */
/* ENV : VS2017 Pro */
#pragma once

/* Stantard C includes */
#include <stdlib.h>
#include <stdio.h>

/* MySql includes */
#include <mysql.h>
#pragma comment(lib, ".\\lib\\libmysql.lib")

/* CONF of the MySql Connect */
#define MYSQL_HOST "0.0.0.0"
#define MYSQL_USER "user"
#define MYSQL_PASSWORD "password"
#define MYSQL_PORT	3306

/* Function declear */
void showResult(MYSQL * mysql);

int main()
{
	int iReturn = 0, i = 0;
	/* MYSQL Connect instance */
	MYSQL mysqlConnect = { 0 };
	/* Initilize the MYSQL Connect instance */
	mysql_init(&mysqlConnect);
	/* Set character set to gbk, if didn't set this Chinese will show garbled */
	mysql_set_character_set(&mysqlConnect, "gbk");
	/* Connect to Mysql DB */
	if (mysql_real_connect(&mysqlConnect, MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, NULL, MYSQL_PORT, NULL, 0) == NULL)
	{
		printf(">数据库连接失败!\n");
		return -1;
	}
	printf(">数据库连接成功!\n");
	if (mysql_select_db(&mysqlConnect, "words_recite"))
	{
		printf(">切换数据库失败!\n");
		return -1;
	}
	//查询数据
	if (iReturn = mysql_query(&mysqlConnect, "SELECT * FROM user"))
	{
		printf(">数据查询错误!错误代码:%d\n", iReturn);
		return -1;
	}
	//显示数据
	showResult(&mysqlConnect);

	mysql_close(&mysqlConnect);
	return 0;
}

void showResult(MYSQL * mysql)
{
	MYSQL_RES * mysqlResult = NULL;
	MYSQL_FIELD * mysqlField = NULL;
	MYSQL_ROW mysqlRow;
	int iNumRow = 0,
		iNumField = 0,
		i = 0;
	
	mysqlResult = mysql_store_result(mysql);
	if (mysqlResult == NULL)
	{
		printf(">数据查询失败! %d:%s\n", mysql_errno(mysql), mysql_error(mysql));
		return;
	}

	iNumField = mysql_num_fields(mysqlResult);
	iNumRow = mysql_num_rows(mysqlResult);

	printf(">记录共%d条\n", iNumRow);
	
	while (mysqlField = mysql_fetch_field(mysqlResult))
		printf("%s\t", mysqlField->name);
	printf("\n");

	while (mysqlRow = mysql_fetch_row(mysqlResult))
	{
		for (i = 0; i < iNumField; i++)
		{
			printf("%s\t", mysqlRow[i]);
		}
		printf("\n");
	}
	mysql_free_result(mysqlResult);
}