MySQL C API基本用法

一、常用API

（摘自MySQL 8.0 C API Developer Guide）

1. mysql_init()

Function Prototype

MYSQL *mysql_init(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. In a nonmultithreaded environment, mysql_init() invokes mysql_library_init() automatically as necessary. However, mysql_library_init() is not thread-safe in a multithreaded environment, and thus neither is mysql_init(). Before calling mysql_init(), either call mysql_library_init() prior to spawning any threads, or use a mutex to protect the mysql_library_init() call. This should be done prior to any other client library call.

Return Values

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

Errors

In case of insufficient memory, NULL is returned.

Example

MYSQL* conn = NULL;
conn = mysql_init(NULL);
if(!conn) {
    printf("mysql_init failed!\n");
}

MYSQL conn;
if(!mysql_init(&conn)) {
    printf("mysql_init failed!\n");
}

2. mysql_real_connect()

Function Prototype

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)

Description

mysql_real_connect() attempts to establish a connection to a MySQL server running on host. Client programs must successfully connect to a server before executing any other API functions that require a valid MYSQL connection handler structure.

Return Values

A MYSQL* connection handler 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 argument.

Errors

• CR_CONN_HOST_ERROR

  Failed to connect to the MySQL server.

• CR_CONNECTION_ERROR

  Failed to connect to the local MySQL server.

• CR_IPSOCK_ERROR

  Failed to create an IP socket.

• CR_OUT_OF_MEMORY

  Out of memory.

• CR_SOCKET_CREATE_ERROR

  Failed to create a Unix socket.

• CR_UNKNOWN_HOST

  Failed to find the IP address for the host name.

• CR_VERSION_ERROR

  A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version.

• CR_NAMEDPIPEOPEN_ERROR

  Failed to create a named pipe on Windows.

• CR_NAMEDPIPEWAIT_ERROR

  Failed to wait for a named pipe on Windows.

• CR_NAMEDPIPESETSTATE_ERROR

  Failed to get a pipe handler on Windows.

• CR_SERVER_LOST

  If connect_timeout > 0 and it took longer than connect_timeout seconds to connect to the server or if the server died while executing the init-command.

• CR_ALREADY_CONNECTED

  The MYSQL connection handler is already connected.

Example

MYSQL mysql;
mysql_init(&mysql);
mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, "your_prog_name");
if (!mysql_real_connect(&mysql, "host", "user", "passwd", "database", 0, NULL, 0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));
}

By using mysql_options() the MySQL client library reads the [client] and [your_prog_name] sections in the my.cnf file. This enables you to add options to the [your_prog_name] section to ensure that your program works, even if someone has set up MySQL in some nonstandard way.

3. mysql_query()

Function Prototype

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

Description

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. See Chapter 23, C API Multiple Statement Execution Support. 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.) If you want to know whether the statement returns a result set, you can use mysql_field_count() to check for this. See Section 7.22, “mysql_field_count()”.

Return Values

Zero for success. Nonzero if an error occurred.

Errors

• CR_COMMANDS_OUT_OF_SYNC

  Commands were executed in an improper order.

• CR_SERVER_GONE_ERROR

  The MySQL server has gone away.

• CR_SERVER_LOST

  The connection to the server was lost during the query.

• CR_UNKNOWN_ERROR

  An unknown error occurred.

4. mysql_use_result()

Function Prototype

MYSQL_RES *mysql_use_result(MYSQL *mysql)

Description

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.

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client allocates memory only for the current row and a communication buffer that may grow up to max_allowed_packet bytes.

On the other hand, you should not use mysql_use_result() for locking reads if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the result set for your next query. The C API gives the error Commands out of sync; you can’t run this command now if you forget to do this!

You may not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(),mysql_num_rows(), or mysql_affected_rows() with a result returned from mysql_use_result(), nor may you issue other queries until mysql_use_result() has finished. (However, after you have fetched all the rows, mysql_num_rows() accurately returns the number of rows fetched.)

You must call mysql_free_result() once you are done with the result set.

Return Values

A MYSQL_RES result structure. NULL if an error occurred.

Errors

mysql_use_result() resets mysql_error() and mysql_errno() if it succeeds.

• CR_COMMANDS_OUT_OF_SYNC

  Commands were executed in an improper order.

• CR_OUT_OF_MEMORY

  Out of memory.

• CR_SERVER_GONE_ERROR

  The MySQL server has gone away.

• CR_SERVER_LOST

  The connection to the server was lost during the query.

• CR_UNKNOWN_ERROR

  An unknown error occurred.

5. mysql_num_fields

Function Prototype

unsigned int mysql_num_fields(MYSQL_RES *result)

To pass a MYSQL* argument instead, use unsigned int mysql_field_count(MYSQL *mysql).

Description

Returns the number of columns in a result set.

You can get the number of columns either from a pointer to a result set or to a connection handler. You would use the connection handler if mysql_store_result() or mysql_use_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a nonempty result. This enables the client program to take proper action without knowing whether the query was a SELECT (or SELECTlike) statement. The example shown here illustrates how this may be done.

See Section 29.1, “Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success”.

Return Values

An unsigned integer representing the number of columns in a result set.

Errors

None.

Example

MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;
if (mysql_query(&mysql,query_string))
{
    // error
} 
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
 	if (result) // there are rows
 	{
 		num_fields = mysql_num_fields(result);
 		// retrieve rows, then call mysql_free_result(result)
 	}
 	else // mysql_store_result() returned nothing; should it have?
 	{
 		if (mysql_errno(&mysql))
 		{
 			fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
 		}
		else if (mysql_field_count(&mysql) == 0)
 		{
 			// query does not return data
 			// (it was not a SELECT)
 			num_rows = mysql_affected_rows(&mysql);
 		}
 	}
}

An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check whether mysql_field_count(&mysql) returns 0. This happens only if something went wrong.

6. mysql_fetch_fields()

Function Prototype

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.

For metadata-optional connections, this function returns NULL when the resultset_metadata system variable is set to NONE. To check whether a result set has metadata, use the mysql_result_metadata() function. For details about managing result set metadata transfer, see Chapter 27, C API Optional Result Set Metadata.

Return Values

An array of MYSQL_FIELD structures for all columns of a result set. NULL if the result set has no metadata.

Errors

None.

Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;
num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{
	printf("Field %u is %s\n", i, fields[i].name);
}

7. mysql_fetch_row()

Function Prototype

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

【Note】mysql_fetch_row() is a synchronous function. Its asynchronous counterpart is mysql_fetch_row_nonblocking(), for use by applications that require asynchronous communication with the server. See Chapter 12, C API Asynchronous Interface.

mysql_fetch_row() retrieves the next row of a result set:

• When used after mysql_store_result() or mysql_store_result_nonblocking(), mysql_fetch_row() returns NULL if there are no more rows to retrieve.

• When used after mysql_use_result(), mysql_fetch_row() returns NULL if there are no more rows to retrieve or an error occurred.

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

Return Values

A MYSQL_ROW structure for the next row, or NULL. The meaning of a NULL return depends on which function was called preceding mysql_fetch_row():

• When used after mysql_store_result() or mysql_store_result_nonblocking(), mysql_fetch_row() returns NULL if there are no more rows to retrieve.
• When used after mysql_use_result(), mysql_fetch_row() returns NULL if there are no more rows to retrieve or an error occurred. To determine whether an error occurred, check whether mysql_error() returns a nonempty string or mysql_errno() returns nonzero.

Errors

Errors are not reset between calls to mysql_fetch_row()

• CR_SERVER_LOST

  The connection to the server was lost during the query.

• CR_UNKNOWN_ERROR

  An unknown error occurred.

Example

MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
    unsigned long *lengths;
    lengths = mysql_fetch_lengths(result);
    for(i = 0; i < num_fields; i++)
    {
        printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
    }
    printf("\n");
}

二、 Example

以数据库mysql，数据表user为例：

/*************************************************************************
	> File Name: mysql_demo.c
	> Author: 
	> Mail: 
	> Created Time: Mon Dec 28 16:54:06 2020
 ************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

int main()
{
    MYSQL *conn = NULL;
    MYSQL_RES *res = NULL;
    MYSQL_FIELD *fields = NULL;
    MYSQL_ROW row;
    memset(&row, 0, sizeof(row));
    char sql_query[128] = {0};
    unsigned int num_fields = 0;
    unsigned int i = 0;
    
    do{
        conn = mysql_init(NULL);
        if(!conn) {
            printf("mysql_init failed!\n");
            break;
        }

        mysql_options(conn, MYSQL_SET_CHARSET_NAME, "utf8mb4");
        if (!mysql_real_connect(conn, "localhost", "user", "passwd", "mysql", 0, NULL, 0))		// 记得替换用户名及密码		
        {
            printf("Failed to connect to database: Error: %s\n", mysql_error(conn));
            break;
        }
        
        sprintf(sql_query, "select User, Host, plugin, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv from user");
        if(mysql_query(conn, sql_query)) {
            printf("mysql_query failed, Error: %s\n", mysql_error(conn));
            break;
        }
        
        res = mysql_use_result(conn);
        if(!res) {
            printf("mysql_use_result failed, Error: %s\n", mysql_error(conn));
            break;
        }
        
        num_fields = mysql_num_fields(res);
        fields = mysql_fetch_fields(res);
        if(!fields) {
            printf("the result set has no metadata.\n");
            break;
        }
        for(i = 0; i < num_fields; i++)
        {
            printf("%-25s", fields[i].name);
        }
        printf("\n");
        
        while ((row = mysql_fetch_row(res)))
        {
            unsigned long *lengths;
            lengths = mysql_fetch_lengths(res);
            for(i = 0; i < num_fields; i++)
            {
                printf("%-25s", row[i] ? row[i] : "NULL");
            }
            printf("\n");
        }
    }while(0);
    
    if(conn) {
        mysql_close(conn);
        conn = NULL;
    }
    
    return 0;
}

编译

gcc mysql_demo.c -o mysql_demo -lmysqlclient

运行

./mysql_demo

结果

User                     Host                     plugin                   Select_priv              Insert_priv              Update_priv              Delete_priv              Create_priv              Drop_priv                
root                     localhost                mysql_native_password    Y                        Y                        Y                        Y                        Y                        Y                        
mysql.session            localhost                mysql_native_password    N                        N                        N                        N                        N                        N                        
mysql.sys                localhost                mysql_native_password    N                        N                        N                        N                        N                        N                        
debian-sys-maint         localhost                mysql_native_password    Y                        Y                        Y                        Y                        Y                        Y 

下一篇：MySQL预处理语句MYSQL_STMT的使用（待整理）