mysql c api安装,C++封裝MySQL預處理C API

#ifndef _PRE_STATEMENT_H_

#define _PRE_STATEMENT_H_

#include "mysql.h"

#include

class pre_statement

{

public:

pre_statement();

~pre_statement();

int init(MYSQL* mysql, string& sql);

int set_param_bind(int index, int& value);

int set_param_bind(int index, string& value);

int set_param_bind(int index, MYSQL_TIME& value);

int execute();

int query();

int set_param_result(int index, enum_field_types type, char* buffer, unsigned long buffer_length, unsigned long*length);

int get_result();

int fetch_result();

void free();

private:

MYSQL_STMT* m_stmt;

MYSQL_BIND* m_param_bind;

MYSQL_BIND* m_result_bind;

MYSQL_RES* m_result;

unsigned long m_param_count;

unsigned int m_result_count;

};

#endif

#include "pre_statement.h"pre_statement::pre_statement(){m_stmt = NULL;m_param_bind = NULL;m_result_bind = NULL;m_result = NULL;m_param_count = 0;m_result_count = 0;}pre_statement::~pre_statement(){free();}int pre_statement::init(MYSQL* mysql, string& sql){m_stmt = mysql_stmt_init(mysql);if (!m_stmt){return 1;}if (mysql_stmt_prepare(m_stmt, sql.c_str(), sql.size())){return 1;}m_param_count = mysql_stmt_param_count(m_stmt);if (m_param_count > 0){m_param_bind = new MYSQL_BIND[m_param_count];if (!m_param_bind){return 1;}memset(m_param_bind, 0, sizeof(MYSQL_BIND)*m_param_count);}return 0;}int pre_statement::set_param_bind(int index, int& value){if (index >= m_param_count){perror("索引超出總數！\n");return 1;}m_param_bind[index].buffer_type = MYSQL_TYPE_LONG;m_param_bind[index].buffer = &value;return 0;}int pre_statement::set_param_bind(int index, string& value){if (index >= m_param_count){perror("索引超出總數！\n");return 1;}m_param_bind[index].buffer_type = MYSQL_TYPE_STRING;m_param_bind[index].buffer = (void*)(value.c_str());m_param_bind[index].buffer_length = value.size();return 0;}int pre_statement::set_param_bind(int index, MYSQL_TIME& value){if (index >= m_param_count){perror("索引超出總數！\n");return 1;}m_param_bind[index].buffer_type = MYSQL_TYPE_TIMESTAMP;m_param_bind[index].buffer = &value;m_param_bind[index].buffer_length = sizeof(value);return 0;}int pre_statement::set_param_result(int index, enum_field_types type, char* buffer, unsigned long buffer_length, unsigned long*length){if (index >= m_result_count){perror("索引超出總數！\n");return 1;}m_result_bind[index].buffer_type = type;m_result_bind[index].buffer = buffer;m_result_bind[index].buffer_length = buffer_length;m_result_bind[index].length = length;return 0;}int pre_statement::execute(){if (!m_stmt){return 1;}if (mysql_stmt_bind_param(m_stmt, m_param_bind)){//mysql_stmt_errno(m_stmt);return 1;}if (mysql_stmt_execute(m_stmt)){//mysql_stmt_errno(m_stmt);return 1;}if (0 == mysql_stmt_affected_rows(m_stmt)){perror("更新失敗沒有改變！\n");return 1;}return 0;}int pre_statement::query(){if (!m_stmt){return 1;}if (mysql_stmt_bind_param(m_stmt, m_param_bind)){//mysql_stmt_errno(m_stmt);return 1;}m_result = mysql_stmt_result_metadata(m_stmt);if (NULL == m_result){//mysql_stmt_errno(m_stmt);return 1;}else{m_result_count = mysql_num_fields(m_result);m_result_bind = new MYSQL_BIND[m_result_count];if (!m_result_bind){return 1;}memset(m_result_bind, 0, sizeof(MYSQL_BIND)*m_result_count);}if (mysql_stmt_execute(m_stmt)){//mysql_stmt_errno(m_stmt);return 1;}return 0;}int pre_statement::get_result(){if (mysql_stmt_bind_result(m_stmt, m_result_bind)){return 1;}if (mysql_stmt_store_result(m_stmt)){return 1;}return 0;}int pre_statement::fetch_result(){if (!mysql_stmt_fetch(m_stmt)){return 0;}else{return 1;}}void pre_statement::free(){if (m_stmt){mysql_stmt_close(m_stmt);m_stmt = NULL;}if (m_param_bind){delete[]m_param_bind;m_param_bind = NULL;}if (m_result_bind){delete[]m_result_bind;m_result_bind = NULL;}if (NULL != m_result){mysql_free_result(m_result);m_result = NULL;}m_param_count = 0;m_result_count = 0;}

使用步驟說明：

1. init是將SQL語句和MySQL連接實例綁定在一起的，並且檢測sql語句中含有多少個“？”，然后對應的創建bind數組，后面對數組內容賦值，就相當於對每個“？”賦值。

2. set_param_bind就是對SQL語句中的問號進行賦值，第一個索引index為0，和數組下標規則一樣。

3. 執行execute就是執行SQL語句，先是把之前賦值了的bind帶入到SQL語句中，然后execute執行，最后會檢測是否對數據庫行數造成了影響。

4. 如果此時SQL語句不是增刪改，而是查，那么我們就不是步驟3調用execute了而是調用query，也是先把之前賦值了的bind帶入到SQL語句中，然后metadata會計算這個查詢語句得到的結果集的一個元數據(不帶數據的只是結果集的屬性列表)，我們通過這個元數據要計算結果集列的數目，所以就num_fields。

5. 為結果集綁定bind，使用set_param_result，這里的buffer通過后面的執行，會得到列值。

6. 執行get_result獲取結果集。

7. 執行fetch_result對結果集逐行來進行獲取。

8. 最后關閉預處理語句，釋放bind的內存，釋放結果集。