数据库mysql项目实战（二）

最新推荐文章于 2024-06-20 00:32:26 发布

想当开心果哦

最新推荐文章于 2024-06-20 00:32:26 发布

阅读量654

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_53492721/article/details/120963205

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

需要实现的操作：

mysql数据库图片存储 read_image or write_image

mysql数据库图片存储 mysql_write

mysql数据库图片存储 mysql_read

完成的代码演示及结果

思考

需要实现的操作：

准备好一张图片，并且将图片read。
xxx,xxx， mysql_write_image
mysql_read_image
写入磁盘

mysql数据库图片存储 read_image or write_image

先进行简单的1、4.

mysql数据库图片存储 mysql_write

在MySQL workbench 中输入如下命令，在TBL_USER再加入一列U_IMG：

ALTER TABLE TBL_USER ADD U_IMG BLOB

重点理解MYSQL *handle 与STATEMENT之间的关系！！

类比STATEMENT相当于卫星发射中心与空间站的储物间，MYSQL *handle相当于发射台到空间站中间这部分。

相关代码：


#define SQL_INSERT_IMG_USER	     "INSERT TBL_USER(U_NAME, U_GENGDER, U_IMG) VALUES('GQ', 'man', ?);" //?为占位符


// 2
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);  //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_execue: %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;
	
}

代码的分析与解释：

STATEMENT这个储物空间由下列命令已经创建好了

	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;
	}

现在我们要绑定这个问号里面存储的是什么数据，什么类型的数据？我们需要对外宣称！！下面代码实现这以问题。把要传输的数据放在卫星发射中心的仓库，准备发射到空间站的仓库，绑定一个参数param。

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);  //stmt 与 param 绑定
if(ret){
	printf("mysql_stmt_bind_param: %s\n",mysql_error(handle));
	return -3;
}

注：以下所有函数语句的解释均参考MySQL5.1参考手册，版本不同会有出入

MYSQL_BIND

该结构用于语句输入（发送给服务器的数据值）和输出（从服务器返回的结果值）。对于输入，它与mysql_stmt_bind_param()一起使用，用于将参数数据值绑定到缓冲区上，以供mysql_stmt_execute()使用。对于输出，它与mysql_stmt_bind_result()一起使用，用于绑定结果缓冲区，以便用于with mysql_stmt_fetch()以获取行。

现在我们将数据发送到空间站：

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;
}

mysql_stmt_send_long_data()

my_bool mysql_stmt_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, const char *data, unsigned long length)

允许应用程序分段地（分块）将参数数据发送到服务器。可以多次调用该函数，以便发送关于某一列的字符或二进制数据的不同部分，列必须是TEXT或BLOB数据类型之一

现在数据已经到空间站仓库，还没有到我们的数据库服务器数据库表里面。现在发送到数据库服务器当中。

ret = mysql_stmt_execute(stmt);
if(ret){
	printf("mysql_stmt_execue: %s\n",mysql_error(handle));
	return -5;
}

mysql_stmt_execute()

mysql_stmt_execute()执行与语句句柄相关的预处理查询。在该调用期间，将当前绑定的参数标记符的值发送到服务器，服务器用新提供的数据替换标记符。

最后记得关闭！

mysql数据库图片存储 mysql_read

相关代码：

#define SQL_SELECT_IMG_USER		 "SELECT U_IMG FROM TBL_USER WHERE U_NAME='GQ';"

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_stmt_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_prepare: %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){
		
		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;
}

代码分析与解释：

与发送到空间站相同，从空间站返回的数据也需要先存储在发射中心的仓库。所以与前者类似，代码的实现相同。

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_stmt_prepare: %s\n",mysql_error(handle));
	return -2;
}

返回的结果也需要绑定（result），返回的时候返回的是一个长度的值。

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_prepare: %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;
}

现在将管道中的数据拿出来进行存储，与项目实战一中的store过程类似，相当于升级版。

ret = mysql_stmt_store_result(stmt);
if(ret){
	printf("mysql_stmt_store_result: %s\n",mysql_error(handle));
	return -5;
}

下面利用whiel(1)循环将数据读出来进行显示，mysql_stmt_fetch与项目实战一中的fetech过程类似，相当于升级版。

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;
	}	
}

重点理解：

result.buffer = buffer + start; //?

第一个buffer是结果集的，第二个buffer是参数传过来的。这两个buffer是公用的同一个空间，不需要另外分配一个空间进行copy。一个形象的比喻，空间站发过来的数据，我们用卡车一个一个接，这个卡车就是buffer，卫星发射中心的仓库空间就是buffer。

result.buffer_length = 1; // 1 个字节

这个意思就像说的是卡车的长度，一个卡车能接多长的数据。

完成的代码演示及结果

代码运行前:

#include<stdio.h>

#include<mysql.h>

#include<string.h>


#define GQ_DB_SERVER_IP              "192.168.80.128"
#define GQ_DB_SERVER_PORT            3306

#define GQ_DB_SERVER_USERNAME     	 "admin"
#define GQ_DB_SERVER_PASSWORD        "521125"

#define GQ_DB_SERVER_DEFAULTBD  	 "GQ_DB" 

#define SQL_INSERT_TBL_USER          "INSERT TBL_USER(U_NAME, U_GENGDER) VALUES('GQ', 'man'); "
#define SQL_SELECT_TBL_USER			 "SELECT *FROM TBL_USER;"

#define SQL_DELETE_TBL_USER			 "CALL PROC_DELETE_USER('GQ')"
#define SQL_INSERT_IMG_USER			 "INSERT TBL_USER(U_NAME, U_GENGDER, U_IMG) VALUES('GQ', 'man', ?);" //?占位符

#define SQL_SELECT_IMG_USER			 "SELECT U_IMG FROM TBL_USER WHERE U_NAME='GQ';"
#define FILE_IMAGE_LENGTH			 (64*1024)



int Gq_mysql_select(MYSQL *handle){

	//mysql_real_query --> sql
	if(mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(handle));
		return -1;
	}
	

	//sotre -->
	MYSQL_RES *res = mysql_store_result(handle);
	if(res == NULL){
		printf("mysql_store_result: %s\n", mysql_error(handle));
		return -2;
	}

	
	//rows / fields
	int rows = mysql_num_rows(res);
	printf("rows: %d\n", rows);
	
	int fields = mysql_num_fields(res);
	printf("fields: %d\n", fields);
	

	//fetch
	MYSQL_ROW row;
	while((row = mysql_fetch_row(res))){

		int i = 0;
		for(i = 0; i < fields; i++){
			printf("%s\t", row[i]);
		}
		printf("\n");
		
	}


	mysql_free_result(res);

	return 0;

}


//filename: path + filename
//buffer: store image data


// 1
int read_image(char *filename, char *buffer){

	if(filename == NULL || buffer == NULL)  return -1;
	
	FILE *fp = fopen(filename, "rb");   //标准c
	if(fp == NULL){
		printf("fopen failed\n");
		return -2;	
	}

	//file size
	fseek(fp, 0, SEEK_END);
	int length = ftell(fp);     //tell us file size
	fseek(fp, 0, SEEK_SET);     //fp  -->begin

	//从指针fp位置开始，每次读取一个字节，读length次４嫒隻uffer
	int size = fread(buffer, 1, length, fp);
	if(size != length){
		printf("fread failed: %d\n", size);
		return -3;
	}
	
	fclose(fp);

	return size;
	
}

// 4 
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 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;

}

// 2
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);  //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_execue: %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;
	
}


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_stmt_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_prepare: %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){
		
		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;
}



//C U R D  

int main(){
	
	MYSQL mysql;   //操作句柄  mysql | (管道)

	if(NULL == mysql_init(&mysql)){
		printf("mysql_init: %s\n", mysql_error(&mysql));
		return -1;
	}

	if(!mysql_real_connect(&mysql, GQ_DB_SERVER_IP, GQ_DB_SERVER_USERNAME,
		GQ_DB_SERVER_PASSWORD, GQ_DB_SERVER_DEFAULTBD, GQ_DB_SERVER_PORT, NULL, 0)){

		printf("mysql_real_connect: %s\n", mysql_error(&mysql));
		goto Exit;
	}

	//mysql --> insert
	printf("case: mysql --> insert\n");
#if 1
	if(mysql_real_query(&mysql, SQL_INSERT_TBL_USER, strlen(SQL_INSERT_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
		goto Exit;
	}

#endif

	Gq_mysql_select(&mysql);

// mysql -->delete
	printf("case: mysql -->delete\n");
#if 1
	if(mysql_real_query(&mysql, SQL_DELETE_TBL_USER, strlen(SQL_DELETE_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
		goto Exit;
	}
		
#endif
	
	Gq_mysql_select(&mysql);

	printf("case: mysql -->read image and write mysql\n");
	
	char buffer[FILE_IMAGE_LENGTH] = {0};
	int length = read_image("/home/guoqiang/share/Mysql/0voice.jpg",buffer); //图片path
	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);       // 置0，防止以前有脏数据，这样buffer就没数据
	length = mysql_read(&mysql, buffer, FILE_IMAGE_LENGTH);

	write_image("b.jpg", buffer, length);      // 写的图片名称为存b.jpg

Exit:
	mysql_close(&mysql);

	return 0;
	
}

运行完成过后：

检查插入的图片与返回的图片是否相同，即0voice.jpg 与b.jpg是否相同，相同则表示成功。

思考

怎么实现数据库MySQL的连接池？不需要每次都handle。连接池要支持SQL语句读写存储过程，包括文件的读取，文件的插入。

想当开心果哦

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
数据库mysql项目实战（二）

准备好一张图片，并且将图片read。 xxx,xxx， mysql_write_image mysql_read_image 写入磁盘mysql数据库图片存储 read_image or write_image先进行简单的1、4.相关代码：// 1int read_image(char *filename, char *buffer){ if(filename == NULL || buffer == NULL) return -1; FILE *fp = fop.
复制链接

扫一扫