C语言实现往MySQL插入和读取图片
-
MySQL版本:5.7.32
-
客户端(node server):192.168.200.152
-
MySQL服务端:192.168.200.129;Port:3306
准备工作
客户端安装C接口库libmysqlclient:
sudo apt install libmysqlclient-dev
用到的表的结构:
CREATE TABLE TBL_USER (
U_ID INT PRIMARY KEY AUTO_INCREMENT,
U_NAME VARCHAR(32),
U_GENDER VARCHAR(8),
U_IMG BLOB
);
- 注:代码中相关函数具体使用方法,可查询官方文档:MySQL C API
实现的功能与实现逻辑
在这里插入图片描述
- 从node server往数据库服务器上的某个表插入带有图片字段的记录
- 从磁盘上读出文件。
- 将读出的文件写入到MySQL服务器上。
- 从node server向数据库服务器查询某条记录中的图片字段
- 从MySQL服务器读取图片的数据到节点服务器上。
- 将读到的图片数据写到node server上的某个文件中。
代码实现
从磁盘上读出文件
这里实现的也就是第一个功能的第一步用到的函数。读取出文件写入到buffer中:首先打开文件,然后读取文件的数据写入到缓冲中,供待会发送到 MySQL服务器用
int read_image(char *filename, char *buffer)
{
if(filename == NULL || buffer == NULL)
{
return -1;
}
//1.读二进制方式打开文件filename
FILE *fp = fopen(filename, "rb");
if(fp == NULL)
{
printf("fopen failed\n");
return -2;
}
//文件指针移动到末尾
fseek(fp, 0, SEEK_END);
//获得文件长度
int length = ftell(fp);
//文件指针移动到开始处
fseek(fp, 0, SEEK_SET);
//2.从fp指向的文件位置每次读1个字节,一共读length字节,写入到buffer中
int size = fread(buffer, 1, length, fp);
if(size != length)
{
printf("fread failed:%d\n", size);
return -3;
}
//有fopen就要有fclose
fclose(fp);
return size;
}
将读到的数据写入磁盘文件
//我们此时从MySQL服务器读到的图片数据在buffer中
int write_image(char *filename, char *buffer, int length)
{
if(filename == NULL || buffer == NULL || length <= 0)
{
return -1;
}
//wb:写二进制方式打开文件
//+:文件不存在就创建,存在就清空原文件内容
FILE *fp = fopen(filename, "wb+");
if(fp == NULL)
{
printf("fopen failed\n");
return -2;
}
//将buffer中的内容,以每次1个字节的方式
//共写length长度,写入到fp所指的文件中
int size = fwrite(buffer, 1, length, fp);
if(size != length)
{
printf("fwrite failed:%d\n", size);
}
fclose(fp);
return size;
}
将buffer的内容写入到MySQL服务器上
statement的函数调用流程可参考https://dev.mysql.com/doc/c-api/5.7/en/c-api-prepared-statement-interface-usage.html
//将buffer中length长度的数据通过handle写到MYSQL服务器上
int mysql_write(MYSQL *handle, char* buffer, int length)
{
if(handle == NULL || buffer == NULL || length <= 0)
return -1;
//statement就是客户端和服务端通信的储物空间
//1.用MYSQL句柄初始化statement
MYSQL_STMT *stmt = mysql_stmt_init(handle);
//2.准备statement
//#define SQL_INSERT_IMG_USER "INSERT TBL_USER(U_NAME, U_GENDER, U_IMG) VALUES('ljf', 'woman', ?);"
//这里传入的查询可带有占位符?,下面的MYSQL_BIND参数就是来替换这个?
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;
}
//3.设置绑定到MYSQL句柄的参数
MYSQL_BIND param = {0};
param.buffer_type = MYSQL_TYPE_LONG_BLOB; // 缓冲类型
param.buffer = NULL; //对于输入,这是指向存储语句参数数据值的缓冲的指针
param.is_null = 0; //表明数据值不为空
param.length = NULL; //缓冲区内实际的字节数
//4.绑定参数
//mysql_stmt_bind_param()用于为SQL语句中的参数标记符(?)绑定数据,以传递给mysql_stmt_prepare()。
ret = mysql_stmt_bind_param(stmt, ¶m); // 成功返回0
if(ret)
{
printf("mysql_stmt_bind_param:%s\n", mysql_error(handle));
return -3;
}
//5.
//允许应用程序分段地(分块)将参数数据发送到服务器
// 它可以被多次调用,以发送一个列的字符或二进制数据值的部分,它必须是TEXT或BLOB数据类型之一。
//第二个参数0默认
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;
}
//6.执行与语句句柄相关的预处理查询。
//在该调用期间,将当前绑定的参数标记符的值发送到服务器,服务器用新提供的数据替换标记符(?)。
ret = mysql_stmt_execute(stmt);
if(ret)
{
printf("mysql_stmt_execute:%s\n", mysql_error(handle));
return -5;
}
//7.关闭statement
ret = mysql_stmt_close(stmt);
if(ret)
{
printf("mysql_stmt_close:%s\n", mysql_error(handle));
return -6;
}
return ret;
}
将MySQL查询语句传回的图片数据读取到buffer中
int mysql_read(MYSQL *handle, char *buffer, int length)
{
if(handle == NULL || buffer == NULL || length <= 0)
return -1;
//1.初始化statement
MYSQL_STMT *stmt = mysql_stmt_init(handle);
//2.为stmt设置要执行的语句
//#define SQL_SELECT_IMG_USER "SELECT U_IMG FROM TBL_USER WHERE U_NAME='ljf';"
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;
}
//3.设置绑定的结构体参数
MYSQL_BIND result = {0}; // 用于接受MYSQL发送来的数据
result.buffer_type = MYSQL_TYPE_LONG_BLOB;
unsigned long total_length = 0;
result.length = &total_length; //后面执行后会把数据写入total_length
//4.绑定结果参数到statement上
ret = mysql_stmt_bind_result(stmt, &result);
if(ret)
{
printf("mysql_stmt_bind_result:%s\n", mysql_error(handle));
return -3;
}
//5.执行语句(prepare阶段2提供的),程序会把数据长度写入到total_length
ret = mysql_stmt_execute(stmt);
if(ret)
{
printf("mysql_stmt_execute:%s\n", mysql_error(handle));
return -4;
}
//6.存储执行获得的结果,存储的位置是前面bind的相关空间
ret = mysql_stmt_store_result(stmt);
if(ret)
{
printf("mysql_stmt_store_result:%s\n", mysql_error(handle));
return -4;
}
//7.读取获得结果的每一行
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
result.buffer = buffer + start;//设置buffer指向哪个位置
result.buffer_length = 1;//可保存在缓冲区内的最大数据
//将结果的第0列的start(偏移量)位置开始,把数据获取到result.buffer中
mysql_stmt_fetch_column(stmt, &result, 0, start);
start += result.buffer_length;
}
}
//8.关闭statement,内部关闭了handle
mysql_stmt_close(stmt);
return total_length;
}
未解问题:这里mysql_read读取获得结果集的每一行退出循环的条件为什么是
if(ret != 0 && ret != MYSQL_DATA_TRUNCATED)
break;
个人理解:
MYSQL_NO_DATA = 100
MYSQL_DATA_TRUNCATED = 101
ret != 0 有三种情况:错误发生, MYSQL_NO_DATA
,MYSQL_DATA_TRUNCATED
因为发送图片数据的时候是分段发的,所以遇到MYSQL_DATA_TRUNCATED
截短错误是正常的,除此之外其他错误就需要退出循环了.
主函数测试代码
//printf("MYSQL_NO_DATA = %d", MYSQL_NO_DATA);
printf("read image and write mysql\n");
char buffer[FILE_IMAGE_LENGTH] = {0};
int length = read_image("/home/jyhlinux/share/05_mysql/0voice.jpg", buffer);
if(length < 0) goto Exit;//节省每次都要写close和return的麻烦,慎用
mysql_write(&mysql, buffer, length);
printf("read mysql and write image\n");
memset(buffer, 0x00, FILE_IMAGE_LENGTH);
//从MySQL服务器读出数据写入到buffer中,返回读到数据的实际长度
length = mysql_read(&mysql, buffer, FILE_IMAGE_LENGTH);
write_image("a.jpg", buffer, length);
源代码
#include <mysql.h>
#include <stdio.h>
#include <string.h>
#define JYH_DB_SERVER_IP "192.168.200.129"
#define JYH_DB_SERVER_PORT 3306
#define JYH_DB_SERVER_USERNAME "admin"
#define JYH_DB_SERVER_PASSWORD "123456"
#define JYH_DB_DEFAULTDB "JYH_DB"
#define SQL_INSERT_TBL_USER "INSERT TBL_USER(U_NAME, U_GENDER) VALUES('czw', 'man');"
#define SQL_SELECT_TBL_USER "SELECT * FROM TBL_USER;"
#define SQL_DELETE_TBL_USER "CALL PROC_DELETE_USER('czw')"
//?是占位符,记住这个用法就行
#define SQL_INSERT_IMG_USER "INSERT TBL_USER(U_NAME, U_GENDER, U_IMG) VALUES('ljf', 'woman', ?);"
#define SQL_SELECT_IMG_USER "SELECT U_IMG FROM TBL_USER WHERE U_NAME='ljf';"
#define FILE_IMAGE_LENGTH (64*1024)
int jyh_mysql_select(MYSQL *mysql)
{
//1.进行mysql查询
//mysql_real_query成功返回0
if(mysql_real_query(mysql, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER)))
{
printf("mysql_real_query: %s\n", mysql_error(mysql));
return -1;
}
//2.存储查询的结果
MYSQL_RES *res = mysql_store_result(mysql);
if(NULL == res)
{
printf("mysql_store_result: %s \n", mysql_error(mysql));
return -2;
}
//3.获得结果的行数(rows)和列数(fields)
//my_ulonglong mysql_num_rows(MYSQL_RES *result)
int num_rows = mysql_num_rows(res);
printf("rows: %d\n", num_rows);
int num_fields = mysql_num_fields(res);
printf("fields: %d\n", num_fields);
//4.取出每行每列显示出来
MYSQL_ROW row;
while((row = mysql_fetch_row(res)))
{
unsigned int i = 0;
for(i = 0; i < num_fields; i++)
{
printf("%s\t", row[i]);
}
printf("\n");
}
mysql_free_result(res);
return 0;
}
//读取磁盘上的某个文件写入到buffer中
int read_image(char *filename, char *buffer)
{
if(filename == NULL || buffer == NULL)
{
return -1;
}
//1.读二进制方式打开文件filename
FILE *fp = fopen(filename, "rb");
if(fp == NULL)
{
printf("fopen failed\n");
return -2;
}
//文件指针移动到末尾
fseek(fp, 0, SEEK_END);
//获得文件长度
int length = ftell(fp);
//文件指针移动到开始处
fseek(fp, 0, SEEK_SET);
//2.从fp指向的文件位置每次读1个字节,一共读length字节,写入到buffer中
int size = fread(buffer, 1, length, fp);
if(size != length)
{
printf("fread failed:%d\n", size);
return -3;
}
//有fopen就要有fclose
fclose(fp);
return size;
}
//将buffer中(存放着从数据库中读到的数据)长度为length的数据写入到文件filename中
int write_image(char *filename, char *buffer, int length)
{
if(filename == NULL || buffer == NULL || length <= 0)
{
return -1;
}
//wb:写二进制方式打开文件
//+:文件不存在就创建,存在就清空原文件内容
FILE *fp = fopen(filename, "wb+");
if(fp == NULL)
{
printf("fopen failed\n");
return -2;
}
//将buffer中的内容,以每次1个字节的方式
//共写length长度,写入到fp所指的文件中
int size = fwrite(buffer, 1, length, fp);
if(size != length)
{
printf("fwrite failed:%d\n", size);
}
fclose(fp);
return size;
}
//将buffer中length长度的数据通过handle写到MYSQL服务器上
int mysql_write(MYSQL *handle, char* buffer, int length)
{
if(handle == NULL || buffer == NULL || length <= 0)
return -1;
//statement就是向MYSQL写入数据的内容
//1.用MYSQL句柄初始化statement
MYSQL_STMT *stmt = mysql_stmt_init(handle);
//2.准备statement
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;
}
//3.设置绑定到MYSQL句柄的参数
MYSQL_BIND param = {0};
param.buffer_type = MYSQL_TYPE_LONG_BLOB; // 缓冲类型
param.buffer = NULL; //对于输入,这是指向存储语句参数数据值的缓冲的指针
param.is_null = 0;
param.length = NULL; //缓冲区内实际的字节数
//4.绑定参数
//mysql_stmt_bind_param()用于为SQL语句中的参数标记符(?)绑定数据,以传递给mysql_stmt_prepare()。
ret = mysql_stmt_bind_param(stmt, ¶m); // 成功返回0
if(ret)
{
printf("mysql_stmt_bind_param:%s\n", mysql_error(handle));
return -3;
}
//5.
//允许应用程序分段地(分块)将参数数据发送到服务器
//第二个参数0默认
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;
}
//6.执行与语句句柄相关的预处理查询。
//在该调用期间,将当前绑定的参数标记符的值发送到服务器,服务器用新提供的数据替换标记符(?)。
ret = mysql_stmt_execute(stmt);
if(ret)
{
printf("mysql_stmt_execute:%s\n", mysql_error(handle));
return -5;
}
//7.关闭statement
ret = mysql_stmt_close(stmt);
if(ret)
{
printf("mysql_stmt_close:%s\n", mysql_error(handle));
return -6;
}
}
//将通过handle从MYSQL服务器读到的内容写入长度为length的buffer中
int mysql_read(MYSQL *handle, char *buffer, int length)
{
if(handle == NULL || buffer == NULL || length <= 0)
return -1;
//1.初始化statement
MYSQL_STMT *stmt = mysql_stmt_init(handle);
//2.为stmt设置要执行的语句
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;
}
//3.设置绑定的结构体参数
MYSQL_BIND result = {0}; // 用于接受MYSQL发送来的数据
result.buffer_type = MYSQL_TYPE_LONG_BLOB;
unsigned long total_length = 0;
result.length = &total_length; //后面执行后会把数据写入total_length
//4.绑定参数到statement上
ret = mysql_stmt_bind_result(stmt, &result);
if(ret)
{
printf("mysql_stmt_bind_result:%s\n", mysql_error(handle));
return -3;
}
//5.执行语句(prepare阶段2提供的),程序会把数据长度写入到total_length
ret = mysql_stmt_execute(stmt);
if(ret)
{
printf("mysql_stmt_execute:%s\n", mysql_error(handle));
return -4;
}
//6.存储执行获得的结果,
ret = mysql_stmt_store_result(stmt);
if(ret)
{
printf("mysql_stmt_store_result:%s\n", mysql_error(handle));
return -4;
}
//7.读取获得结果的每一行
while(1)
{
ret = mysql_stmt_fetch(stmt);
//为什么????
//if(ret != 0 || ret == MYSQL_DATA_TRUNCATED)
if(ret != 0 && ret != MYSQL_DATA_TRUNCATED)
break;
int start = 0;//表示当前所读到的位置
while(start < (int)total_length)
{
//result.buffer直接用用于接受数据的buffer
result.buffer = buffer + start;
result.buffer_length = 1;//可保存在缓冲区内的最大数据长度
//将结果的第0列的start(偏移量)位置开始,把数据获取到result.buffer中
mysql_stmt_fetch_column(stmt, &result, 0, start);
start += result.buffer_length;
}
}
//8.关闭statement,内部关闭了handle
mysql_stmt_close(stmt);
return total_length;
}
int main()
{
MYSQL mysql;
//mysql_init函数解释:
if(NULL == mysql_init(&mysql))
{
printf("mysql_init:%s\n", mysql_error(&mysql));
return -1;
}
//mysql_real_connect失败返回NULL
if(!mysql_real_connect(&mysql, JYH_DB_SERVER_IP, JYH_DB_SERVER_USERNAME,
JYH_DB_SERVER_PASSWORD, JYH_DB_DEFAULTDB, JYH_DB_SERVER_PORT, NULL, 0))
{
printf("mysql_real_connect:%s\n", mysql_error(&mysql));
return -2;
}
#if 1
//mysql-插入操作
//mysql_real_query成功返回0
if(mysql_real_query(&mysql, SQL_INSERT_TBL_USER,strlen(SQL_INSERT_TBL_USER)))
{
printf("mysql_real_query:%s\n", mysql_error(&mysql));
return -3;
}
#endif
printf("mysql-insert:\n");
jyh_mysql_select(&mysql);
#if 1
//mysql-删除操作
//mysql_real_query成功返回0
if(mysql_real_query(&mysql, SQL_DELETE_TBL_USER,strlen(SQL_DELETE_TBL_USER)))
{
printf("mysql_real_query:%s\n", mysql_error(&mysql));
return -3;
}
#endif
printf("mysql-delete:\n");
jyh_mysql_select(&mysql);
//printf("MYSQL_NO_DATA = %d", MYSQL_NO_DATA);
printf("read image and write mysql\n");
char buffer[FILE_IMAGE_LENGTH] = {0};
int length = read_image("/home/jyhlinux/share/05_mysql/0voice.jpg", buffer);
if(length < 0) goto Exit;//节省每次都要写close和return的麻烦,慎用
mysql_write(&mysql, buffer, length);
printf("read mysql and write image\n");
memset(buffer, 0x00, FILE_IMAGE_LENGTH);
//从MySQL服务器读出数据写入到buffer中,返回读到数据的实际长度
length = mysql_read(&mysql, buffer, FILE_IMAGE_LENGTH);
write_image("a.jpg", buffer, length);
Exit:
//勿忘关闭mysql连接(相当于socket的fd)的句柄
mysql_close(&mysql);
return 0;
}
心得
这个代码的函数不需要死记硬背,都是官方文档有的,真正用到的时候懂得找到文档看懂文档 即可。