C++ MySQL存储二进制数据

来源：互联网发布：手机有些软件打不开编辑：程序博客网时间：2024/05/21 17:11

0x01

首先MySQL支持二进制的类型有Blob:

MySQL的四种BLOB类型

类型大小(单位：字节)
TinyBlob 最大 255
Blob 最大 65K
MediumBlob 最大 16M

LongBlob 最大 4G

0x02

插入二进制数据时需要利用mysql_real_escape_string函数对数据进行转换

0x03

读取二进制数据时需要利用mysql_fetch_length函数字段长度,该函数需要在mysql_fetch_row调用后才可以正常获取结果

0x04

写入例子：

int  CFinger_PrintDlg::Connect_Mysql(byte b_name[], byte* b_data,int len_of_data) {const char user[] = "root";const char pswd[] = "toor";const char host[] = "127.0.0.1";const char db[] = "Fingerprint";int res;int listrow = 0;char sql_buf[100] = {};unsigned int port = 3306;bool return_result = false;MYSQL myCont;MYSQL_RES *result;MYSQL_ROW sql_row;HWND hWnd = GetSafeHwnd();TCHAR sql_buf_t[100] = {};//CListCtrl* list = (CListCtrl*)GetDlgItem(IDC_LIST3);mysql_init(&myCont);if (mysql_real_connect(&myCont, host, user, pswd, db, port, NULL, 0)){PostMessage(WM_MyMessage, (WPARAM)&L"连接数据库成功！！", (LPARAM)NULL);}mysql_query(&myCont, "SET NAMES GBK"); //设置编码格式char buf[2000] = { NULL };unsigned int escape_size = 2 * len_of_data + 2;char* escape_object = (char *)malloc(escape_size);escape_size = mysql_real_escape_string(&myCont, (char*)escape_object, (char *)b_data, len_of_data);int sql_len = sprintf_s(buf, "insert into finger3(info) values('%s')", escape_object);res = mysql_real_query(&myCont,buf, sql_len);if (!res){return_result = TRUE;if (!res){PostMessage(WM_MyMessage, (WPARAM)L"写入成功", NULL);}}else{PostMessage(WM_MyMessage, (WPARAM)L"写入失败", NULL);}mysql_close(&myCont);return return_result;}

0x05

读取操作：

void* CFinger_PrintDlg::Read_Mysql(){if (!g_connect){SendMessage(WM_MyMessage, (WPARAM)L"数据库未连接...", NULL);return 0;}MYSQL_RES *res = NULL;MYSQL_ROW row;unsigned long *row_len;char *object = NULL;//const char *sql = "select info from final where id=";char sql[100] = { NULL };unsigned long objsize;int ret;for (int i = 1; i <= 10; i++){sprintf_s(sql, "select info from final where id='%d'", i);ret = mysql_real_query(&myCont, sql, strlen(sql));if (ret){PostMessage(WM_MyMessage, (WPARAM)L"读取失败1", NULL);}res = mysql_store_result(&myCont);if (res == NULL){PostMessage(WM_MyMessage, (WPARAM)L"读取失败2", NULL);}/* important */row = mysql_fetch_row(res);row_len = mysql_fetch_lengths(res);  /* get the object's length */if (row_len == NULL){PostMessage(WM_MyMessage, (WPARAM)L"读取失败3", NULL);}objsize = row_len[0];object = (char*)malloc(objsize);if (object == NULL){PostMessage(WM_MyMessage, (WPARAM)L"读取失败4", NULL);}memcpy(object, row[0], objsize);if (BIOKEY_DB_ADD(ZKFingerHandle, i, objsize, (byte*)object)){PostMessage(WM_MyMessage, (WPARAM)L"读取成功", NULL);}}g_connect = false;;//mysql_close(&myCont);mysql_free_result(res);return NULL;}

0x06

更新操作：

int CFinger_PrintDlg::Updata_Mysql(byte b_name[], byte* b_data, int len_of_data){if (!g_connect){SendMessage(WM_MyMessage, (WPARAM)L"数据库未连接...", NULL);return 0;}int res;bool return_result = false;mysql_query(&myCont, "SET NAMES GBK"); //设置编码格式char buf[2000] = { NULL };unsigned int escape_size = 2 * len_of_data + 2;char* escape_object = (char *)malloc(escape_size);escape_size = mysql_real_escape_string(&myCont, (char*)escape_object, (char *)b_data, len_of_data);CString id_cs;CString name_cs;int id;char name[20] = { NULL };GetDlgItem(IDC_EDIT1)->GetWindowTextW(id_cs);GetDlgItem(IDC_EDIT2)->GetWindowTextW(name_cs);id = _wtoi(id_cs);WideCharToMultiByte(CP_ACP, 0, name_cs, -1, name, name_cs.GetLength(), NULL, NULL);//int sql_len = sprintf_s(buf, "insert into final(id,name,info) values('%d','%s','%s')", id, name, escape_object);//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值int sql_len = sprintf_s(buf, "UPDATE final SET name='%s',info='%s' where id ='%d'", name, escape_object, id);res = mysql_real_query(&myCont, buf, sql_len);if (!res){return_result = TRUE;if (!res){PostMessage(WM_MyMessage, (WPARAM)L"更新成功", NULL);}}else{PostMessage(WM_MyMessage, (WPARAM)L"更新失败", NULL);}//mysql_close(&myCont);g_connect = false;return return_result;}

0 0