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

最新推荐文章于 2022-04-26 18:09:06 发布

sunrier

最新推荐文章于 2022-04-26 18:09:06 发布

阅读量3.1k

点赞数 1

分类专栏： Linux C MySQL 文章标签： mysql 存储 linux fp 数据库 image

本文链接：https://blog.csdn.net/sunrier/article/details/7686464

版权

C 同时被 3 个专栏收录

57 篇文章 0 订阅

订阅专栏

Linux

48 篇文章 0 订阅

订阅专栏

MySQL

6 篇文章 0 订阅

订阅专栏

前言:

最近正好在项目中接触到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

#makefile
OBJS = 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 -lm

store: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)

sunrier

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
3
评论
Linux下实现MySQL存储和提取图片

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

扫一扫