Part 1: init initializes MySQL connection related API s:

(1) mysql_init :

MYSQL* mysql_init(MYSQL *mysql);

Returns a MySQL handle. Corresponding to this handle, MySQL will allocate an object in the database and open up memory to store the processing information associated with the connection.
All subsequent operations on this database connection pass through this handle, similar to the sockfd returned by socket().
NULL is returned if MySQL has insufficient internal memory.

(2) mysql_options :

int mysql_options(MYSQL* mysql, enum mysql_option option, const void* arg);

Set additional connection options to control connection behavior.
In MySQL_ After init, MySQL_ Call before connect.
The option parameter is used to specify the type of option to be set;
The arg parameter sets the value of this option.
Common options are:
MYSQL_OPT_RECONNECT: when the connection is found to be disconnected, it will be reconnected automatically (for example, if there is no activity for more than 8 hours, after the connection is disconnected by MySQL server, it will be reconnected automatically when the connection is found to be disconnected when accessing again)
MYSQL_SET_CHARSET_NAME: set the default charset

(3) mysql_real_connect :

MYSQL* mysql_real_connect(	MYSQL* mysql, 
	const char* host, const char* user, const char* passwd, 
	const char* db, unsigned int port, const char* unix_socket, unsigned long client_flag);

It is used to initiate a connection to MySQL server. All subsequent operations on the database need to establish a connection first.

>>Example 1: use example of init() initialization part:

int CDBConn::Init() {
	m_mysql = mysql_init(NULL);
    if(!m_mysql) {
		return -1;
	}
	
    my_bool reconnect = true;
    mysql_options(m_mysql, MYSQL_OPT_RECONNECT, &reconnect);
    mysql_options(m_mysql, MYSQL_OPT_CHARSET_NAME, "utf8mb4");

	if(mysql_real_connect(m_mysql, m_pDBPool->GetDBServerIP(), m_pDBPool->GetUsername(), m_pDBPool->GetPassword(), 
			m_pDBPool->GetDBName(), m_pDBPool->GetDBServerPort(), NULL, 0) ) { 
	    return -1; 
	}

	return 0;
}

Part 2: API for executing "select" query function:

(4) mysql_ping :

int mysql_ping(MYSQL* mysql);

Check whether the connection with MySQL is normal. If the connection has been disconnected and automatic reconnection to MySQL has been set_ OPT_ If reconnect is selected, automatic reconnection is initiated;
If the automatic reconnection option is not enabled, error is returned.

(5) mysql_real_query :

int mysql_real_query(MYSQL* mysql, const char* stmt_str, unsigned long length);

Execution parameter stmt_str the query statement passed in, which does not contain the ';' SQL statement terminator.

(6) mysql_store_result :

MYSQL_RES* mysql_store_result(MYSQL* msyql);

Calling MySQL_ real_ After query, you must call mysql_store_result is used to store query results.
After use, you must call mysql_free_result to release mysql_store_result allocated memory (memory used to save query results).

(7) mysql_free_result :

void mysql_free_result(MYSQL_RES *result);

Used to release the previous mysql_store_result allocated memory.

>>Example 2: use example of select query part:

CResultSet* CDBConn::ExcuteQuery(const char* sql_query) {
    mysql_ping(m_mysql);
    
    if(mysql_real_query(m_mysql, sql_query, strlen(sql_query))) {
        printf("mysql_real_query failed: %s, sql: %s\n", mysql_error(m_mysql), sql_query);
        return NULL;
    }

	MYSQL_RES* res = mysql_store_result(m_mysql);
	if(!res) {
        printf("mysql_store_result failed: %s\n", mysql_error(m_mysql));
        return NULL;
    }
	//The query result will be MYSQL_RES is encapsulated into a CResultSet object and returned to the superior calling function. Call MySQL after the outer function is used_ free_ Result to free this memory
	CResultSet* result_set = new CResultSet(res);
	return result_set;
}

(8) mysql_num_fields :

unsigned int mysql_num_fields(MYSQL_RES* result);

Returns the number of columns in the query result set.

(9) mysql_fetch_fields :

MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES* result);

Returns all MySQL in the query result set_ An array of field structures. Each structure in the array represents the field definition of a column. "Attribute description of a column", including column name, column data type, etc

(10) mysql_fetch_row :

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

Retrieves the next row in the query result set (MYSQL_RES).
In MySQL_ store_ When used after result, if there is no row to retrieve or there is an error in the retrieval, mysql_fetch_row() returns NULL;
The data of in-line values is generated by mysql_ num_ Fields () (i.e. the number of columns). These pointers can be accessed through row[column_index] (from row[0] to row[mysql_num_fields()-1])

>>Example 3: parsing query MySQL query results example:

//Mysql_ store_ MySQL returned by result_ After parsing and decomposing the res query structure, it is stored in the CResultSet object:
class CResultSet {
public:
    CResultSet(MYSQL_RES* res);
    virtual ~CResultSet();
	
	bool Next();
	int GetInt(const char* key);
	char* GetString(const char* key);
private:
	int _GetIndex(const char* key);
private:
	MYSQL_RES*		  m_res;
	MYSQL_ROW		  m_row;
	map<string, int>  m_key_map;
};

Part 3: API for performing insert / delete / update operations:

(11) mysql_affected_rows :

my_ulonglong  mysql_affected_rows(MYSQL *mysql);

Returns the number of rows changed in the last UPDATE, deleted in the last DELETE, or inserted in the last INSERT statement.
For UPDATE, DELETE and INSERT statements, you can use MSYQL_ Called immediately after query();
For SELECT statements, mysql_affected_rows() works the same way as mysql_num_rows is similar.

>>Example 4: example of executing Update operation:

//For example, char* sql_query = "update [table_name] set [change_value] where [select_option];"
bool CDBConn::ExcuteUpdate(const char* sql_query) {
    mysql_ping(m_mysql);

    if(mysql_real_query(m_mysql, sql_query, strlen(sql_query))) {
        printf("mysql_real_query failed: %s\n", mysql_error(m_mysql));
        return false;
    }
    
    //Using msyql_affected_rows check whether statements such as UPDATE take effect correctly:
    if(mysql_affected_rows(m_mysql) > 0) {	
		return true;
	}
	else {
        return false;
    }
}

Part IV: PrepareStatement anti injection:

PrepareStatement mainly uses precompile to prevent SQL injection.
Precompiled statements replace the values in such statements with "placeholders" (?), which can be regarded as "templating" or "parameterizing" sql statements.
Compile once and run many times, eliminating the process of parsing and optimization.

Precompiled functions:
(1) The precompiling phase can optimize the execution of sql;
(2) Prevent SQL injection:
Pre compilation is used, and the parameters injected later will not be SQL compiled, that is, the parameters injected later will not be considered as an SQL statement,
By default, it is a parameter, and or or and in the parameter are not reserved words in SQL statements.

(12) mysql_stmt_init :

Create MYSQL_STMT handle

MYSQL_STMT*  mysql_stmt_init(MYSQL *mysql);

Description: create MySQL_ Stmt handle. For this handle, MySQL should be used_ stmt_ close(MYSQL_STMT*); Release.
Return value: points to MySQL when successful_ Pointer of stmt structure; NULL on failure.

(13) mysql_stmt_prepare :

The function is equivalent to registering an SQL statement with "?" parameter marker. Here should be the operation of [precompile]

int  mysql_stmt_prepare(MYSQL_STMT* stmt, const char* query, unsigned long length);

Input:
Given MYSQL_STMT_ MySQL returned by init_ Stmt handle and the SQL statement to be executed (string, string length).
By embedding the question mark character "?" in the appropriate position of the SQL string, the application can contain one or more parameter markers in the SQL statement.
The tag is only valid at a specific location in the SQL statement.
You must use MySQL before executing the statement_ stmt_ bind_ Param() binds the "parameter marker" (?) to the "application variable".
(MySQL _stmt _bind _paramis equivalent to a parameter passing process, where "is a formal parameter and the variable passed in by the application is an actual parameter.)
Use examples:

mysql_stmt_prepare( m_stmt, sql.c_str(), sql.size() );

string sql = "insert into IMMessage_x (related, fromId, toId, msgId, content, status, type, created, updated) values (?,?,?,?,?,?,?,?,?)";

(14) mysql_stmt_param_count :

unsigned long  mysql_stmt_param_count(MYSQL_STMT* stmt);

Returns the number of parameter markers in the preprocessing statement.

(15) mysql_stmt_bind_param :

my_bool  mysql_stmt_bind_param(MYSQL_STMT* stmt, MYSQL_BIND* bind);

It is used to bind data ("arguments") for "parameter markers" (?) in SQL statements to pass to MySQL_ stmt_ In prepare? Location.
MySQL is required to inject this function_ Bind * is an input parameter for an array of element types, so new MySQL is required before calling the function_ Bind [param_count] constructs an array.

(16) mysql_stmt_execute :

int  mysql_stmt_excute(MYSQL_STMT* stmt);

Executes a preprocessed query related to the statement.
Sends the value of the currently bound parameter marker to the server, which replaces the marker with the newly provided data.

If the statement is insert / update / delete, call mysql_stmt_affected_rows() confirms the total number of rows changed / inserted / deleted;
If the statement is select, you need to call msyql_stmt_fetch() to get the data.

(17) mysql_stmt_affected_rows :

my_ulonglong  mysql_stmt_affected_rows(MYSQL_STMT* stmt);

Returns the total number of rows changed, deleted, or inserted by the last executed statement.

(18) mysql_stmt_fetch :

Same as MySQL_ fetch_ row / mysql_ fetch_ Use of fields.

(19) mysql_stmt_insert_id :

my_ulonglong  mysql_stmt_insert_id(MYSQL_STMT* stmt);

Return the preprocessed "INSERT" or "UPDATE" statement to auto_ The value generated by the increment column.
Include in
That is, if the attribute of a column in a table is AUTO_INCREMENT is self incremented. After inserting or updating this table,
Call MySQL_ stmt_ INSERT_ The ID () function can get the value of the self incremented column of the row element added by the INSERT operation.

>>Example 5: use precompiling to prevent SQL injection example:

To be added......

MYSQL_STMT* m_stmt = mysql_stmt_init(MYSQL* m_mysql);
mysql_stmt_prepare(m_stmt, sql.c_str(), sql.size());	//sql="insert into ... values (???)"
	mysql_stmt_param_count(m_stmt);	//Just a security check
MYSQL_BIND* m_param_bind = new MYSQL_BIND[m_param_cnt];		//new a piece of heap memory

m_param_bind[index].buffer_type 	= MYSQL_TYPE_STRING;
m_param_bind[index].buffer			= (char*)value.c_str();
m_param_bind[index].buffer_length	= value.size();
/*
m_param_bind[index].buffer_type		= MYSQL_TYPE_LONG;
m_param_bind[index].buffer			= &value;
*/

mysql_stmt_bind_param(m_stmt, m_param_bind);
mysql_stmt_excute(m_stmt);
mysql_stmt_affected_rows();	/ mysql_stmt_fetch_fields();