MYSQL[03]存取位元據

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/mysqlLIB := -L/usr/local/mysql/lib/mysql -lmysqlclientall : mainmain : main.cppg++ -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