mysql存取二进制数据

1 -- 存取二进制数据可行性分析

考虑1：函数mysql_query存储二进制数据，可行不？也行也不行，比如SQL语句中恰好含有'\0'，而mysql_query又是以'\0'表示SQL语句结束，这样SQL语句被截断就不完整了。查看帮助发现还有一个mysql_real_query，最妙的是它不是使用'\0'终结SQL，而是用一个整形数表示SQL语句的长度。

考虑2：函数mysql_real_query规避了'\0'可能引起的麻烦。如果SQL中的二进制数据是“'”、“"”这些特殊字符呢？看来还得用mysql_real_escape_string过滤下，才能放心使用。

考虑3：经过上面的两步后，存储二进制数据基本上没什么问题。那怎么将其读出来呢？如果直接将读出的数据赋值给string，有可能被截断。解决方法是，先调用mysql_fetch_lengths获得值的长度，然后按长度copy数据。

2 -- 实例程序

三个关键函数：

int mysql_query(MYSQL *mysql, const char *query);
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length);
unsigned long mysql_real_escape_string(MYSQL *mysql, char *d, const char *s, unsigned long len);

#include <string>
#include <iostream>
#include "mysql.h"
const std::string Escape(MYSQL * pHandle, const std::string & sFrom)
{
std::string::size_type iLen = sFrom.size() * 2 + 1;
char * pTo = (char *)malloc(iLen);
memset(pTo, 0x00, iLen);
unsigned long lEscLen = mysql_real_escape_string(pHandle, pTo, sFrom.data(), sFrom.size());
std::string sTo(pTo, lEscLen);
free(pTo);
return sTo;
}
int main()
{
MYSQL * pHandle = mysql_init(NULL);
if (mysql_real_connect(pHandle, "127.0.0.1", "user", "pass", "base", 1206, NULL, 0) == NULL)
{
std::cout << "connect fail" << std::endl;
return -1;
}
{
std::string sTemp("aaa\0bbb", 7); sTemp = Escape(pHandle, sTemp);
std::string sFrom("INSERT INTO t_testsz(myname, mytext) VALUES('name', '");
sFrom.append(sTemp.data(), sTemp.size());//这一步放入一个二进制的数据
sFrom.append("')");
if (mysql_real_query(pHandle, sFrom.data(), sFrom.size()) != 0)
{
std::cout << "mysql exec fail:" << mysql_error(pHandle) << std::endl;
return -1;
}
}
{
std::string sFrom("SELECT myname, mytext FROM t_testsz");
if (mysql_real_query(pHandle, sFrom.data(), sFrom.size()) != 0)
{
printf("%s\n%s", sFrom.c_str(), mysql_error(pHandle));
return -1;
}
MYSQL_RES * pRes = mysql_store_result(pHandle);
for (MYSQL_ROW stRow = NULL; (stRow = mysql_fetch_row(pRes)) != NULL; )
{
unsigned long * lengths = mysql_fetch_lengths(pRes);
std::string sName; sName.append(stRow[0], lengths[0]);
std::string sText; sText.append(stRow[1], lengths[1]);
std::cout << "Name:" << sName << "|size:" << sName.size() << std::endl;
for (std::string::size_type i = 0; i < sName.size(); i++)
{
printf("%02X%c", sName[i], (i == sName.size() - 1?'\n':' '));
}
std::cout << "Text:" << sText << "|size:" << sText.size() << std::endl;
for (std::string::size_type i = 0; i < sText.size(); i++)
{
printf("%02X%c", sText[i], (i == sText.size() - 1?'\n':' '));
}
}
}
return 0;
}

makefile文件：

INCLUDE := -I/usr/local/mysql/include/mysql
LIB := -L/usr/local/mysql/lib/mysql -lmysqlclient
all : main
main : main.cpp
g++ -Wall -o main main.cpp ${INCLUDE} ${LIB}
clean :
rm -rf main *.o

建表SQL语句：

CREATE TABLE `t_testsz` (
`id` int(11) NOT NULL auto_increment,
`myname` varchar(200) NOT NULL default '',
`mytext` varchar(200) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk