On the underlying data structure and function support of C + + operating MySQL data query

Article catalog

recent developments

These days, half the time is spent practicing cars, which slows down the completion of the design. And recently, perfectionism has become more and more serious, so a small version of tuning has been added. It was supposed to enter the second phase (main redis) today. As a result, the first version is still being tuned. So it is still the main mysql.

After writing mysql code for so many days, I dare not say that I am very skilled, but one thing I must do is to take out the "essence" and air it. I can't tune the API every day (I'm really confused because I haven't seen the API source code)

Sample function

Let's start with a function example and put it in: MySQL is a class I encapsulated. The function name has not been changed. It is original.

MySQL mysql;
if (mysql.connect())
{
    MYSQL_RES *res = mysql.query(sql1);		//first blood
    if (res != nullptr)
    {
            ···
        int num_fields = mysql_num_fields(res);	//notes
					 
					 //double kill
        MYSQL_ROW row = mysql_fetch_row(res); //mysql_ fetch_ Each time row() is executed, a piece of data is taken from the resource, that is, the result set,
                                                  //It is returned in the form of an array. The last piece of data is obtained at the current time, and an empty result is returned.
                                                  //The returned array is a one-dimensional index group, and each subscript corresponds to the sorting of the database one by one
        while (row != nullptr)
        {
            for (int i = 0; i < num_fields; i++)
            {
                    ···
            }
                
            row = mysql_fetch_row(res);	//notes
        }
        mysql_free_result(res);	//notes
    }
}
// Query operation
MYSQL_RES *MySQL::query(string sql)
{
    if (mysql_query(_conn, sql.c_str()))
    {
        LOG_INFO << __FILE__ << ":" << __LINE__ << ":" << sql << "Query failed!";
        cout << mysql_error(_conn) << endl;
        return nullptr;
    }

    return mysql_use_result(_conn);
}

This involves some data structures and functions commonly used in mysql query. If the composition is not clear, it is confused to use it!!!

data structure

MYSQL_RES

This is a data structure for storing mysql result sets. It's boring to say so. Look directly at the composition:

typedef struct st_mysql_res {
    my_ulonglong row_count;                               // Number of rows in the result set
    unsigned int field_count, current_field;              // Number of columns in the result set, current column
    MYSQL_FIELD *fields;                                  // Column information for result set
    MYSQL_DATA *data;                                     // Data of result set
    MYSQL_ROWS *data_cursor;                              // Cursor of result set
    MEM_ROOT field_alloc;                                 // Memory structure
    MYSQL_ROW row;                                        // Used when not buffered
    MYSQL_ROW current_row;                                // mysql_store_result is used. Current row
    unsigned long *lengths;                               // Length of each column
    MYSQL *handle;                                        // mysql_use_result will be used.
    my_bool eof;                                          // End of line
} MYSQL_RES;

There are things we are very concerned about: row number, column number and data.

MYSQL_DATA

typedef struct st_mysql_data {
    my_ulonglong rows;
    unsigned int fields;
    MYSQL_ROWS *data;
    MEM_ROOT alloc;
} MYSQL_DATA; // Structure of data set 

MYSQL_ROWS

typedef struct st_mysql_rows {
    struct st_mysql_rows *next; /* list of rows */
    MYSQL_ROW data;
} MYSQL_ROWS;        //The linked list node of mysql data.

MYSQL_ROW

typedef char** MYSQL_ROW; /* The value of each row returned is all represented by a string*/ 

MYSQL_FIELD

typedef struct st_mysql_field {
    char *name;  /* Name of column */
    char *table;                               /* Table of column if column was a field */
    char *def;                                 /* Default value (set by mysql_list_fields) */
    enum enum_field_types type;                /* Type of field. Se mysql_com.h for types */
    unsigned int length;                       /* Width of column */
    unsigned int max_length;                   /* Max width of selected set */
    unsigned int flags;                        /* Div flags */
    unsigned int decimals;                     /* Number of decimals in field */
} MYSQL_FIELD;  //Structure of column information 

Related API

mysql_store_result

MYSQL_RES *mysql_store_result(MYSQL *mysql); 

mysql_store_result() reads all the results of the query to the client and allocates one MYSQL_RES structure.

If the query does not return a result set, mysql_store_result() will return a NULL pointer.

If reading the result set fails, mysql_store_result() also returns a NULL pointer. By checking MySQL_ Whether error() returns a non empty string, MySQL_ Whether errno() returns a non-zero value or MySQL_ field_ Whether count() returns 0, you can check whether there is an error.

mysql_use_result

MYSQL_RES *mysql_use_result(MYSQL *mysql);
//NULL if an error occurs.

mysql_use_result() saves the results in the server, which must pass mysql_fetch_row() to retrieve each row separately. And mysql_store_result() is faster and uses less memory.

On the other hand, this binds the server and prevents other threads from updating any tables (indeed, I tried and couldn't execute any commands until free). If you are doing a lot of processing operations for each row on the client side, you should not use mysql_use_result().

Once the operation on the result set is completed, you must call mysql_free_result().

mysql_fetch_row

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

Retrieves the next row of the result set, and returns null at the end of the retrieval.

The number of columns in the result set is determined by mysql_num_fields(result). If the line is saved, call mysql_ fetch_ The values returned by row () will access the pointers of these values according to row[0] to row[mysql_num_fields(result)-1]. The NULL value in the row is indicated by a NULL pointer.

You can call mysql_fetch_lengths() to get the length of the field value in the row. For empty fields and fields containing NULL, the length is 0. They can also be distinguished by examining pointers to field values.

mysql_fetch_field

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);

Return using MySQL_ The column of the result set of the field structure. Call this function repeatedly to retrieve information about all columns in the result set. When there are no remaining fields, mysql_fetch_field() returns NULL.

MySQL will be reset every time a new SELECT query is executed_ fetch_ Field() to return information about the first field. Call mysql_field_seek() also affects mysql_ fetch_ The field returned by field().

If MySQL is called_ fetch_ Field() to request the length of the BLOB field. MySQL will return the default blob length (8KB). 8KB is selected because MySQL does not know the maximum length of blob. It should be configurable in the future. Once the result set is retrieved, field - > max_ Length will contain the length of the maximum value of the column in a specific query.

mysql_free_result

void mysql_free_result(MYSQL_RES *result);

Release by mysql_store_result(),mysql_use_result(),mysql_ list_ The memory allocated for the result set, such as DBS (). After completing the operation on the result set, you must call mysql_free_result() frees the memory used by the result set.

Do not attempt to access the result set after the release is complete.

Am I clear?

Posted on Wed, 24 Nov 2021 02:56:49 -0500 by Foser