mysql api 学习笔记

 一、认识mysql客户端可以使用的库

libmysqlclient.so.主版本号.次版本号        ====  通过网络连接到一个独立运行的数据库服务程序

libmysqld.so.主版本号.次版本号             ====   客户端本身内嵌mysql服务代码                    

当这些库的主版本号改变时，都需要重新编译程序。因为当一个库的主版本号改变，其所提供的api可能会发生改变，例如函数的调用参数个数发生改变。

二、客户端和服务端的缓存分配方式

   默认，客户端所能使用的最大缓存为16MB，对应的服务端为1MB。并不是一开始就分配最大缓存，而是在现有缓存不满足需求下才分配更大的缓存。
  通过修改max_allowed_packet 来修改最大缓存配置值。


  在服务端，当一个查询会话结束后，缓存大小自动缩减到net_buffer_lenght, 而客户端的缓存到程序结束之后才会被回收。

三、mysql 编译帮助工具

gcc `mysql_config --cflags` `mysql_config --include` `mysql_config --libs` progname.c

当出现类似下面的错误：            

          mf_format.o(.text+0x201): undefined reference to `__lxstat'
说明当前的编译环境和库文件的编译环境不一样。解决这个问题的方法是，在官网下载库的源代码，自己编译。

四、mysql 客户客户端程序编写要点

                1 需要为 SIGPIPE 信号安装处理函数，方法为
                                    mysql_library_init
                                    sigaction(SIGPIPE, &act, NULL);

                 2 对于多线程客户端程序， 一个connection就是一个临界区，因此在mysql_query() 和 mysql_store_result() 之间需要使用锁。例如pthread_mutex_lock 和 pthread_mutex_unlock


                            在所有线程被创建之前调用mysql_library_init(), 在线程的函数内首先 要mysql_thread_init() 作为开始。 在线程结束之前要调用mysql_thread_end()。



                  3 API 详解
                      1) int mysql_library_init(int argc, char **argv, char **groups)
                                    当使用内嵌数据库时才使用 argv, groups 参数，这样可以向数据库服务传递配置参数；如果只是单纯客户端程序，可以这样调用
                         int cal_ret = mysql_library_init(0, NULL, NULL);
                         if (cal_ret == 0) // call success

                     2)  void mysql_library_end() 
                                     当使用完数据库，调用这个函数来释放资源。

                     3)  MYSQL *mysql_init(MYSQL *mysql)
                                      初始化connection handler。
                              MYSQL *mysql = mysql_init(NULL);
                              if（mysql == NULL) // failed, call mysql_error

                      4) 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);
                                     与数据库建立连接
                          if (mysql_real_connect("127.0.0.1", "root", "hantele", "information_schema", 3306, NULL, 0) == NULL) // failed, call mysql_error

                       5) int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length);
                                  执行sql语句
                            #define sql_command "select table_schema, table_name from tables;"
                            if（mysql_real_query(mysql,  sql_command, sizeof(sql_command)) // failed, call mysql_error

                       6) my_ulonglong mysql_affected_rows(MYSQL *mysql)
                                   对于非查询类的sql语句（例如 insert，update， delete), 查询有多少行受到影响。                           
         

#include <mysql.h>
#include <iostream>
#include <sstream>
#include <map>
#include <string>
#include <list>
#include <cstdlib>
#include <unistd.h>
#include <cstring>
#include <errno.h>
#include <sys/statvfs.h> /* for statvfs */
using namespace std;


int GetFreeSpaceSize(const char *ib_data_dir)
{
    struct statvfs buf;
    if (statvfs(ib_data_dir, &buf) != 0)
    {
        cerr << "[ERROR] ############# Failed to get file system informations, error message is " << strerror(errno) << endl;
        return (-1);
    }
    typedef unsigned long long int guint64; // 64 bits
    guint64 free_size = ((guint64)buf.f_bsize * buf.f_bavail) / 1024/1024/1024;
    return (free_size);
}
int main(int argc, char *argv[])
{
    int keep_space = 500; // G
    const char *ib_data_dir = "/opt/mysql";
    const char * host = "127.0.0.1";
    const char * user = "root";
    const char * passwd = "hantele";
    const char * db = "information_schema";
    unsigned int port = 3306;
    const char *unix_socket = NULL;
    unsigned long client_flag = 0;
    MYSQL *mysql = NULL;
    MYSQL_RES *sql_results = NULL;
    MYSQL_ROW sql_row = NULL;
    my_ulonglong sql_rows_count = 0;
    unsigned int sql_field_count = 0;
    map<string, list<string> > mmap;

    try
    {
        if (mysql_library_init(0, NULL, NULL))
        {
            cerr << "[ERROR] ########## Could not initialize MYSQL library .!" << endl;
            throw (1);
        }

        mysql = mysql_init(NULL);
        if (mysql == NULL)
        {
            cerr << "[ERROR] ######### Can not initialize MYSQL connection, error message: %s .!" << mysql_error(mysql) << endl;
            throw (2);
        }

        if (!mysql_real_connect(mysql, host, user, passwd, db, port, unix_socket, client_flag))
        {
            cerr << "[ERROR] ######### Failed to connect to database, error  message: %s .!" << mysql_error(mysql) << endl;
            throw (3);
        }

        cout << "[INFO] ############ connect to database ok .!" << endl;

        char stmt_str[] = "select concat(table_schema, '.', table_name) from tables where table_name like \"%common_xdr%\";";
        if (mysql_real_query(mysql, stmt_str, sizeof(stmt_str)- 1))
        {
            cerr << "[ERROR] ########### Faield to execute statement, error message: %s .!" << mysql_error(mysql) << endl;
            throw (4);
        }

        sql_results = mysql_store_result(mysql);
        if (sql_results == NULL)
        {
            cerr << "[ERROR] ############# Failed to get results, error  message: %s .!" << mysql_error(mysql) << endl;
            throw (5);
        }

        // 处理结果集
        sql_rows_count = mysql_num_rows(sql_results);
        sql_row = mysql_fetch_row(sql_results);
#ifndef NDEBUG
        cout << "************************************************************************ " << endl;
#endif
        while (sql_row)
        {
            string table_name = sql_row[0];
#ifndef NDEBUG // 打印每一行记录
            cout << "[INFO] ############ table_name: " << table_name << endl;
#endif
            string::size_type timestamp_p = table_name.find_last_of('_');
            if (timestamp_p == string::npos)
            {

                cerr << "[ERROR] ############ Find a table name which is not a valid common_xdr table name ===> " << table_name << endl;
                continue;
            }
            string timestamp = table_name.substr(timestamp_p+1);
#ifndef NDEBUG
            cout << "[INFO] ############ timestamp " << timestamp << endl;
#endif
            list<string>& table_list = mmap[timestamp];
            table_list.push_back(table_name);

            sql_row = mysql_fetch_row(sql_results);
        }
#ifndef NDEBUG
        cout << "************************************************************************ " << endl;
#endif


        mysql_free_result(sql_results);



#ifndef NDEBUG
        cout << "######################################################################## " << endl;
        for (map<string, list<string> >::iterator it = mmap.begin(); it != mmap.end(); ++it)
        {
            cout << "timestamp: " << it->first << endl;
            list<string>& table_list = it->second;

            for (list<string>::iterator it_list = table_list.begin(); it_list != table_list.end(); ++it_list)
            {
                cout << "\t " << *it_list << endl;
            }
        }
        cout << "######################################################################## " << endl;
#endif

        // 删除最老的表，直到磁盘空间剩余足够用的空间
        map<string, list<string> >::iterator it_map;
        for (it_map = mmap.begin(); it_map != mmap.end(); ++it_map)
        {
            int space_size = GetFreeSpaceSize(ib_data_dir);
            if (space_size < keep_space)
            {
#ifndef NDEBUG
                cout << "[INFO] ############ Current space size: " << space_size << "G less then " << keep_space << "G, so delete tables in old day .!" << endl;
#endif

                // 删除表
                list<string>& table_list = it_map->second;
                for (list<string>::iterator it_list = table_list.begin(); it_list != table_list.end(); ++it_list)
                {
                    ostringstream del_str;
                    del_str << "drop table " << *it_list << ";";
                    string stmt_str = del_str.str();

#ifndef NDEBUG
                    cout << "[INFO] ############# try to drop table ========> " << *it_list << endl ;
#endif
                    if (mysql_real_query(mysql, stmt_str.c_str(), stmt_str.size()))
                    {
                        cerr << "[ERROR] ########### Faield to execute statement, error message: %s .!" << mysql_error(mysql) << endl;
                        throw (5);
                    }
#ifndef NDEBUG
                    cout << "[INFO] ############# drop table ========> " << *it_list << " ok.!" << endl ;
#endif
                }
            }
            else
            {
                cout << "[INFO] ########### There are " << space_size << "G in the partion, larger then " << keep_space << "G, so do nothing .!" << endl;
                exit(0);
            }
        }
    }
    catch (int exp_code)
    {
        switch (exp_code)
        {
            case 2:
            case 3:
            case 4:
            case 5:
            case 6:
                mysql_close(mysql);
            case 1:
                mysql_library_end();
                return 0;
                break;
        }
    }

    mysql_close(mysql);
    mysql_library_end();
    return 0;
}

                                  
                    

                     

To prepare and execute a statement, an application follows these steps:

1. Create a prepared statement handle with . To prepare the statement on the server, call and pass it a string containing the SQL statement.

2. If the statement will produce a result set, call to obtain the result set metadata. This metadata is itself in the form of result set, albeit a separate one from the one that contains the rows returned by the query. The metadata result set indicates how many columns are in the result and contains information about each column.

3. Set the values of any parameters using . All parameters must be set. Otherwise, statement execution returns an error or produces unexpected results.

4. Call mysql_stmt_execute() to execute the statement.

5. If the statement produces a result set, bind the data buffers to use for retrieving the row values by calling .

6. Fetch the data into the buffers row by row by calling repeatedly until no more rows are found.

7. Repeat steps 3 through 6 as necessary, by changing the parameter values and re-executing the statement.

When is called, the MySQL client/server protocol performs these actions:

• The server parses the statement and sends the okay status back to the client by assigning a statement ID. It also sends total number of parameters, a column count, and its metadata if it is a result set oriented statement. All syntax and semantics of the statement are checked by the server during this call.

• The client uses this statement ID for the further operations, so that the server can identify the statement from among its pool of statements.

When mysql_stmt_execute() is called, the MySQL client/server protocol performs these actions:

• The client uses the statement handle and sends the parameter data to the server.

• The server identifies the statement using the ID provided by the client, replaces the parameter markers with the newly supplied data, and executes the statement. If the statement produces a result set, the server sends the data back to the client. Otherwise, it sends an okay status and the number of rows changed, deleted, or inserted.

When is called, the MySQL client/server protocol performs these actions:

• The client reads the data from the current row of the result set and places it into the application data buffers by doing the necessary conversions. If the application buffer type is same as that of the field type returned from the server, the conversions are straightforward.

If an error occurs, you can get the statement error number, error message, and SQLSTATE code using , , and , respectively.

Prepared Statement Logging

For prepared statements that are executed with the and mysql_stmt_execute() C API functions, the server writes Prepare and Execute lines to the general query log so that you can tell when statements are prepared and executed.

Suppose that you prepare and execute a statement as follows:

1. Call to prepare the statement string "SELECT ?".

2. Call to bind the value 3 to the parameter in the prepared statement.

3. Call mysql_stmt_execute() to execute the prepared statement.

As a result of the preceding calls, the server writes the following lines to the general query log:

Prepare  [1] SELECT ?
Execute  [1] SELECT 3

Each Prepare and Execute line in the log is tagged with a [N] statement identifier so that you can keep track of which prepared statement is being logged. N is a positive integer. If there are multiple prepared statements active simultaneously for the client, N may be greater than 1. Each Execute lines shows a prepared statement after substitution of data values for ? parameters.

#include <mysql.h>
#include <iostream>
#include <sstream>
#include <map>
#include <string>
#include <list>
#include <cstdlib>
#include <unistd.h>
#include <cstring>
#include <errno.h>
#include <sys/statvfs.h> /* for statvfs */
using namespace std;


int GetFreeSpaceSize(const char *ib_data_dir)
{
    struct statvfs buf;
    if (statvfs(ib_data_dir, &buf) != 0)
    {
        cerr << "[ERROR] ############# Failed to get file system informations, error message is " << strerror(errno) << endl;
        return (-1);
    }
    typedef unsigned long long int guint64; // 64 bits
    guint64 free_size = ((guint64)buf.f_bsize * buf.f_bavail) / 1024/1024/1024;
    return (free_size);
}
int main(int argc, char *argv[])
{
    int keep_space = 500; // G
    const char *ib_data_dir = "/opt/mysql";
    const char * host = "127.0.0.1";
    const char * user = "root";
    const char * passwd = "hantele";
    const char * db = "test";
    unsigned int port = 3306;
    const char *unix_socket = NULL;
    unsigned long client_flag = 0;
    MYSQL *mysql = NULL;
    MYSQL_RES *sql_results = NULL;
    MYSQL_ROW sql_row = NULL;
    my_ulonglong sql_rows_count = 0;
    unsigned int sql_field_count = 0;
    map<string, list<string> > mmap;

    try
    {
        if (mysql_library_init(0, NULL, NULL))
        {
            cerr << "[ERROR] ########## Could not initialize MYSQL library .!" << endl;
            throw (1);
        }

        mysql = mysql_init(NULL);
        if (mysql == NULL)
        {
            cerr << "[ERROR] ######### Can not initialize MYSQL connection, error message: %s .!" << mysql_error(mysql) << endl;
            throw (2);
        }

        if (!mysql_real_connect(mysql, host, user, passwd, db, port, unix_socket, client_flag))
        {
            cerr << "[ERROR] ######### Failed to connect to database, error message: %s .!" << mysql_error(mysql) << endl;
            throw (3);
        }

        MYSQL_STMT * mysql_stmt = mysql_stmt_init(mysql);
        if (mysql_stmt == NULL)
        {
            cerr << "[ERROR] ########## Failed to call mysql_stmt_init, error message:" << mysql_error(mysql) << endl;
            throw (3);
        }
        cout << "[INFO] ############ connect to database ok .!" << endl;


        const char stmt_str[] = "insert into test_table values(?,?,?)";
        if (mysql_stmt_prepare(mysql_stmt, stmt_str, sizeof(stmt_str)) != 0)
        {
            cerr << "[ERROR] ########## failed to call mysql_stmt_prepare, error message: " << mysql_stmt_error(mysql_stmt) << endl;
            throw (3);
        }
        cout << "call mysql_stmt_prepare ok .!" << endl;

        /* MYSQL_BIND
         * This structure is used both for statment input(data values sent to the server) and output (result values returned from server):
         * To use a MYSQL_BIND structure, zero its contents to initialize it, then set its members appropriately.
         */


        MYSQL_BIND bind[3];
        memset(bind, 0, sizeof(bind));

        /*
         * The MYSQL_BIND structure contains the following memebers for use by application programs. For serveral of the members, the manner of use depends on whether the structure is used for input or output.
         *            Input Variable C Type        | buffer_type value        SQL Type of Destination Value
         *            signed char                        MYSQL_TYPE_TINY        TINYINT
         *            short int                        MYSQL_TYPE_SHORT    SMALLINT
         *            int                                MYSQL_TYPE_LONG        INT
         *            long long int                    MYSQL_TYPE_LONGLONG    BIGINT
         *            float                            MYSQL_TYPE_FLOAT    FLOAT
         *            double                            MYSQL_TYPE_DOUBLE    DOUBLE
         *            MYSQL_TIME                        MYSQL_TYPE_TIME        TIME
         *            MYSQL_TIME                        MYSQL_TYPE_DATE        DATE
         *            MYSQL_TIME                        MYSQL_TYPE_DATETIME    DATETIME
         *            MYSQL_TIME                        MYSQL_TYPE_TIMESTAMP    TIMESTAMP
         *            char []                            MYSQL_TYPE_STRING    TEXT,CHAR,VARCHAR
         *            char []                            MYSQL_TYPE_BLOB        BLOB,BINARY,VARBINARY
         *                                            MYSQL_TYPE_NULL        NULL
         */

        bind[0].buffer_type = MYSQL_TYPE_STRING;
        bind[1].buffer_type = MYSQL_TYPE_STRING;
        bind[2].buffer_type = MYSQL_TYPE_NULL;

        /* void *buffer
         * A pointer to ther buffer to be  used for data transfer.This is the address of a C language variable.
         * For input, buffer is a pointer to the  variable in which you store the data value for a statment parameter. When you call mysql_stmt_execute, MySQL use the value stored in the variable in place of the corresponding parameter marker in the statment
         * For output, buffer is a pointer to variable in which to return a result set column value. When you call mysql_stmt_fetch(), MySQL stores a column value from the current row of the result set in this variable. You can access the value when the call returns.
         */

#define CONTENTS "hello,world"
        bind[0].buffer = (void*)CONTENTS;
        bind[1].buffer = (void*)CONTENTS;
        bind[2].buffer = (void*)CONTENTS;


        /* unsigned long buffer_length
         * The actual size of *buffer in bytes.This indiactes the maximum amount of data that can be stored in the buffer. For character and binary C data, the buffer_length value specifies the length of *buffer when used with mysql_stmt_bind_param() to specify input values, or the maximum number of output data bytes that can be fetched into the buffer when used with mysql_stmt_bind_result().
         */
        bind[0].buffer_length = sizeof(CONTENTS);
        bind[1].buffer_length = sizeof(CONTENTS);
        bind[2].buffer_length = sizeof(CONTENTS);
        /* unsigned long *length
         * A pointer to an unsigned long variable that indicates the actual number of bytes data stored in *buffer. length is used for character or binary C data.
         * For input parameter data binding, set *length to indiacte the actual length of the parameter value stored in *buffer. This is used by mysql_stmt_execute().
         * For output value bingding, MySql sets *length when you call mysql_stmt_fetch(). The mysql_stmt_fetch() return value determines how to interpret the lenght:
         *        if the return value is 0, *length indicates the actual length of the parameter value.
         *        if the value is MYSQL_DATA_TRUNCATED, *length indicates the nontruncated length of the parameter value. In this case, the minimum of *length and buffer_length indicates the actual length of the value.
         *    length is ignored for numeric and temporal data types because the buffer_type value determines the length of the data value.
         */
        bind[0].length = &bind[0].buffer_length;
        bind[1].length = &bind[1].buffer_length;
        bind[2].length = &bind[2].buffer_length;

        /*
         * mysql_stmt_bind_param is used to bind input data for the parameter markers in the SQL statement that was passed to mysql_stmt_prepare().
         * It uses MYSQL_BIND structures to supply the data. bind is the address of an array of MYSQL_bIND structures. The client library expects the array to contain one element for each ? parameter marker that is present in the query.
         * Suppose that you prepare the following statement:
         * INSERT INTO mytbl VALUES(?,?,?)
         *
         * When you bind the parameters, the array of MYSQL_BIND structures must contain three elements, and can be declared like this:
         *  MYSQL_BIND bind[3];
         */
        /*
         *    For input, use MYSQL_BIND structures with mysql_stmt_bind_param() to bind parameter data values to buffers for use by mysql_stmt_execute().
         *
         *    For output, use MYSQL_BIND structures with mysql_stmt_bind_result() to bind buffers to result set columns,for use in fetching rows with mysql_stmt_fetch().
         */
        if (mysql_stmt_bind_param(mysql_stmt, bind) != 0)
        {
            cerr << "[ERROR] ########### failed to call mysql_stmt_bind_param, error message: " << mysql_stmt_error(mysql_stmt) << endl;
            throw (3);
        }


        /*
         * mysql_stmt_execute() executes the prepared query associated with the statement handle. The currentl bound parameter marker values are sent to server during this call, and the server replaces the markers with this newly supplied data.
         */
        if (mysql_stmt_execute(mysql_stmt) != 0)
        {
            cerr << "[ERROR] ####### failed to call mysql_stmt_execute, error message: " << mysql_stmt_error(mysql_stmt) << endl;
        }


        /*
         * For an update, delete, or insert, the number of changed, delete, or inserted rows can be found by call mysql_stmt_affected_rows(). It may be called immediately after executing a statement with mysql_stmt_execute.
         *
         */
        cout << "[INFO] ############## call mysql_stmt_execute ok, there are " << mysql_stmt_affected_rows(mysql_stmt) << " rows affacted .!