mysql的c接口,使用普通的query方法,很难获取到二进制数据。
使用mysql_stmt_前缀的函数,用mysql_stmt_bind_result的方式,可针对各种类型的数据进行获取操作,示例代码是我在linux中编译通过的,如下所示:
#include <iostream>
#include <cstring>
#include <mysql/mysql.h>
using namespace std;
void test_insert_stmt(MYSQL *mysql)
{
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
if (stmt == NULL)
{
std::cout << "stmt is NULL. mysql_stmt_init failed. "
<< mysql_error(mysql) << std::endl;
return ;
}
const char str_sql[] = "INSERT INTO tb_bin_data(bin_data) VALUES(?)";
int res = mysql_stmt_prepare(stmt, str_sql, sizeof(str_sql) - 1);
if (res != 0)
{
std::cout << "mysql_stmt_prepare INSERT failed."
<< mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
return ;
}
char bin_data[] = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
MYSQL_BIND bind[1];
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_BLOB;
bind[0].is_null = NULL;
bind[0].buffer = bin_data;
bind[0].buffer_length = sizeof(bin_data);
res = mysql_stmt_bind_param(stmt, bind);
if (res != 0)
{
std::cout << "mysql_stmt_bind_param failed. "
<< mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
return;
}
std::cout << "mysql_stmt_bind_param successful." << std::endl;
// res = mysql_stmt_send_long_data(stmt, 0, escape_bin, strlen(escape_bin));
// std::cout << "mysql_stmt_send_long_data result is " << res << std::endl;
res = mysql_stmt_execute(stmt);
std::cout << "mysql_stmt_execute() func result is " << res << std::endl;
mysql_stmt_close(stmt);
}
void test_query_stmt(MYSQL *mysql)
{
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
if (stmt == NULL)
{
std::cout << "stmt is NULL. mysql_stmt_init failed. "
<< mysql_error(mysql) << std::endl;
return ;
}
const char str_sql[] = "SELECT id,bin_data FROM tb_bin_data where id=?";
int res = mysql_stmt_prepare(stmt, str_sql, sizeof(str_sql) - 1);
if (res != 0)
{
std::cout << "mysql_stmt_prepare SELECT failed."
<< mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
return;
}
unsigned int id = 1;
MYSQL_BIND bind[1];
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].is_null = NULL;
bind[0].is_unsigned = true;
bind[0].buffer = &id;
bind[0].buffer_length = sizeof(id);
res = mysql_stmt_bind_param(stmt, bind);
if (res != 0)
{
std::cout << "mysql_stmt_bind_param failed. "
<< mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
return ;
}
std::cout << "mysql_stmt_bind_param successful." << std::endl;
// res = mysql_stmt_send_long_data(stmt, 0, escape_bin, strlen(escape_bin));
// std::cout << "mysql_stmt_send_long_data result is " << res << std::endl;
unsigned int int_data;
bool is_null[2];
bool error[2];
char blob_data;
unsigned long length[2] = {sizeof int_data, sizeof blob_data};
MYSQL_BIND bind2[2];
memset(bind2, 0, sizeof bind2);
bind2[0].buffer_type= MYSQL_TYPE_LONG;
bind2[0].buffer= &int_data;
bind2[0].is_null= &is_null[0];
bind2[0].is_unsigned = true;
bind2[0].buffer_length= length[0];
bind2[0].length= &length[0];
bind2[0].error= &error[0];
bind2[1].buffer_type= MYSQL_TYPE_BLOB;
bind2[1].buffer= &blob_data;
bind2[1].is_null= &is_null[1];
bind2[1].buffer_length= length[1];
bind2[1].length= &length[1];
bind2[1].error= &error[1];
/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind2))
{
fprintf(stderr, " mysql_stmt_bind_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
return;
}
res = mysql_stmt_execute(stmt);
std::cout << "mysql_stmt_execute() func result is " << res << std::endl;
/* Now buffer all results to client (optional step) */
/*
if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, " mysql_stmt_store_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
return;
}
*/
/* Fetch all rows */
int row_count= 0;
fprintf(stdout, "Fetching results ...\n");
while (1)
{
int result = mysql_stmt_fetch(stmt);
if (result == MYSQL_NO_DATA) {
cout << "no data!" << endl;
break;
}
if (result == 1) {
cout << "mysql_stmt_fetch error: " << mysql_stmt_error(stmt) << endl;
break;
}
row_count++;
printf("row %d: ", row_count);
if (is_null[0]) {
printf("id=NULL ");
} else {
printf("id=%d(%d) ", int_data, length[0]);
}
if (is_null[1]) {
printf("bin_data=NULL ");
} else {
unsigned long start = 0;
unsigned long totallength = length[1];
printf("bin_data=");
while (start < totallength) {
bind2[1].buffer = &blob_data;
bind2[1].buffer_length = 1;
mysql_stmt_fetch_column(stmt, &bind2[1], 1, start);
printf("%X", blob_data);
start += bind2[1].buffer_length;
}
printf("(%d) ", totallength);
}
printf("\n");
}
mysql_stmt_close(stmt);
}
int main(int argc, char **argv)
{
MYSQL *mysql = mysql_init(NULL);
if (!mysql_real_connect(mysql, "127.0.0.1", "root", "", "test", 0, NULL, 0)) {
fprintf(stderr, "Failed to mysql to database: Error: %s\n",
mysql_error(mysql));
}
test_insert_stmt(mysql);
test_query_stmt(mysql);
mysql_close(mysql);
return 0;
}
用mysql_real_query也能达到相同的效果:
#include <iostream>
#include <cstring>
#include <mysql/mysql.h>
using namespace std;
const std::string Escape(MYSQL * mysql, 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(mysql, pTo, sFrom.data(), sFrom.size());
std::string sTo(pTo, lEscLen);
free(pTo);
return sTo;
}
void test_insert(MYSQL *mysql)
{
char data[] = {0, 1, 2};
string str(data, 3);
str = Escape(mysql, str);
string sql = "INSERT INTO tb_bin_data (bin_data) values ('";
sql.append(str.data(), str.size());
sql.append("')");
cout << sql << endl;
if (mysql_real_query(mysql, sql.data(), sql.size()) != 0)
{
std::cout << "mysql exec fail:" << mysql_error(mysql) << std::endl;
}
}
void test_query(MYSQL *mysql)
{
string sFrom = "select id, bin_data from tb_bin_data where id = 3";
if (mysql_real_query(mysql, sFrom.data(), sFrom.size()) != 0)
{
printf("Error while query sql: %s\n%s\n", sFrom.c_str(), mysql_error(mysql));
return;
}
MYSQL_RES *res = mysql_store_result(mysql);
for (MYSQL_ROW row = NULL; (row = mysql_fetch_row(res)) != NULL;)
{
unsigned long *lengths = mysql_fetch_lengths(res);
string id = row[0];
string data(row[1], lengths[1]);
cout << id << " ";
for (int i = 0; i < data.size(); i++)
printf("%X", data[i]);
printf("\n");
}
}
int main(int argc, char **argv)
{
MYSQL *mysql = mysql_init(NULL);
if (mysql_real_connect(mysql, "127.0.0.1", "root", "", "test", 0, NULL, 0) == NULL)
{
std::cout << "connect fail" << std::endl;
return -1;
}
test_insert(mysql);
mysql_close(mysql);
return 0;
}
如果使用mysqlmgr这个封装的hpp,使用将更加简单(https://github.com/alongL/mysqlmgr):
#include <iostream>
#include "mysqlmgr.hpp"
using namespace std;
void insert()
{
auto &sqlmgr = Mysqlmgr::getinstance();
auto stmt = sqlmgr.getstmt("INSERT INTO tb_bin_data (bin_data) values (?)");
if (!stmt)
{
printf("insert error: %s", sqlmgr.geterror());
return;
}
char data[] = {0, 1, 2, 3};
int length;
stmt.bind_param(0, MYSQL_TYPE_BLOB, (void*)data, sizeof data, NULL, NULL);
if (stmt.execute())
{
printf("insert error: %s", sqlmgr.geterror());
return;
}
printf("insert sucess.\n");
}
void query()
{
auto &sqlmgr = Mysqlmgr::getinstance();
Result r = sqlmgr.query("SELECT id, bin_data FROM tb_bin_data");
if (!r)
{
printf("query error %s\n", sqlmgr.geterror());
return;
}
printf("query result:\n");
while (Row row = r.next())
{
int id = row.columnint(0);
string data = row[1];
printf("id:%d data: ", id);
for (int i = 0; i < data.size(); i++)
printf("%X", data[i]);
printf("\n");
}
}
int main(int argc, char **argv)
{
auto &sqlmgr = Mysqlmgr::getinstance();
if (!sqlmgr.connect("127.0.0.1", "root", "", "test", 0, NULL, 0))
{
printf("can not connect to mysql: %s\n", sqlmgr.geterror());
return -1;
}
query();
return 0;
}
我修正过mysqlmgr.hpp,在mysql8.0中测试通过。
#pragma once
#include <string>
#include <mysql/mysql.h>
#include <memory.h>
class Stmt;
class Result;
class Stmt
{
private:
MYSQL_STMT *stmt;
size_t count;
MYSQL_BIND *params;
long unsigned int str_length;
public:
Stmt() : stmt(0), count(0), params(0) {}
Stmt(MYSQL_STMT *stmt)
: stmt(stmt), count(mysql_stmt_param_count(stmt)), params(new MYSQL_BIND[count])
{
memset(params, 0, count * sizeof(MYSQL_BIND));
}
~Stmt()
{
if (stmt)
mysql_stmt_close(stmt);
delete[] params;
}
Stmt(const Stmt &) = delete;
Stmt &operator=(const Stmt &) = delete;
Stmt(Stmt &&x) : stmt(x.stmt), count(x.count), params(x.params), str_length(x.str_length)
{
x.stmt = 0;
x.params = nullptr;
}
Stmt &operator=(Stmt &&x)
{
stmt = x.stmt;
count = x.count;
params = x.params;
str_length = x.str_length;
x.stmt = 0;
x.params = nullptr;
return *this;
}
operator bool()
{
return !!stmt;
}
inline int step() // the same to execute
{
return execute();
}
inline int execute() // run sql
{
mysql_stmt_bind_param(stmt, params);
return mysql_stmt_execute(stmt);
}
void bind_param(int i, enum_field_types buffer_type, void *buffer, int buffer_length, bool *is_null, unsigned long *length)
{
if (i >= count)
{
printf("Stmt::bind_param error! index:%d, count:%d \n", i, count);
return;
}
MYSQL_BIND &b = params[i];
b.buffer_type = buffer_type;
b.buffer = (char *)buffer;
b.buffer_length = buffer_length;
b.is_null = is_null;
b.length = length;
}
inline void bindint64(int i, int64_t x)
{
bind_param(i, MYSQL_TYPE_LONGLONG, (char *)&x, 0, 0, 0);
}
inline void bindint(int i, const int &x)
{
bind_param(i, x);
}
// char *x should long live tot execute()
inline void bindtext(int i, const char *x, int len)
{
bind_param(i, x, len);
}
// string x cann't be a temp string
inline void bindstring(int i, const std::string &x)
{
const char *c = x.c_str();
bind_param(i, MYSQL_TYPE_STRING, (char *)c, x.size(), 0, &(params[i].buffer_length));
}
private:
inline void bind_param(int i, const int &x)
{
bind_param(i, MYSQL_TYPE_LONG, (char *)&x, 0, 0, 0);
}
inline void bind_param(int i, const std::string &x)
{
const char *c = x.c_str();
bind_param(i, MYSQL_TYPE_STRING, (char *)c, x.size(), 0, &(params[i].buffer_length));
}
inline void bind_param(int i, const char *x, int len)
{
bind_param(i, MYSQL_TYPE_STRING, (char *)x, len, 0, &(params[i].buffer_length));
}
};
class Row
{
private:
MYSQL_ROW row;
unsigned long *lengths;
public:
Row() : row(nullptr), lengths(nullptr) {}
Row(MYSQL_ROW row, unsigned long *lengths) : row(row), lengths(lengths) {}
Row(Row &&x) : row(x.row), lengths(x.lengths)
{
x.row = nullptr;
x.lengths = nullptr;
}
Row &operator=(Row &&x)
{
using std::swap;
swap(*this, x);
return *this;
}
operator bool()
{
return !!row;
}
std::string operator[](size_t n)
{
return std::string(row[n], lengths[n]);
}
std::string columntext(size_t n)
{
return std::string(row[n], lengths[n]);
}
int columnint(size_t n)
{
auto str = std::string(row[n], lengths[n]);
return std::stoi(str);
}
int columnint64(size_t n)
{
auto str = std::string(row[n], lengths[n]);
return std::stoll(str);
}
};
class Result
{
private:
MYSQL_RES *res;
int num_fields;
public:
Result() : res(0), num_fields(0) {}
Result(MYSQL_RES *res) : res(res), num_fields(mysql_num_fields(res)) {}
Result(Result &&r)
{
res = r.res;
r.res = 0;
num_fields = r.num_fields;
}
~Result()
{
if (res)
mysql_free_result(res);
}
operator bool()
{
return !!res;
}
Result &operator=(Result &&r)
{
mysql_free_result(res);
res = r.res;
num_fields = r.num_fields;
r.res = 0;
return *this;
}
Result &operator=(const Result &) = delete;
Result(const Result &) = delete;
Row fetch_row()
{
MYSQL_ROW row = mysql_fetch_row(res);
unsigned long *lengths = mysql_fetch_lengths(res);
return Row{row, lengths};
}
inline Row next()
{
return fetch_row();
}
};
class Mysqlmgr
{
private:
Mysqlmgr()
{
handle = mysql_init(0);
// https://dev.mysql.com/doc/refman/5.7/en/c-api-auto-reconnect.html
// set auto reconnect flag. When mysql restarted, we will auto reconnect
bool reconnect = 1;
mysql_options(handle, MYSQL_OPT_RECONNECT, &reconnect);
}
MYSQL *handle;
Mysqlmgr(const Mysqlmgr &) = delete; // no copy
Mysqlmgr &operator=(const Mysqlmgr &) = delete; // no assignment
public:
inline static Mysqlmgr &getinstance()
{
static Mysqlmgr instance;
return instance;
};
~Mysqlmgr()
{
if (handle)
{
mysql_close(handle);
}
}
bool connect(const char *host, const char *user, const char *password,
const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)
{
MYSQL *h = mysql_real_connect(handle, host, user, password,
db, port, unix_socket, client_flag);
if (h)
return true;
else
return false;
}
Stmt getstmt(const std::string &s)
{
MYSQL_STMT *stmt = mysql_stmt_init(handle);
int x = mysql_stmt_prepare(stmt, s.c_str(), s.size());
if (x != 0)
{
return Stmt{};
}
return Stmt{stmt};
}
inline Result query(const std::string &s)
{
int x = mysql_real_query(handle, s.c_str(), s.size());
if (x != 0)
{
return Result{};
}
return use_result();
}
int execSQL(const std::string &sql)
{
if (!mysql_real_query(handle, sql.c_str(), (unsigned long)sql.length()))
{
return (int)mysql_affected_rows(handle);
}
else
{
return -1;
}
}
inline int more_results()
{
return mysql_more_results(handle);
}
inline int next_result()
{
int x = mysql_next_result(handle);
return x;
}
inline Result use_result()
{
MYSQL_RES *result = mysql_use_result(handle);
if (!result)
{
Result{};
}
return Result{result};
}
const char *geterror()
{
return mysql_error(handle);
}
int ping()
{
if (!mysql_ping(handle))
{
return 0;
}
return -1;
}
int create_db(const std::string &strName)
{
std::string temp;
temp = "CREATE DATABASE ";
temp += strName;
if (!mysql_real_query(handle, strName.c_str(), (unsigned long)temp.length()))
return 0;
else
return -1;
}
int drop_db(const std::string &strName)
{
std::string temp;
temp = "DROP DATABASE ";
temp += strName;
if (!mysql_real_query(handle, temp.c_str(), (unsigned long)temp.length()))
{
return 0;
}
else
{
return -1;
}
}
};