一、认识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
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include /* 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 > 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& 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 >::iterator it = mmap.begin(); it != mmap.end(); ++it)
{
cout << "timestamp: " << it->first << endl;
list& table_list = it->second;
for (list::iterator it_list = table_list.begin(); it_list != table_list.end(); ++it_list)
{
cout << "\t " << *it_list << endl;
}
}
cout << "######################################################################## " << endl;
#endif
// 删除最老的表，直到磁盘空间剩余足够用的空间
map >::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& table_list = it_map->second;
for (list::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:
Create a prepared statement handle withmysql_stmt_init(). To prepare
the statement on the server, callmysql_stmt_prepare()and pass
it a string containing the SQL statement.
If the statement will produce a result set, callmysql_stmt_result_metadata()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.
Set the values of any parameters usingmysql_stmt_bind_param(). All
parameters must be set. Otherwise, statement execution returns
an error or produces unexpected results.
Callmysql_stmt_execute()to
execute the statement.
If the statement produces a result set, bind the data buffers
to use for retrieving the row values by callingmysql_stmt_bind_result().
Fetch the data into the buffers row by row by callingmysql_stmt_fetch()repeatedly
until no more rows are found.
Repeat steps 3 through 6 as necessary, by changing the
parameter values and re-executing the statement.
Whenmysql_stmt_prepare()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.
Whenmysql_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.
Whenmysql_stmt_fetch()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 usingmysql_stmt_errno(),mysql_stmt_error(), andmysql_stmt_sqlstate(),
respectively.
Prepared Statement Logging
For prepared statements that are executed with themysql_stmt_prepare()andmysql_stmt_execute()C API
functions, the server writesPrepareandExecutelines 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:
Callmysql_stmt_prepare()to
prepare the statement string"SELECT ?".
Callmysql_stmt_bind_param()to bind the value3to the parameter in the
prepared statement.
Callmysql_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
EachPrepareandExecuteline in the log is tagged with a[N]statement
identifier so that you can keep track of which prepared statement
is being logged.Nis a positive
integer. If there are multiple prepared statements active
simultaneously for the client,Nmay be
greater than 1. EachExecutelines shows a
prepared statement after substitution of data values for?parameters.
点击(此处)折叠或打开
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include /* 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 > 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 .!
441
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
