Linux下通过c操作mysql（向数据库的表中存储图片并进行查询）

    
	MYSQL mysql;
	
    //非0值成功
    if(NULL==mysql_init(&mysql))
    {
        
        printf("mysql_init: %sn",mysql_error(&mysql));
        return -1;
    }
    #define KING_DB_SERVER_IP "192.168.112.111"//主机地址
	#define KING_DB_SERVER_PORT 3306
	#define KING_DB_USERNAME "admin"  //用户名
	#define KING_DB_PASSWORD "123456" //密码
	#define KING_DB_DATABASE "DB_King" //数据库名
	
    //非0值成功
	if(!mysql_real_connect(&mysql,KING_DB_SERVER_IP,KING_DB_USERNAME,KING_DB_PASSWORD,KING_DB_DATABASE,KING_DB_SERVER_PORT,NULL,0))
    {
        printf("mysql_real_connect: %sn",mysql_error(&mysql));
    }

	#define SQL "insert into t_1(id,name) values(1,'as');"
	
	if(mysql_real_query(&mysql,SQL,strlen(SQL)))
    {
        printf("mysql_real_connect: %sn",mysql_error(&mysql));
    }
    
    mysql_close(&mysql);

int King_mysql_select(MYSQL *mysql)
{
	#define SQL_Select "select * from t_1;"
    //mysql_real_query-->SQL 使用sql语句
    if(mysql_real_query(mysql,SQL_Select,strlen(SQL_Select)))
    {
        printf("mysql_real_query: %sn",mysql_error(mysql));
        return -1;
    }
    
    
    //存储查询结果
    MYSQL_RES *res=mysql_store_result(mysql);
    if(res==NULL)
    {
        printf("mysql_store_result: %sn",mysql_error(mysql));
        return -2;
    }
    
    
    //判断结果的行和列
    int rows=mysql_num_rows(res); //数据结果的行数
    printf("rows:%dn",rows);
    int fields=mysql_num_fields(res); //数据结果的列数
    printf("fields:%dn",fields);
    //抓取数据
    
    
    MYSQL_ROW row;//行数组
    while((row=mysql_fetch_row(res)))
    {
        int i=0;
        for(i=0;i
            printf("%st",row[i]);
        }
        printf("n");
    }
    //释放
    mysql_free_result(res);
    return 0;
}

//filename: path+ file name
//buffer: store image data
int read_image(char *filename,char *buffer)
{
    if(filename == NULL || buffer==NULL)
        return -1;
    FILE *fp=fopen(filename,"rb");
    if(fp==NULL)
    {
        printf("fopen failedn");
        return -2;
    }
    //检测整个文件的大小length
    
    
    fseek(fp,0,SEEK_END);//将读写位置移动到文件末尾
    int length=ftell(fp); //文件偏移量file size
    fseek(fp,0,SEEK_SET);


    //读取
    
    int size=fread(buffer,1,length,fp);//读取fp的内容存在buffer
    if(size!=length)
    {
        printf("fread failed: %dn",size);
        return -3;
    }
    fclose(fp);
    return size;
}

int write_image(char *filename,char *buffer,int length)
{
    if(filename==NULL || buffer==NULL || length<=0)
        return -1;
    
    FILE *fp=fopen(filename,"wb+");
    if(fp==NULL)
    {
        printf("fopen failedn");
        return -2;
    }
    
    int size=fwrite(buffer,1,length,fp);//将buffer的内容写到fp中
    if(size!=length)
    {
        printf("fwrite failed: %dn",size);
        return -3;
    }

    fclose(fp);
    return size;
}
//向数据库存储图片信息
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); //数据存储，缓冲
    #define SQL_Insert "insert into t_1(id,name,u_img) values(2,'bs',?);"
    
    int ret=mysql_stmt_prepare(stmt,SQL_Insert,strlen(SQL_Insert));
    if(ret)
    {
        printf("mysql_stmt_prepare: %sn",mysql_stmt_error(stmt));
        return -2;
    }
    //param跟stmt是一对一的
    MYSQL_BIND param={0};
    param.buffer_type=MYSQL_TYPE_LONG_BLOB;//buffer_type指明了与语句参数捆绑的值类型
    param.buffer=NULL;
    param.is_null=0;
    param.length=NULL;
    //绑定参数
    
    ret=mysql_stmt_bind_param(stmt,¶m);
    if(ret)
    {
        printf("mysql_stmt_bind_param: %sn",mysql_error(handle));
        return -3;
    }
    //数据到了mysql服务器的缓冲区
    
    ret=mysql_stmt_send_long_data(stmt,0,buffer,length);
    if(ret)
    {
        printf("mysql_stmt_send_long_data: %sn",mysql_error(handle));
        return -4;
    }
	
    ret=mysql_stmt_execute(stmt);
    if(ret)
    {
        printf("mysql_stmt_execute: %sn",mysql_error(handle));
        return -5;
    }

    mysql_stmt_close(stmt);
    return 0;
}
//读取数据库的图片信息
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,strlen(SQL_Select_Img));
    if(ret)
    {
        printf("mysql_stmt_prepare: %sn",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: %sn",mysql_error(handle));
        return -3;
    }
    mysql_stmt_execute(stmt);
    
    mysql_stmt_store_result(stmt);
    //将存的图片数据显示出来
    while(1)
    {
    	
        ret=mysql_stmt_fetch(stmt);
        if(ret!=0 && ret!=MYSQL_DATA_TRUNCATED) break;
        int start=0;
        while(start<(int)total_length)
        {
            result.buffer=buffer+start;
            result.buffer_length=1;
            
            mysql_stmt_fetch_column(stmt,&result,0,start);
            start+=result.buffer_length;
        }  
    }
    mysql_stmt_close(stmt);
    return total_length;
}
int main()
{
	#define FILE_IMAGE_LENGTH (64*1024)
	//图片写进数据库里面去
    printf("case:mysql-->read_image and write mysqln");
    char buffer[FILE_IMAGE_LENGTH]={0};
    int length=read_image("01.jpg",buffer);
    if(length<0) goto Exit;
    mysql_write(&mysql,buffer,length); //往数据库中写图片数据

    //将图片从数据库里面读出
    printf("case:mysql-->read_image and write mysqln");
    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;
}