C语言连接mysql

配置

mysql8.7 vs2019
首先我们需要在vs中导入mysql的包lib和include，点击解决方案->属性然后在VC++目录和包目录中导入mysql目录下的lib和include，然后再把lib.dll放到项目文件下。
然后就可以进行连接了，需要注意的坑点就是在向数据库写入中文的时候需要设置字符集为gbk

if (!mysql_set_character_set(&mysqlConnect, "gbk"))
	{
		printf("New client character set: %s\n", mysql_character_set_name(&mysqlConnect));
	}

源代码

#include <stdio.h>
#include <Windows.h>
#include <stdlib.h>
#include <winsock.h>
#include <mysql.h>
#include <string.h>
MYSQL mysqlConnect;  //数据源指针
MYSQL_RES* res;  //查询结果集
MYSQL_FIELD* field;  //包含字段信息的结构指针
MYSQL_ROW nextRow;  //存放查询sql语句字符串数组
FILE* fp;
struct student {
	long long number;
	char name[20];
	char address[50];
	long long telephoneNumber;
	char sex[20];
};
struct Database{
	int count = 0;
	student stu[50];
}data;
void addstudent() {
	int cnt = data.count;
	printf("请输入学生信息(学号，姓名，性别，地址，电话)\n");
	scanf("%lld %s %s %s %lld", &data.stu[cnt].number, data.stu[cnt].name, data.stu[cnt].sex, data.stu[cnt].address, &data.stu[cnt].telephoneNumber);
	data.count = cnt+1;
	printf("更新成功\n");
}
void delestudent() {
	int op;
	printf("请输入删除第几行\n");
	scanf("%d", &op);
	for (int i = op - 1; i < data.count; i++) {
		data.stu[i] = data.stu[i + 1];
	}
	data.count--;
	printf("删除成功");
}
void readdata() {
	printf("学号\t\t姓名\t性别\t地址\t\t\t电话\n");
	for (int i = 0; i < data.count; i++) {
		printf("%-16lld%-8s%-8s%-24s%lld\n", data.stu[i].number, data.stu[i].name, data.stu[i].sex, data.stu[i].address, data.stu[i].telephoneNumber);
	}
}
void write_database(student s) {
	char sql[500];
	sprintf_s(sql, "INSERT INTO student (`number`, `name`, `sex`, `address`, `phone`) VALUES ('%lld', '%s', '%s', '%s', '%lld')", s.number, s.name, s.sex, s.address, s.telephoneNumber);
	printf("%s\n", sql);
	int err = mysql_real_query(&mysqlConnect,sql,(unsigned int)strlen(sql));
	if (err != 0) {
		printf("执行查询时出现异常: %s", mysql_error(&mysqlConnect));
	}
	else printf("写入成功");
}
void read_database() {
	char sql[500];
	sprintf_s(sql, "select * from student");
	mysql_real_query(&mysqlConnect, sql, strlen(sql));
	res = mysql_store_result(&mysqlConnect);
	printf("学号\t\t姓名\t性别\t地址\t\t\t电话\n");
	while (nextRow = mysql_fetch_row(res)) {
		printf("%-16s%-8s%-8s%-24s%s\n", nextRow[1], nextRow[2], nextRow[3], nextRow[4], nextRow[5]);
	}
}
int main() {
	fp = fopen("t.bin", "rb+");
	fread(&data, sizeof(Database), 1, fp);
	if (&data == NULL) {
		data.count = 0;
	}
	mysql_init(&mysqlConnect);//分配对象 p4302
	if (!(mysql_real_connect(&mysqlConnect, "localhost", "root", "", "p_user", 3306, NULL, 0))) {
		printf("Failed to access to the database...Error: %s\n", mysql_error(&mysqlConnect));
		return 0;
	}
	else printf("连接数据库成功\n");
	if (!mysql_set_character_set(&mysqlConnect, "gbk"))
	{
		printf("New client character set: %s\n", mysql_character_set_name(&mysqlConnect));
	}
	printf("1.在文件中写入数据\n2.读取文件数据\n3.向数据库写入数据\n4.读取数据库数据\n5.删除学生数据\n6.结束\n");
	int op;
	while (scanf("%d",&op) != EOF) {
		student st;
		if (op == 3) {
			printf("请输入学生信息(学号，姓名，性别，地址，电话)\n");
			scanf("%lld %s %s %s %lld", &st.number, st.name, st.sex, st.address, &st.telephoneNumber);
			write_database(st);
		}
		else if (op == 4) {
			read_database();
		}
		else if (op == 2) {
			readdata();
		}
		else if (op == 1) {
			addstudent();
		}
		else  if (op == 5) {
			delestudent();
		}
		else  break;
	}
	fwrite(&data, sizeof(Database), 1, fp);
	mysql_close(&mysqlConnect);
	fclose(fp);
	return 0;
}