MySQL Connector C

最新推荐文章于 2023-09-14 09:57:30 发布

lazying_bird

最新推荐文章于 2023-09-14 09:57:30 发布

阅读量906

点赞数

分类专栏： SQL C/C++ 文章标签： mysql

本文链接：https://blog.csdn.net/a_flying_bird/article/details/50633729

版权

C/C++ 同时被 2 个专栏收录

73 篇文章 0 订阅

订阅专栏

SQL

18 篇文章 0 订阅

订阅专栏

For English readers: The blue font is written corresonding the Chinese content, and all the Chinese content can be skipped.

本文讨论MySQL Connector C的使用方法，版本是6.1。

This blog will describe how to connected to MySQL in C/C++.

准备工作 Preparation

采用了前文已经创建的数据库：

We assume the reader has created the database in:

http://blog.csdn.net/a_flying_bird/article/details/50543266

参考资料 Reference

refman-5.7-en.pdf： 23.8 MySQL C API
MySQL Connector/C Developer Guide

部分要点拷贝如下。

For simple, I copy some content here.

Compiling MySQL Clients on Microsoft Windows

To specify header and library file locations, use the facilities provided by your development environment.

To build C API clients on Windows, you must link in the C client library, as well as the Windows ws2_32 sockets library and Secur32 security library.

On Windows, you can link your code with either the dynamic or static C client library. The static library is named mysqlclient.lib and the dynamic library is named libmysql.dll. In addition, the libmysql.lib static import library is needed for using the dynamic library.

If you link with the static library, failure can occur unless these conditions are satisfied:

The client application must be compiled with the same version of Visual Studio used to compile the library.
The client application should link the C runtime statically by using the /MT compiler option.

If the client application is built in debug mode and uses the static debug C runtime (/MTd compiler option), it can link to the mysqlclient.lib static library if that library was built using the same option. If the client application uses the dynamic C runtime (/MD option, or /MDd option in debug mode), it must be linked to the libmysql.dll dynamic library. It cannot link to the static client library.

VC6

后面的代码是以VS2010+MySql5.7为例。但在VC6下面编译错误。

All the code is based on VS2010 + MySQL 5.7. – The code will be compiled error in VC6.

VC6下面可以用MySql5.0的版本，如

So in VC6, MySQL 5.0 can be used, for example,

mysql-5.0.41-win32

include: C:\PROGRAM FILES (X86)\MYSQL\MYSQL SERVER 5.0\INCLUDE
lib: C:\PROGRAM FILES (X86)\MYSQL\MYSQL SERVER 5.0\LIB\DEBUG
bin: C:\PROGRAM FILES (X86)\MYSQL\MYSQL SERVER 5.0\BIN

如果运行时提示dll找不到，直接把libmySQL.dll拷贝到exe目录下。

If there is a prompt to say “dll not found” while running, please copy libmySQL.dll to the same directory exe file.

另外，VC6下面编译项MDd的时候，lib库选用libmysql.lib：——用静态库mysqlclient.lib会有链接错误，如上面MySql文档所述。

Further more， while compiling in VC6 with MDd option, please select libmysql.lib, don’t use the static library mysqlclient.lib as described above.

#pragma comment(lib, "libmysql.lib")

VS2010示例代码 Demo codes in VS2010

mysql-installer-community-5.7.10.0.msi

这里创建的是一个控制台应用程序，只有一个main.c。

Here’s a console application, contains only a main.c file.

#include <stdio.h>
#include <WinSock2.h>
#include <mysql.h>

#pragma comment(lib, "mysqlclient.lib")

int main()
{   
    MYSQL *pConn = NULL;
    const char *db_name = "menagerie";
    MYSQL_RES *result = NULL;
    MYSQL_ROW row;

    pConn = mysql_init(NULL);
    if (!mysql_real_connect(pConn, "localhost", "admin", "admin", db_name, 0, NULL, 0)) {
        printf("Cannot connect to db: %s\n", mysql_error(pConn));
        return 1;
    }

    if (mysql_query(pConn, "select * from pet")) {
        printf("query failed: %s\n", mysql_error(pConn));
        mysql_close(pConn);
        return 2;
    }

    result = mysql_store_result(pConn);
    while (row = mysql_fetch_row(result)) {
        printf("%s %s %s %s %s %s\n", row[0], row[1], row[2], row[3], row[4], row[5]);
    }

    mysql_free_result(result);
    mysql_close(pConn);

    return 0;
}

在project的属性窗口中设置：C/C++ -> 代码生成 -> 运行库：多线程/MT(或/MTd).

In the project’s setting dialog: C/C++ –> Code Generation –> Run Library: Multi-Thread /MT (or /MTd).

控制台应用程序如果包括多个文件，则在每个用到MySql数据结构的文件，都添加下面这个头文件。——因为是分别编译每个源代码文件，所以必须保证每个源代码文件独立且有socket的定义。

If the console application contains multiple files, then every file that uses the MySQL structure or APIs should add a header as below. It’s because every file is compiled seperately, so every cpp source file must have its own socket definition.

#include <WinSock2.h>

运行结果 Running Result

Fluffy Harold cat f 1993-02-04 0000-00-00
Claws Gwen cat m 1994-03-17 0000-00-00
Buffy Harold dog f 1989-05-13 0000-00-00
Fang Benny dog m 1990-08-27 0000-00-00
Bowser Diane dog m 1989-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 0000-00-00
Whistler Gwen bird (null) 1997-12-09 0000-00-00
Slim Benny snake m 1996-04-29 (null)
Puffball Diane hamster f 1999-03-30 (null)
请按任意键继续. . .

语言说明 Language Reference

本节内容源于pdf的23.8节。

All the content is copied from 23.8 of pdf.

数据结构 Data Structure

MYSQL

This structure represents a handle to one database connection. It is used for almost all MySQL functions.

表示一个数据库连接，可以看到，大多数的函数都要用到这个参数。要点：

Represents a database connection. Every MySQL application will use this parameter. The key points are:

初始化MYSQL对象：首先要用mysql_init()初始化一个MYSQL对象，之后才能打开数据库。Initialize: First, use mysql_init() to initialize a MYSQL object, then open the database.
打开数据库：用mysql_real_connect()建立到一个数据库的连接 Open Database: Using mysql_real_connect() to create a connection to the specified database.
关闭数据库：用mysql_close()关闭一个数据库连接 Close Database: Using mysql_close() to close a connection.

MYSQL_RES

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.

表示一次查询结果，包括多个row，即下面说的MYSQL_ROW数据结构。许多mysql API都会返回MYSQL_RES对象。比如上面例子中的mysql_query()。

MYSQL_ROW

This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row().

要把MYSQL_RES中的每一项取出来，就要用到MYSQL_ROW，即数据库（表单）中的一行记录。数据类型的定义：

typedef char **MYSQL_ROW;       /* return data as array of strings */

可以看到，MYSQL_ROW建模为一个字符串数组，每个元素表示数据库中的一个column。正因为这种原因，如果返回的数据中包括了binary数据，就要慎用MYSQL_ROW了。

如上面例子给出的，mysql_fetch_row()是用来遍历MYSQL_RES中的每一行，该函数原型：

MYSQL_ROW   STDCALL mysql_fetch_row(MYSQL_RES *result);

API

mysql_init

函数原型如下：

MYSQL *     STDCALL mysql_init(MYSQL *mysql);

pdf中的说明：

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.

这个init函数是分配、初始化一个MYSQL对象。如果入参是NULL，就是会返回一个新的MYSQL对象；否则，就是把入参初始化一遍，再返回该入参（地址）。

创建的MYSQL对象，需要用mysql_close()是否它，并关闭其对应的数据库连接。

mysql_init()的返回值是下一步mysql_real_connect()的入参。

关于多线程部分，直接参考pdf文档，此处略。

mysql_close()

原型：

void mysql_close(MYSQL *mysql)

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_\real_connect().

关闭数据库连接、释放资源。

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)

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.

和数据库建立连接，只有连接成功了，才能调用mysql_query()等函数。

函数的各个参数不言自明，仅几点说明，详细内容参考pdf：

host: 是主机名称或IP地址。如果NULL或localhost，就表示本机。
db：数据库的名称，如果NULL，则表示缺省的数据库。

mysql_query()

原型：

int mysql_query(MYSQL *mysql, const char *stmt_str)

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

执行一条SQL（查询）语句。如果这个字符串对应的是一条SQL语句，则最后不需要加分号。如果对应的是多条语句，就要用分号分隔。

mysql_query() cannot be used for statements that contain binary data; you must use mysql_real_query() instead. (Binary data may contain the “\0” character, which mysql_query() interprets as the end of the statement string.)

mysql_query()不能用来查询包括二进制的数据，在前面讲述数据结构MYSQL_ROW的时候提到。——要查询二进制的数据，就用mysql_real_query()。

If you want to know whether the statement returns a result set, you can use mysql_field_count() to check for this.

mysql_field_count() 确定本次查询是否返回了结果。

mysql_store_result()

原型：

MYSQL_RES *mysql_store_result(MYSQL *mysql)

After invoking mysql_query() or mysql_real_query(), you must call mysql_store_result() or mysql_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.

在调用query命令之后，需要用mysql_store_result() 或者mysql_use_result()产生一个结果（集合）。如前面提到的，接下来再fetch每一个row。数据处理完之后，要调用mysql_free_result()释放资源。

mysql_free_result()

原型：

void mysql_free_result(MYSQL_RES *result)

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().

如前所述，这个API用来释放资源。

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

自然释放了之后，就不要再访问了。

mysql_fetch_row()

原型：

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Retrieves the next row of a result set. When used after mysql_store_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve. When used after mysql_use_result(), mysql_\fetch_row() returns NULL when there are no more rows to retrieve or if an error occurred.

这个函数类似于迭代器，或其他数据结构中的next()方法，遍历每一条数据。如果没有可用的数据了，就返回NULL（，退出循环）。

The number of values in the row is given by mysql_num_fields(result). If row holds the return value from a call to mysql_fetch_row(), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.

在取到一个row之后，如何知道有多少column（或称field）呢？就调用mysql_num_fileds()，其入参是MYSQL_RES对象。有了这个column数值之后，就可以遍历每一列（或每个字段）的数据了，即row[0], …。

The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths(). Empty fields and fields containing NULL both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL, the field is NULL; otherwise, the field is empty.

这里讨论的是返回结果中，或确切地说，当前要处理的row中，每个field（或称column）的长度。前面说过，每个field或column都对应一个字符串（参考MYSQL_ROW的说明），这里的length即为每个字符串的长度。一个row包括有多个field，所以mysql_fetch_lengths()返回的也是一个长度（整型）数组。

对于空的field，长度定义为0.

关于这几个函数，参考下面的代码示例。

功能增强后的代码

前面讨论了mysql_fetch_lengths()和mysql_num_fileds()，所以我们在前面的代码中增加这两个函数的调用示例。

代码

#include <stdio.h>
#include <WinSock2.h>
#include <mysql.h>

#pragma comment(lib, "mysqlclient.lib")

int main()
{   
    MYSQL *pConn = NULL;
    const char *db_name = "menagerie";
    MYSQL_RES *result = NULL;
    MYSQL_ROW row;
    unsigned int num_field = 0;
    unsigned long *field_lengths = NULL;
    unsigned int i;

    pConn = mysql_init(NULL);
    if (!mysql_real_connect(pConn, "localhost", "admin", "admin", db_name, 0, NULL, 0)) {
        printf("Cannot connect to db: %s\n", mysql_error(pConn));
        return 1;
    }

    if (mysql_query(pConn, "select * from pet")) {
        printf("query failed: %s\n", mysql_error(pConn));
        mysql_close(pConn);
        return 2;
    }

    result = mysql_store_result(pConn);
    num_field = mysql_num_fields(result);
    while (row = mysql_fetch_row(result)) {
        field_lengths = mysql_fetch_lengths(result);
        for (i = 0; i < num_field; i++) {
            printf("%s(%d) ", row[i], field_lengths[i]);
        }
        printf("\n");
    }

    mysql_free_result(result);
    mysql_close(pConn);

    return 0;
}

运行结果

Fluffy(6) Harold(6) cat(3) f(1) 1993-02-04(10) 0000-00-00(10)
Claws(5) Gwen(4) cat(3) m(1) 1994-03-17(10) 0000-00-00(10)
Buffy(5) Harold(6) dog(3) f(1) 1989-05-13(10) 0000-00-00(10)
Fang(4) Benny(5) dog(3) m(1) 1990-08-27(10) 0000-00-00(10)
Bowser(6) Diane(5) dog(3) m(1) 1989-08-31(10) 1995-07-29(10)
Chirpy(6) Gwen(4) bird(4) f(1) 1998-09-11(10) 0000-00-00(10)
Whistler(8) Gwen(4) bird(4) (null)(0) 1997-12-09(10) 0000-00-00(10)
Slim(4) Benny(5) snake(5) m(1) 1996-04-29(10) (null)(0)
Puffball(8) Diane(5) hamster(7) f(1) 1999-03-30(10) (null)(0)
请按任意键继续. . .