最近才开始使用mysql c api,网上的一些资料不能完全符合我的需求,也走了很多弯路,在不断探索中跌跌撞撞前行,积累了一些经验,跟大家分享交流一下。
#include <windows.h>
#include <winsock.h>
#include <iostream>
#include <string>
#include <mysql.h>
#include<io.h>
#include<iostream>
using namespace std;
//必备的一个数据结构
MYSQL mydata;
//连接数据库
int connect()
{
//初始化数据库
if(0==mysql_library_init(0,NULL,NULL))
{
cout<<"mysql_library_init() succeed"<<endl;
}
else
{
cout<<"mysql_library_init() failed"<<endl;
return -1;
}
//初始化数据结构
if(NULL!=mysql_init(&mydata))
{
cout<<"mysql_init() succeed"<<endl;
}
else
{
cout<<"mysql_init() failed"<<endl;
return -1;
}
//在连接数据库之前,设置额外的连接选项
//可以设置的选项很多,这里设置字符集,否则无法处理中文
if(0==mysql_options(&mydata,MYSQL_SET_CHARSET_NAME,"gbk"))
{
cout<<"mysql_options() succeed"<<endl;
}
else
{
cout<<"mysql_options() failed"<<endl;
return -1;
}
//连接数据库
if(NULL!=mysql_real_connect(&mydata,"localhost","root","123456","MySql",3306,NULL,0))
//这里的地址,用户名,密码,端口可以根据自己本地的情况更改
{
cout<<"mysql_real_connect() succeed"<<endl;
return 0;
}
else
{
cout<<"mysql_real_connect() failed"<<endl;
return -1;
}
}
//建表操作
int create(string name1)
{
char sqlBuf[1024]="";
const char *name;
name=name1.c_str();
sprintf(sqlBuf,"CREATE TABLE %s(picture_id varchar(50) not null primary key,picture mediumblob)",name);
if(0==mysql_query(&mydata,sqlBuf))
{
cout<<"mysql_query() create table succeed"<<endl;
return 0;
}
else
{
cout<<"mysql_query() create table failed"<<endl;
mysql_close(&mydata);
exit(1);
}
}
//插入操作,其中picture为图片路径
int insert(string tablename,string id,string picture)
{
char sqlBuf[1024]="";
const char *tablename1;
const char *picture1;
const char *id1;
tablename1=tablename.c_str();
picture1=picture.c_str();
id1=id.c_str();
unsigned long length;
char blog_data[2*50*1024+1];
char szImageData[1024*50];/*图片大小最大不超过50k*/
char szSql[2*1024*50+1];
char szImageName[64];
unsigned long ulReadLength = 0,ulStoreLength = 0;
memset(blog_data, 0x01, sizeof(blog_data));
char* pos = blog_data;
FILE *fp;
fp = fopen(picture1, "rb");
memset(szImageData,0,sizeof(szImageData));
//读取图片大小
ulReadLength = fread(szImageData, 1, 1024*20, fp);
fclose(fp);
memset(szImageName,0,sizeof(szImageName));
memcpy(szImageName,picture1,strlen(picture1));
memset(blog_data,0,sizeof(blog_data));
ulStoreLength = mysql_real_escape_string(&mydata,blog_data,szImageData,ulReadLength);
sprintf(szSql,"insert into %s(picture_id,picture) values('%s','%s')",tablename1,id1,blog_data);
if(0==mysql_query(&mydata,sqlBuf))
{
cout<<"mysql_query() insert data succeed"<<endl;
}
else
{
cout<<"mysql_query() insert data failed"<<endl;
mysql_close(&mydata);
return -1;
}
}
//下载图片一共有两种方式,一种是使用into dumpfile(这种方式只能在安装mysql数据库的主机上使用),另外一种则是所有能够连接上mysql数据库的主机都适用
void download(string tablename,string picture_id,string download_path)
{
char sqlBuf[1024]="";
memset(sqlBuf,0,sizeof(sqlBuf));
//path为图片的下载路径,此处为"D:/picture"路径加图片ID号码
string path=download_path++"/"+picture_id+".bmp";
const char*tablename1=tablename.c_str();
const char*picture_id1=picture_id.c_str();
const char*path1=path.c_str();
sprintf(sqlBuf,"select picture from %s where picture_id='%s' into dumpfile '%s' ",tablename1,picture_id1,path1);
if(0==mysql_query(&mydata,sqlBuf))
{
cout<<"download success!"<<endl;
}
else
{
cout<<"download failed!"<<endl;
exit(1);
}
}
void download1(string tablename,string picture_id,string download_path)
{
char sqlBuf[1024]="";
memset(sqlBuf,0,sizeof(sqlBuf));
//path为图片的下载路径,此处为"D:/picture"路径加图片ID号码
string path=download_path++"/"+picture_id+".bmp";
const char*tablename1=tablename.c_str();
const char*picture_id1=picture_id.c_str();
const char*path1=path.c_str();
unsigned long *lengths;
MYSQL_RES *result;
MYSQL_ROW row;
FILE *fp;
fp = fopen(path1, "wb");
//cout<<"start select"<<endl;
sprintf(sqlBuf,"select picture from %s where picture_id='%s' ",tablename1,picture_id1);
if(0==mysql_query(&mydata,sqlBuf))
{
result = mysql_store_result(&mydata);
row = mysql_fetch_row(result);
lengths = mysql_fetch_lengths(result);
fwrite(row[0], lengths[0], 1, fp);
mysql_free_result(result);
fclose(fp);
cout<<"download success!"<<endl;
}
else
{
cout<<"download failed!"<<endl;
exit(1);
}
}
//清空表中内容
void clean(string tablename)
{
const char *tablename1;
tablename1=tablename.c_str();
char sqlBuf[100]="";
sprintf(sqlBuf,"TRUNCATE TABLE %s",tablename1);
if(0==mysql_query(&mydata,sqlBuf))
{
cout<<"mysql_query() clean data succeed"<<endl;
}
else
{
cout<<"mysql_query() clean data failed"<<endl;
mysql_close(&mydata);
exit(1);
}
}
{
string tablename="image";
connect();
create(tablename);
string id="1";
string picture_path="D:/image/test.jpg";
string download_path="D:/image";
insert(tablename,id, picture_path);
download( tablename,id,download_path);
download1( tablename,id,download_path);
clean(tablename);
system("pause");
return 0;
}