mysql c api（一）

参考资料：http://dev.mysql.com/doc/refman/5.5/en/c.html

 

近几天，工作中需要用到MYSQL数据库编程，开始学习linux下mysql c编程。刚把一个程序写得七七八八，写一下总结。

这几天看得最多的，就是MYSQL的官方文档了，一边看文档，一边写程序试着使用里面提到的接口，熟悉起来还算是比较快的。

 

先说说编程环境吧，操作系统：linux,编译软件：g++，库文件：mysql-connector-c-6.0.2-linux-glibc2.3-x86-64bit

1.下载安装库文件。因为开发的机子中没有安装mysql，系统没有mysql编程需要用到的库文件，只能自己下载，

在官网http://dev.mysql.com/downloads/connector/c/#downloads找到这个库文件，下载，解压，放到系统特定目录（自己随便放，知道路径就行了）。

2.编写程序。

3.进行编译。写完测试程序后，执行，g++ dminingmysql.cpp _public.cpp -o dminingmysql -I(mysql库文件存放路径/include) -L(mysql库文件存放目录/lib) -lmysqlclient -lz。

就可以运行查看结果了。

 

基本数据结构：

MYSQL：数据库连接句柄，不能COPY，只能使用mysql_init()获得

MYSQL_RES：用来保存结果集（result set）

MYSQL_FIELD：查看字段属性（字段名，长度，字段类型......）

 

基本方法：

MYSQL *mysql_init(MYSQL *mysql)：MYSQL数据结构使用前要使用该方法进行初始化。

 

Description

Allocates or initializes a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, it is freed when mysql_close() is called to close the connection.

Return Values

An initialized MYSQL* handle. NULL if there was insufficient memory to allocate a new object.

Errors

In case of insufficient memory, NULL is returned.

 

 

 

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)：初始完MYSQL后，建立连接。

 

 

Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host.mysql_real_connect() must complete successfully before you can execute any other API functions that require a valid MYSQL connection handle structure.The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect()you must call mysql_init() to initialize the MYSQL structure. 

 

 

Return Values

A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter.

 

 

 

unsigned long mysql_get_server_version(MYSQL *mysql)：获取服务器版本

Description

Returns the version number of the server as an integer.

Return Values

A number that represents the MySQL server version in this format:

major_version*10000 + minor_version *100 + sub_version

For example, 5.1.5 is returned as 50105.

This function is useful in client programs for quickly determining whether some version-specific server capability exists.

Errors

None.

 

 

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)：列出服务器的数据库

Description

Returns a result set consisting of database names on the server that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters “%” or “_”, or may be a NULL pointer to match all databases. Calling mysql_list_dbs() is similar to executing the query SHOW DATABASES [LIKEwild].

You must free the result set with mysql_free_result().

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

 

 

 

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)：获取查询的字段信息（长度，字段名，字段类型等等）

Description

Returns an array of all MYSQL_FIELD structures for a result set. Each structure provides the field definition for one column of the result set.

Return Values

An array of MYSQL_FIELD structures for all columns of a result set.

Errors

None.

 

 

 

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)：根据结果集获得一行记录信息。

Description

Retrieves the next row of a result set. 

 

 

 

int mysql_query(MYSQL *mysql, const char *stmt_str)：执行SQL语句

Description

Executes the SQL statement pointed to by the null-terminated string stmt_str. Normally, the string must consist of a single SQL statement and you should not add a terminating semicolon (“;”) or /g to the statement. If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons.

 

 

 

Return Values

Zero if the statement was successful. Nonzero if an error occurred.

 

 

 

MYSQL_RES *mysql_store_result(MYSQL *mysql)：保存执行完mysql_query（）的结果集

Description

After invoking mysql_query() or mysql_real_query(), you must call mysql_store_result() ormysql_use_result() for every statement that successfully produces a result set (SELECT, SHOW, DESCRIBE,EXPLAIN, CHECK TABLE, and so forth). You must also call mysql_free_result() after you are done with the result set.

You don't have to call mysql_store_result() or mysql_use_result() for other statements, but it does not do any harm or cause any notable performance degradation if you call mysql_store_result() in all cases. You can detect whether the statement has a result set by checking whether mysql_store_result() returns a nonzero value

 

 

 

void mysql_free_result(MYSQL_RES *result)：释放结果集空间

Description

Frees the memory allocated for a result set by mysql_store_result(), mysql_use_result(),mysql_list_dbs(), and so forth. When you are done with a result set, you must free the memory it uses by calling mysql_free_result().

Do not attempt to access a result set after freeing it.

Return Values

None.

Errors

None.

 

 

 

void mysql_close(MYSQL *mysql)：断开数据库连接，释放MYSQL结构体空间

Description

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to bymysql if the handle was allocated automatically by mysql_init() or mysql_connect().

Return Values

None.

Errors

None.

 

#include <stdio.h>#include "mysql.h"int main( int argc, char *argv[] ){ MYSQL *conn = NULL; MYSQL_RES *res = NULL; MYSQL_FIELD *field = NULL; MYSQL_ROW row; char user[10] = "xxx"; char password[10] = "xxx"; char server[20] = "xxx.xxx.xxx.xxx"; char database[20] = "xxx"; printf( "begin to connect database....../n" ); //初始化MYSQL结构体 conn = mysql_init(NULL); //建立连接 if ( mysql_real_connect(conn, server, user, password, database, 0, NULL, 0) == NULL ) { printf( "connect to database failed/n" ); printf( "error info: %s/n", mysql_error(conn) ); mysql_close(conn); return -1; } printf( "connect to database successfully/n" ); printf( "version: %ul/n", mysql_get_server_version(conn) ); printf( "version: %s/n", mysql_get_client_info() ); //列出数据库 printf( "show dbs: " ); res = mysql_list_dbs( conn, NULL ); if ( res == NULL ) { printf( "list tables failed/n" ); printf( "error info: %s/n", mysql_error(conn) ); return -1; } printf( "(row numbers: %d;", mysql_num_rows(res) ); printf( "field numbers: %d)/n", mysql_num_fields(res) ); for ( int i = 0; i < mysql_num_fields(res); i++ ) { field = mysql_fetch_fields(res); printf( "%s/t", field->name ); } printf( "/n" ); for ( int i = 0; i < mysql_num_rows(res); i++ ) { row = mysql_fetch_row(res); for ( int j = 0; j < mysql_num_fields(res); j++ ) { printf( "%s/t", row[j] ); } printf( "/n" ); } mysql_free_result( res ); printf( "/n" ); //列出test数据库中的表 printf( "show table in test:" ); res = mysql_list_tables( conn, NULL ); printf( "(row numbers: %d;", mysql_num_rows(res) ); printf( "field numbers: %d)/n", mysql_num_fields(res) ); for ( int i = 0; i < mysql_num_fields(res); i++ ) { field = mysql_fetch_fields(res); printf( "%10s/t", field->name ); } printf( "/n" ); for ( int i = 0; i < mysql_num_rows(res); i++ ) { row = mysql_fetch_row(res); for ( int j = 0; j < mysql_num_fields(res); j++ ) { printf( "%10s/t", row[j] ); } printf( "/n" ); } mysql_free_result( res ); //查询xxx表 if ( mysql_query( conn, "select * from xxx") != 0) { printf( "select station_info failed/n" ); return -1; } res = mysql_store_result( conn ); printf( "row numbers: %d/n", mysql_num_rows(res) ); printf( "field numbers: %d/n", mysql_num_fields(res) ); field = mysql_fetch_fields(res); for( int i = 0; i < 7; i++ ) { printf( "%10s/t", field[i].name ); } printf( "/n" ); for ( int i = 0; i < mysql_num_rows(res); i++ ) { row = mysql_fetch_row(res); for( int j = 0; j < 7; j++ ) { printf( "%10s/t", row[j] ); } printf( "/n" ); } mysql_free_result( res ); mysql_close( conn ); return 0;}