1. Linux平台准备
（1）安装SDK开发包的命令
sudo apt-get install libmysqlclient-dev（2）编译时需要链接的库:-lmysqlclient
2. mysql 的初始化和清理
#include <mysql/mysql.h>
MYSQL mysql1; //创建句柄
mysql_init(&mysql1); //初始化句柄
mysql_close(&mysql); //关闭句柄3. mysql 的连接登录
（1）连接登录 mysql_real_connect
const char *p = "192.168.126.215"; //配置ip
const char *user = "root"; //配置用户名
const char *password" = "123456"; //配置密码
const char *db = "test"; //配置要打开的数据库
if ( mysql_real_connect(&mysql1, ip, user, password, db, 13306, nullptr, 0) == 0) //等于0则打开失败
{
cout<<"connect error"<<endl;
}
else
{
cout<<"connect ok"<<endl;
}（2）设置断开自动重连
//设置断开重连的功能
int ret = 1;
mysql_options(&mysql1, MYSQL_OPT_RECONNECT, &ret);
//中间的宏表示如果发现连接丢失，则启动与服务器的自动再连接；
//设置自动检测的超时时间
int timeout = 3;
mysql_options(&mysql1, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
//中间的宏表示以秒为单位的连接超时，即超过3秒就启动重新连接
//设置是否连接的检测
if( mysql_ping(&mysql1) != 0 )
{
cout<<"connect failed"<<endl;
}
else
{
cout<<"connect success"<<endl;
}4. mysql 的数据查询
（1）执行sql语句，注意：执行sql语句后，必须获取结果集并且清理；
//创建表
string sql = "create table person(id int, name varchar(20), age int";
mysql_real_query(&mysql, sql.c_str(), sql.size());
//mysql_query(&mysql, sql.c_str()); 和上面不同的是，这里并没有指定长度，执行效率没有上面的快
//对于包含二进制数据的查询，必须使用mysql_real_query(),因为二进制代码数据可能包含‘\0’字符；
//插入数据
string sql2 = "insert into person(id, name, age)values(1, 'zhangsan', 14);
mysql_real_query(&mysql, sql2.c_str(), sql2.size());
//查询数据
string sql3 = "select * from person";
mysql_real_query(&mysql, sql3.c_str(), sql3.size());（2）获取结果集
//获取结果集
MYSQL_RES *result; //定义一个MYSQL_RES结构；
result = mysql_use_result(&mysql1); //将查询结构保存在result中；
if(result == nullptr)
{
cout<<"select error"<<endl;
}
//获取字段个数，即查询获得的结果里有几列数据
int nums = 0;
nums = mysql_num_fields(result); //属于表结构的获取
//获取字段名，即列的名称
MYSQL_FIELD * fields; //字段名的数据类型的变量
fields = mysql_fetch_fields(result); //属于表结构的获取
for(int i = 0; i < num; i++)
{
cout<<fields[i].name<<"|";
}
cout<<endl;
//遍历显示结果
MYSQL_ROW row; //存储每一行数据的数据类型的变量
while( (row = mysql_fetch_row(result)) != nullptr) //mysql_fetch_row()函数从指定的结果集中获取一行数据返回给row，是数组的形式，即row内部是字符串数组指针（二级指针）
{
for(i = 0; i < nums; i++)
{
cout<<row[i]<<"|";
}
cout<<endl;
}（3）清理结果集
//释放结果集的指针空间
mysql_free_result(result);整体示例如下：
#include <iostream>
#include <mysql/mysql.h>
#include <unistd.h>
using namespace std;
int main(int argc, char **argv)
{
MYSQL mysql1;
mysql_init(&mysql1);
const char *ip = "192.168.226.128";
const char *user = "root";
const char *password = "123456";
const char *db = "test";
if(mysql_real_connect(&mysql1, ip, user, password, db, 13306, nullptr,0) == 0)
{
cout<<"connect error"<<endl;
}
else
{
cout<<"connect ok"<<endl;
}
string sql = "create table person(id int, name varchar(20), age int)";
mysql_real_query(&mysql1, sql.c_str(), sql.size());
// string sql1 = "select"
int ret = 1;
mysql_options(&mysql1, MYSQL_OPT_RECONNECT, &ret);
int timeout = 3;
mysql_options(&mysql1, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
// while(1) //测试断开重连功能
// {
// if(mysql_ping(&mysql1) != 0)
// {
// cout<<"connect failed"<<endl;
// }
// else
// {
// // cout<<"connnect ok"<<endl;
// }
// sleep(1);
// }
string sql2 = "insert into person(id, name, age)values(1,'zhangsan',12)";
mysql_real_query(&mysql1, sql2.c_str(), sql2.size());
string sql22 = "insert into person(id, name, age)values(2,'lisi',16)";
mysql_real_query(&mysql1, sql22.c_str(), sql22.size());
string sql3 = "select * from person";
mysql_real_query(&mysql1, sql3.c_str(), sql3.size());
MYSQL_RES * result = mysql_use_result(&mysql1);
if(result == nullptr)
{
cout<<"select error"<<endl;
}
int nums = 0;
nums = mysql_num_fields(result);
cout<<"nums = "<<nums<<endl;
MYSQL_FIELD * fields = mysql_fetch_fields(result);
for(int i = 0; i < nums; i++)
{
cout<<fields[i].name<<"|";
}
cout<<endl;
MYSQL_ROW row;
while((row = mysql_fetch_row(result)) != nullptr)
{
for(int i =0; i < nums; i++)
{
cout<<row[i]<<"|";
}
cout<<endl;
}
mysql_free_result(result);
mysql_close(&mysql1);
return 0;
}运行结果如下：
5. mysql 显示中文
//设置中文的思路是统一设置字符集为utf8;
const char * csname = "utf8";
mysql_set_character_set(&mysql, csname);6. 图片存储与读取保存的例子
#include <iostream>
#include <mysql/mysql.h>
#include <thread>
#include <string>
#include <sstream>
#include <map>
#include <chrono>
#include <fstream>
using namespace std;
using namespace chrono;
int main()
{
//初始化mysql上下文
MYSQL mysql;
//单线程模式 mysql_init自动调用 mysql_library_init 线程不安全
mysql_init(&mysql);
const char* host = "192.168.226.128";
//const char *host = "192.168.0.203";
const char* user = "root";
const char* pass = "123456";
const char* db = "test"; //数据库名称
//CLIENT_MULTI_STATEMENTS 支持多条sql语句
if (!mysql_real_connect(&mysql, host, user, pass, db, 13306, 0, CLIENT_MULTI_STATEMENTS))
//if (!mysql_real_connect(&mysql, host, user, pass, db, 3306, 0, 0))
{
cout << "mysql connect failed!" << mysql_error(&mysql) << endl;
}
else
{
cout << "mysql connect success!" << endl;
}
string sql = "";
//1 创建好存放二进制数据的表 t_data
sql = "CREATE TABLE IF NOT EXISTS `t_data` (\
`id` int AUTO_INCREMENT,\
`name` varchar(1024),\
`data` blob,\
`size` int,\
PRIMARY KEY(`id`))";
int re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << mysql_error(&mysql) << endl;
}
//2 清空表 truncate t_data
sql = "truncate t_data";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cerr << mysql_error(&mysql) << endl;
}
//3 初始化stmt mysql_stmt_init
MYSQL_STMT* stmt = mysql_stmt_init(&mysql);
if (!stmt)
{
cerr << "mysql_stmt_init failed!" << mysql_error(&mysql) << endl;
}
//4 预处理sql语句
sql = "INSERT INTO `t_data` (name,data,size) VALUES(?,?,?)";
if (mysql_stmt_prepare(stmt, sql.c_str(), sql.size()))
{
cerr << "mysql_stmt_prepare failed!" << mysql_stmt_error(stmt) << endl;
}
//5 打开并读取文件
string path = ".//";
string filename = "mysql.jpg";
cout << path + filename << endl;
//读取二进制
fstream in(path + filename, ios::in | ios::binary);
if (!in.is_open())
{
cerr << "file " << filename << " open failed!" << endl;
}
//文件指针移动到结尾处
in.seekg(0, ios::end);
//文件大小和文件二进制地址
int filesize = in.tellg();
//回到开头
in.seekg(0, ios::beg);
char* data = new char[filesize];
int readed = 0; //已经读了多少
while (!in.eof())
{
in.read(data + readed, filesize - readed);
//读取了多少字节
if (in.gcount() <= 0)
break;
readed += in.gcount();
}
in.close();
//6 绑定字段
MYSQL_BIND bind[3] = { 0 };
bind[0].buffer_type = MYSQL_TYPE_STRING; //name 文件名
bind[0].buffer = (char*)filename.c_str();
bind[0].buffer_length = filename.size();
bind[1].buffer_type = MYSQL_TYPE_BLOB; //data 文件二进制内容
bind[1].buffer = data; //二进制文件
bind[1].buffer_length = filesize;
//文件大小
bind[2].buffer_type = MYSQL_TYPE_LONG;
bind[2].buffer = &filesize;
if (mysql_stmt_bind_param(stmt, bind) != 0)
{
cerr << "mysql_stmt_bind_param failed! " << mysql_stmt_error(stmt) << endl;
}
//7 执行stmt sql
if (mysql_stmt_execute(stmt) != 0)
{
cerr << "mysql_stmt_execute failed! " << mysql_stmt_error(stmt) << endl;
}
delete data;
mysql_stmt_close(stmt);
sql = "select * from t_data";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cerr << "mysql query failed!" << mysql_error(&mysql) << endl;
}
//获取结果集
MYSQL_RES* res = mysql_store_result(&mysql);
if (!res)
{
cerr << "mysql_store_result failed!" << mysql_error(&mysql) << endl;
}
//取一行数据
MYSQL_ROW row = mysql_fetch_row(res);
if (!row)
{
cerr << "mysql_fetch_row failed!" << mysql_error(&mysql) << endl;
}
cout << row[0] << " " << row[1] << " " << row[3] << endl;
//获取每列数据的大小
unsigned long* lens = mysql_fetch_lengths(res);
int fnum = mysql_num_fields(res);
for (int i = 0; i < fnum; i++)
{
cout << "[" << lens[i] << "]";
}
filename = "out_";
filename += row[1];
fstream out(filename, ios::out | ios::binary);
if (!out.is_open())
{
cerr << "open file " << filename << " failed!" << endl;
}
out.write(row[2], lens[2]);
out.close();
mysql_close(&mysql);
mysql_library_end();
std::cout << "Mysql 8.0 API!\n";
mysql_close(&mysql);
mysql_library_end();
std::cout << "Mysql 8.0 API!\n";
getchar();
}
![[C++]异常笔记](https://img-blog.csdnimg.cn/df8a33f492f9491084db33646f1c16d6.png)
