使用mysql接口操作数据库
如果是32位程序用已经编译好的库的话就去下载低版本的库
官网下载地址:http://ftp.nchu.edu.tw/MySQL/downloads/connector/c/index.html#downloads
1包含mysql提供的头文件mysql.h
2包含mysql的libmysql.lib和libmysql.dll动态库
#include<mysql.h>之前一定要加上#include<windows.h>否则会产生如下错误:
代码例子如下
#include <iostream>
#include <mysql.h>//在工程属性中设置好头文件路径,可以拷贝到工程目录直接设置当前路径(.\xxx)
using namespace std;
#pragma comment(lib, "libmysql.lib")//在工程属性中设置好头文件路径,可以拷贝到工程目录直接设置当前路径(.\xxx)
int main()
{
MYSQL mysql;
string sql;
int result;
MYSQL_RES *res = nullptr;
MYSQL_FIELD *field = nullptr;
int field_count = 0;
MYSQL_ROW row;
//为每个字段设置变量保存
char sid[50] = { };
char name[50] = { };
char cid[50] = { "cr001" };
//初始化
if (mysql_init(&mysql) == nullptr)
return 0;
//设置字符集,支持中文编码,注意不过数据插入后最终还是默认是utf-8会自动转换
if (mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk") != 0)
{
printf("mysql_options:%s\n", mysql_error(&mysql));
goto SAFE_EXIT;
}
//连接数据库服务器
if (mysql_real_connect(&mysql, "localhost", "root", "root",
"student", 3306, nullptr, 0) == nullptr)
{
printf("mysql_real_connect:%s\n", mysql_error(&mysql));
goto SAFE_EXIT;
}
printf("connect success\n");
//插入数据
sql = "insert into t_student values('s100', '测试', 'cr001')";
//执行mysql语句
result = mysql_query(&mysql, sql.c_str());
if (result == 0)
{
printf("insert success\n");
}
else
{
printf("mysql_query:%s\n", mysql_error(&mysql));
//goto SAFE_EXIT;
}
//删除数据
sql = "DELETE FROM T_STUDENT WHERE STUDENT_ID = 'S100'";
result = mysql_query(&mysql, sql.c_str());
if (result == 0)
{
printf("delete success\n");
}
else
{
printf("delete:%s\n", mysql_error(&mysql));
//goto SAFE_EXIT;
}
//修改
sql = "update T_STUDENT set student_name='测试' WHERE STUDENT_ID = 'S001'";
result = mysql_query(&mysql, sql.c_str());
if (result == 0)
{
printf("update success\n");
}
else
{
printf("update:%s\n", mysql_error(&mysql));
//goto SAFE_EXIT;
}
//查询,用where 1=1这个小技巧是为了不用写一些冗余的判断条件,这样可以一条sql语句组合所有的条件,不然还有判断那个字段是否输入
sql = "select * from t_student where 1 = 1 ";
if (strlen(sid)>0)
{
sql += " and student_id = '";
sql += sid;
sql += "'";
}
if (strlen(name)>0)
{
sql += " and student_name = '";
sql += name;
sql += "'";
}
if (strlen(cid)>0)
{
sql += " and class_id = '";
sql += cid;
sql += "'";
}
//执行sql语句
result = mysql_query(&mysql, sql.c_str());
if (result == 0)
{
printf("mysql_query success\n");
}
else
{
printf("mysql_query:%s\n", mysql_error(&mysql));
goto SAFE_EXIT;
}
//保存查询结果到res
res = mysql_store_result(&mysql);
if (res == nullptr)
{
printf("mysql_store_result:%s\n", mysql_error(&mysql));
goto SAFE_EXIT;
}
//打印字段
//从res结果中获取字段的数量
field_count = mysql_num_fields(res);
//从res结果中获取字段的属性,如字段名称,获取的是一个结构体数组
field = mysql_fetch_fields(res);
for (int i = 0; i < field_count; i++)
{
printf("%-20s", field[i].name);
}
printf("\n");
//第二种方式获取字段方式
//field = mysql_fetch_field(res);
//while (field != nullptr)
//{
// printf("%s ", field->name);
// field = mysql_fetch_field(res);
//}
//这个执行函数可以包含二进制数据,因为二进制数有包含’\0‘字符,mysql_query执行不了包含二进制的语句
//mysql_real_query();
//从res结果中获取一行数据,遍历获取所有数据
row = mysql_fetch_row(res);
while (row != nullptr)
{
for (int i = 0; i < field_count; i++)
printf("%-20s", row[i]);
printf("\n");
//获取下一行
row = mysql_fetch_row(res);
}
SAFE_EXIT:
//释放查询结构
if (res != nullptr)
mysql_free_result(res);
mysql_close(&mysql);
return 0;
}