利用mysql C API的基本操作（包括对图片的上传下载操作）

   最近才开始使用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);
}


}

int main()
{

    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;

}