8、Linux C/C++ 实现MySQL的图片插入以及图片的读取

int fseek(FILE *stream, long offset, int whence);

size_t fread(void *ptr, size_t size, size_t count, FILE *stream);

size_t fwrite(void *ptr, size_t size, size_t count, FILE *stream);

#include<stdio.h>
#include<string.h>
#include<mysql.h>
#define MING_DB_IP           "192.168.42.128"
#define MING_DB_PORT         3306
#define MING_DB_USENAME      "admin"
#define MING_DB_PASSWORD     "123456"
#define MING_DB_DEFAULTDB    "MING_DB"
#define FILE_IMAGE_LENGTH    (64*1024)
#define SQL_INSERT_IMG_USER     "insert TBL_USER(U_NAME,U_GENDER,U_IMG) values('zxm','women',?);"
#define SQL_SELECT_IMG_USER     "select U_IMG from TBL_USER where U_NAME='zxm';"
//从客户端读取图片存入节点服务器的buffer
    //filename:图片路径； buffer：存储图片
int read_image(char *filename,char *buffer){
    if (filename == NULL || buffer ==NULL ) return -1;
    FILE *fp=fopen(filename,"rb"); //以二进制模式读取该文件
    if (fp == NULL){
        printf("fopen failed\n");
        return -2;
    }
    //检测文件大小file
    fseek(fp,0,SEEK_END);
    int length=ftell(fp);
    fseek(fp,0,SEEK_SET);
    int size=fread(buffer,1,length,fp);
    if (size != length){
        printf("fread failed:%d\n",size);
        return -3;
    }
    fclose(fp);
    return size;
}
//从节点服务器的buffer写入图片到客户端
int write_image(char *filename,char *buffer,int length){
    if (filename == NULL || buffer ==NULL || length <=1) return -1;
    FILE *fp=fopen(filename,"wb+"); 
    if (fp == NULL){
        printf("fopen failed\n");
        return -2;
    }    
    int size=fwrite(buffer,1,length,fp);
    if (size != length){
        printf("fwrite failed:%d\n",size);
        return -3;
    }
    fclose(fp);
    return size;
}
//从节点服务器的buffer读取数据到数据库
int mysql_write(MYSQL *handle,char *buffer,int length){
    if (handle ==NULL || buffer == NULL || length <=0) return -1;
    MYSQL_STMT *stmt=mysql_stmt_init(handle);
    int ret=mysql_stmt_prepare(stmt,SQL_INSERT_IMG_USER,strlen(SQL_INSERT_IMG_USER));
    if (ret){
        printf("mysql_stmt_prepare:%s\n",mysql_error(handle));
        return -2;
    }
    MYSQL_BIND param={0};
    param.buffer_type=MYSQL_TYPE_LONG_BLOB;
    param.buffer=NULL;
    param.is_null=0;
    param.length=NULL;
    ret=mysql_stmt_bind_param(stmt,&param);
    if (ret){
        printf("mysql_stmt_bind_param:%s\n",mysql_error(handle));
        return -3;       
    }
    ret=mysql_stmt_send_long_data(stmt,0,buffer,length);
    if (ret){
        printf("mysql_stmt_send_long_data:%s\n",mysql_error(handle));
        return -4;       
    }   
    ret=mysql_stmt_execute(stmt);
    if (ret){
        printf("mysql_stmt_execute:%s\n",mysql_error(handle));
        return -5;       
    }
    ret=mysql_stmt_close(stmt) ;  
    if (ret){
        printf("mysql_stmt_close:%s\n",mysql_error(handle));
        return -6;       
    }     
    return ret;
}
//从数据库写入数据到节点服务器的buffer
int mysql_read(MYSQL *handle,char *buffer,int length){
    if (handle ==NULL || buffer == NULL || length <=0) return -1;
    MYSQL_STMT *stmt=mysql_stmt_init(handle);
    int ret=mysql_stmt_prepare(stmt,SQL_SELECT_IMG_USER,strlen(SQL_SELECT_IMG_USER));
    if (ret){
        printf("mysql_stat_prepare:%s\n",mysql_error(handle));
        return -2;
    }   
    MYSQL_BIND result={0};
    result.buffer_type=MYSQL_TYPE_LONG_BLOB;
    unsigned long total_length=0;
    result.length=&total_length;
    ret=mysql_stmt_bind_result(stmt,&result);
    if (ret){
        printf("mysql_stmt_bind_result:%s\n",mysql_error(handle));
        return -3;
    }     
    ret=mysql_stmt_execute(stmt);
    if (ret){
        printf("mysql_stmt_execute:%s\n",mysql_error(handle));
        return -4;
    }         
    ret=mysql_stmt_store_result(stmt);
    if (ret){
        printf("mysql_stmt_store_result:%s\n",mysql_error(handle));
        return -5;
    }    
    while (1)
    {
        //获取下一行数据并存储到 result.buffer 中
        ret=mysql_stmt_fetch(stmt);
        if (ret !=0 && ret != MYSQL_DATA_TRUNCATED ) break;
        int start=0;
        while(start <(int)total_length ){
            /*buffer 是一个指向缓冲区起始地址的指针，而 start 是一个表示偏移量的整数。
            因此，buffer+start 的结果就是一个指向缓冲区中某个特定位置的指针。
            result.buffer指向的是buffer+start*/
            result.buffer=buffer+start; 
            //存储实际读取到的数据长度，单位为字节 
            result.buffer_length=1;
            /*在使用 mysql_stmt_fetch_column() 函数从结果集中读取数据时，每次只会读取一部分数据并存储到由
             result.buffer所指向的缓冲区中。如果要将多个数据段合并成完整的数据块，
            则需要利用偏移量来调整存储位置。具体地说，可以通过不断修改 start 的值来控制写入数据时所处的位置。*/
            mysql_stmt_fetch_column(stmt,&result,0,start);
            start+=result.buffer_length;
        }
    }
    mysql_stmt_close(stmt);
    return total_length;
}
int main(){
    //定义一个MYSQL类型的结构体变量，并通过调用mysql_init()函数对其进行初始化
    //调用mysql_error()函数来获取有关错误信息
     MYSQL mysql;
    if (mysql_init(&mysql)==NULL){
        printf("mysql_init:%s\n",mysql_error(&mysql));
        return -1;
    }
    //mysql_real_connect()用于连接到MySQL服务器.它需要以下参数：
    /*1、MYSQL类型的结构体指针，该结构体已由mysql_init()初始化。
      2、MySQL服务器所在主机名或IP地址。
      3、登录MySQL服务器时使用的用户名。
      4、登录MySQL服务器时使用的密码。
      5、要连接的数据库名称。
      6、端口号（默认为3306）。
      7、规定 socket    8、规定不同的连接选项
    */
    if(!mysql_real_connect(&mysql,MING_DB_IP,MING_DB_USENAME,MING_DB_PASSWORD,
    MING_DB_DEFAULTDB,MING_DB_PORT,NULL,0)){    //等于0，不成功
        printf("mysql_real_connect:%s\n",mysql_error(&mysql));
        return -2;
    }
    printf("case:mysql read image and write image \n");
    char buffer[FILE_IMAGE_LENGTH]={0};
    int length=read_image("/home/zxm/share/06mysqlPicture/0voice.jpg",buffer);
    if (length < 0) goto Exit;
    mysql_write(&mysql,buffer,length);
    printf("case:mysql read mysql and write image \n");
    memset(buffer,0,FILE_IMAGE_LENGTH);
    length=mysql_read(&mysql,buffer,FILE_IMAGE_LENGTH);
    write_image("a.jpg",buffer,length);
Exit:
    mysql_close(&mysql);
    return 0;
}