Linux下实现MySQL存储和提取图片

 

前言:

   最近正好在项目中接触到MySQL数据库,无聊时候突然想把照片存储到数据库中，这一想法让我不断的查找资料和编写代码的实践和测试下,今天最终在Linux下实现这一功能.

 

 //dbproc.c

/*************************************************************           FileName : dbproc.c       FileFunc : MySQL数据库操作实现         Version  : V0.1           Author   : Sunrier           Date     : 2012-06-23     Descp    : Linux下使用C语言访问MySQL函数        *************************************************************/   #include <stdio.h>#include <stdlib.h>#include <string.h>#include <stdarg.h>  #include <mysql.h>#define DEBUG        __FILE__,__LINE__static MYSQL s_my_connection;static int s_iDbconnected = 0; /*数据库连接标志 连接时为1,断开时为0*//*程序调试函数*/void debug(char *pFileName,int iLine,const char *fmt, ...){char szTemp[256];va_list vap;/*memset(szTemp,0,sizeof(szTemp));*/va_start(vap,fmt);fprintf(stderr,"[%s][%d]:",pFileName,iLine);memset(szTemp,0,sizeof(szTemp));vsprintf(szTemp,fmt,vap);va_end(vap);fprintf(stderr,"%s\n",szTemp);fflush(stderr);}/*测试MySQL客户端版本*/void mysql_version( void )  {      printf("MySQL client version : %s ! \n",mysql_get_client_info());  }  /*登陆MySQL*/int mysql_login(char* pServer,char *pUser,char *pPassword,char *pDataBase){MYSQL *conn_ptr = NULL;int iRetCode = -1;unsigned int uiTimeOut = 7;if( s_iDbconnected )return 0;conn_ptr = mysql_init(&s_my_connection);if( !conn_ptr ){fprintf(stderr,"mysql_init failed ! \n");return EXIT_FAILURE;}iRetCode = mysql_options(&s_my_connection,MYSQL_OPT_CONNECT_TIMEOUT,(const char *)&uiTimeOut);if( iRetCode  ){fprintf(stderr,"MySQL Connection is timeout! \n");return EXIT_FAILURE;}conn_ptr = NULL;conn_ptr = mysql_real_connect(&s_my_connection,pServer,pUser,pPassword,pDataBase,0,NULL,0);if( conn_ptr ){printf("MySQL Connection success!\n");s_iDbconnected = 1;}else{fprintf(stderr,"MySQL Connection failed!\n");if( mysql_errno(&s_my_connection) ){fprintf(stderr,"Connection error %d: %s!\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));return EXIT_FAILURE;}}return EXIT_SUCCESS;}/*退出MySQL*/void mysql_logout( void ){if( s_iDbconnected )mysql_close(&s_my_connection); /*关闭连接*/  s_iDbconnected = 0 ;}int mysql_proc( void ){return 0;}/*向MySQL数据库中存储图片*/int mysql_store_image(char *pFileName,char *pImageTableName,unsigned char ucIdFlag,int iId){FILE *fp;char szImageName[31];char szImageData[1024*1000];/*图片大小最大不超过1M*/char szStoreImageData[2*1024*1000+1];char szSql[2*1024*1000+1];unsigned long ulReadLength = 0,ulStoreLength = 0;int iRetCode = -1;fp = fopen(pFileName,"rb");if( NULL==fp ){fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);return EXIT_FAILURE;}memset(szImageData,0,sizeof(szImageData));ulReadLength = fread(szImageData,1,1024*1000,fp);fclose(fp);if( !ulReadLength ){fprintf(stderr,"Read file found error !\n");return EXIT_FAILURE;}printf("ulReadLength = %ld \n",ulReadLength);memset(szImageName,0,sizeof(szImageName));memcpy(szImageName,pFileName,strlen(pFileName));memset(szStoreImageData,0,sizeof(szStoreImageData));ulStoreLength = mysql_real_escape_string(&s_my_connection,szStoreImageData,szImageData,ulReadLength);/*二进制数据可能包含一些特殊字符,这些字符在sql语句中可能会引起一些问题,所以必须转义,理论上每个字符都可能是特殊字符,所以szStoreImageData数组大小是szImageData数组大小的两倍,该函数还会在szStoreImageData数组最后加上结尾符*/printf("ulStoreLength = %ld \n",ulStoreLength);memset(szSql,0,sizeof(szSql));if( 1==ucIdFlag ){sprintf(szSql,"insert into %s(id,name,data) values(%d,'%s','%s')",pImageTableName,iId,szImageName,szStoreImageData);}else{sprintf(szSql,"insert into %s(name,data) values('%s','%s')",pImageTableName,szImageName,szStoreImageData);}iRetCode = mysql_query(&s_my_connection,szSql);if( iRetCode ){fprintf(stderr,"insert error ,sqlcode=[%d] : %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));return EXIT_FAILURE;}return EXIT_SUCCESS;}/*向MySQL数据库中存储图片*/int mysql_store_image_ex(char *pFileName,char *pImageTableName,unsigned char ucIdFlag,int iId){FILE *fp;char szImageName[31];char szImageData[1024*1000];/*图片大小最大不超过1M*/char szStoreImageData[2*1024*1000+1];char szSql[2*1024*1000+1];unsigned long ulReadLength = 0,ulStoreLength = 0,ulStoredLength = 0;int iRetCode = -1;fp = fopen(pFileName,"rb");if( NULL==fp ){fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);return EXIT_FAILURE;}memset(szImageData,0,sizeof(szImageData));ulReadLength = fread(szImageData,1,1024*1000,fp);fclose(fp);if( !ulReadLength ){fprintf(stderr,"Read file found error !\n");return EXIT_FAILURE;}printf("ulReadLength = %ld \n",ulReadLength);memset(szImageName,0,sizeof(szImageName));memcpy(szImageName,pFileName,strlen(pFileName));memset(szStoreImageData,0,sizeof(szStoreImageData));ulStoreLength = mysql_real_escape_string(&s_my_connection,szStoreImageData,szImageData,ulReadLength);/*二进制数据可能包含一些特殊字符,这些字符在sql语句中可能会引起一些问题,所以必须转义,理论上每个字符都可能是特殊字符,所以szStoreImageData数组大小是szImageData数组大小的两倍,该函数还会在szStoreImageData数组最后加上结尾符*/printf("ulStoreLength = %ld \n",ulStoreLength);memset(szSql,0,sizeof(szSql));if( 1==ucIdFlag ){sprintf(szSql,"insert into %s(id,name,data) values(%d,'%s','%s')",pImageTableName,iId,szImageName,szStoreImageData);}else{sprintf(szSql,"insert into %s(name,data) values('%s','%s')",pImageTableName,szImageName,szStoreImageData);}ulStoredLength = strlen(szSql);iRetCode =  mysql_real_query(&s_my_connection,szSql,ulStoredLength);printf("ulStoredLength = %ld \n",ulStoredLength);if( iRetCode ){fprintf(stderr,"insert error ,sqlcode=[%d] : %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));return EXIT_FAILURE;}return EXIT_SUCCESS;}/*从MySQL数据库提取存储的图片*/int mysql_fetch_image(char *pFileName,char *pImageTableName,unsigned char ucFlag,int iId){FILE *fp; MYSQL_RES *res_ptr = NULL;MYSQL_ROW sqlrow;int iRetCode = -1,iTableRow = 0;char szSql[256];unsigned long *ulLength = NULL;unsigned long ulWriteLength = 0;/*ucFlag = 1 表示指定文件名*/memset(szSql,0,sizeof(szSql));if( 1==ucFlag ){sprintf(szSql,"select data from %s where id = %d",pImageTableName,iId);}else{fprintf(stderr,"This flag [ %d ] found error for the filename ,and you must specify a filename !\n",ucFlag);return EXIT_FAILURE;}fp = fopen(pFileName,"wb");if( NULL==fp ){fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);return EXIT_FAILURE;}iRetCode = mysql_query(&s_my_connection,szSql);/*执行SQL语句*/  if( iRetCode )    {  fprintf(stderr,"Select error %d: %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));/*打印错误处理具体信息*/   return EXIT_FAILURE;  }  res_ptr = mysql_store_result(&s_my_connection);/*集合*/  if( res_ptr )    {    iTableRow = mysql_num_rows(res_ptr);/*行*/    if( iTableRow==0 )/*表示查询没有结果*/  {  fclose(fp);fprintf(stderr,"Select data from %s where id = [ %d ] , result is null  !\n",pImageTableName,iId); return EXIT_FAILURE;     }    sqlrow = mysql_fetch_row(res_ptr);   ulLength = mysql_fetch_lengths(res_ptr);   printf("ulFetchedLength: %ld\n",ulLength[0]);/*从数据库中提取的数据信息长度*/mysql_free_result(res_ptr);/*完成对数据的所有操作后,调用此函数来让MySQL库清理它分配的对象*/ulWriteLength = fwrite(sqlrow[0],1,ulLength[0],fp);if( ulWriteLength==0 ){fclose(fp);fprintf(stderr,"Write file found error !\n");return EXIT_FAILURE;}printf("ulWriteLength = %ld \n",ulWriteLength);/*写入文件的数据信息长度*/}else    {   fclose(fp);fprintf(stderr,"Select result is null  !\n"); return EXIT_FAILURE;     }     fclose(fp);return EXIT_SUCCESS;}/*从MySQL数据库提取存储的图片*/int mysql_fetch_image_ex(char *pFileName,char *pImageTableName,unsigned char ucFlag,int iId){FILE *fp; MYSQL_RES *res_ptr = NULL;MYSQL_ROW sqlrow;int iRetCode = -1,iTableRow = 0;char szSql[256];unsigned long *ulLength = NULL;unsigned long ulWriteLength = 0;memset(szSql,0,sizeof(szSql));/*ucFlag = 2 表示取数据库表中的信息文件名*/if( 2==ucFlag ){sprintf(szSql,"select data,name from %s where id = %d",pImageTableName,iId);}else{fprintf(stderr,"This flag [ %d ] found error for the filename ,and you needn't specify a filename !\n",ucFlag);return EXIT_FAILURE;}iRetCode = mysql_query(&s_my_connection,szSql);/*执行SQL语句*/  if( iRetCode )    {  fprintf(stderr,"Select error %d: %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));/*打印错误处理具体信息*/   return EXIT_FAILURE;  }  res_ptr = mysql_store_result(&s_my_connection);/*集合*/  if( res_ptr )    {    iTableRow = mysql_num_rows(res_ptr);/*行*/    if( iTableRow==0 )/*表示查询没有结果*/  {  fclose(fp);fprintf(stderr,"Select data,name from %s where id = [ %d ] , result is null  !\n",pImageTableName,iId); return EXIT_FAILURE;     }    sqlrow = mysql_fetch_row(res_ptr);   ulLength = mysql_fetch_lengths(res_ptr);   sscanf(sqlrow[1],"%s",pFileName);/*从数据库中取文件名*/  printf("ulFetchedLength: %ld\n",ulLength[0]);mysql_free_result(res_ptr);/*完成对数据的所有操作后,调用此函数来让MySQL库清理它分配的对象*/fp = fopen(pFileName,"wb");if( NULL==fp ){fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);return EXIT_FAILURE;}ulWriteLength = fwrite(sqlrow[0],1,ulLength[0],fp);if( ulWriteLength==0 ){fclose(fp);fprintf(stderr,"Write file found error !\n");return EXIT_FAILURE;}printf("ulWriteLength = %ld \n",ulWriteLength);}else    {   fclose(fp);fprintf(stderr,"Select result is null  !\n"); return EXIT_FAILURE;     }      fclose(fp);return EXIT_SUCCESS;}

 

 

//store.c

/***********************************************************************           FileName : store.c       FileFunc : 把图片存储到MySQL数据库中操作测试(图片大小最大不超过1M)         Version  : V0.1           Author   : Sunrier           Date     : 2012-06-23     Descp    : Linux下使用C语言访问MySQL函数        ************************************************************************/#include <stdio.h>int main(int argc,char *argv[]){ char szServer[20] = "localhost"; /*127.0.0.1*/ char szUser[20] = "Sunrier";  char szPassword[20] = "redhat";  char szDatabase[20] = "test"; char szFileName[30],szTableName[30];unsigned char ucIdFlag = 0;/*手动插入ID标志*/int iRetCode = -1,iId =0;        iRetCode = mysql_login(szServer,szUser,szPassword,szDatabase);  if( iRetCode ){return 1;}memset(szFileName,0,sizeof(szFileName));memset(szTableName,0,sizeof(szTableName));strcpy(szFileName,"Sunrier.jpg");strcpy(szTableName,"images");ucIdFlag = 0;iId =1;/*iRetCode = mysql_store_image( szFileName,szTableName,ucIdFlag,iId );*/iRetCode = mysql_store_image_ex( szFileName,szTableName,ucIdFlag,iId );if( !iRetCode ){fprintf(stderr,"Image has been stored in the database !\n");}mysql_logout();return 0;}  

 

 

//fetch.c

/**********************************************************************           FileName : fetch.c       FileFunc : 从MySQL数据库中提取图片操作测试(图片大小最大不超过1M)           Version  : V0.1           Author   : Sunrier           Date     : 2012-06-23     Descp    : Linux下使用C语言访问MySQL函数        ***********************************************************************/#include <stdio.h>int main(int argc,char *argv[]){int iRetCode = -1;  char szServer[20] = "localhost";  char szUser[20] = "Sunrier";  char szPassword[20] = "redhat";  char szDatabase[20] = "test";  char szFileName[30],szTableName[30]; unsigned char ucFlag = 0;  int iId = 0;        iRetCode = mysql_login(szServer,szUser,szPassword,szDatabase);  if( iRetCode ){return 1;}ucFlag = 2;iId = 9;memset(szFileName,0,sizeof(szFileName));memset(szTableName,0,sizeof(szTableName));strcpy(szFileName,"Sunrier.jpg");strcpy(szTableName,"images");/*iRetCode = mysql_fetch_image(szFileName,szTableName,ucFlag,iId);*/iRetCode = mysql_fetch_image_ex(szFileName,szTableName,ucFlag,iId);if( !iRetCode ){fprintf(stderr,"Image has been fetched from the database !\n");}mysql_logout();return 0;}  

 

 

//makefile

#makefileOBJS = store fetch   all:$(OBJS)  CFLAGS = -O -w -ansi     #CFLAGS = -O -Wall -ansi    CC = gcc $(CFLAGS)MYSQLINCPATH = -I/usr/include/mysql  MYSQLLIBPATH = -L/usr/lib/mysql  MYSQLLIB = -lmysqlclient -lz -lmstore:store.c dbproc.c @$(CC) $(MYSQLINCPATH) $(MYSQLLIBPATH) -o $@ $? $(MYSQLLIB)fetch:fetch.c dbproc.c @$(CC) $(MYSQLINCPATH) $(MYSQLLIBPATH) -o $@ $? $(MYSQLLIB)clean:@ls | grep -v ^makefile$$ | grep -v [.]c$$ | grep -v [.]h$$ | grep -v [.]sql$$ | grep -v [.]jpg$$ | grep -v [.]txt$$ | xargs rm -rf#makefile

 

 

//images.sql

drop database if exists test;  create database test;  use test;  create table images  (      id integer auto_increment not null primary key,     name varchar(30) not null,     data mediumblob not null); -- )engine=innodb charset=gb2312; -- 指定引擎和编码方式-- 图片是二进制数据,mysql有种特殊的数据类型,用来存储二进制数据,叫做BLOB(Binary Large Ojbect)