mysql常用函数原型讲解和用法

MYSQL API文档

---

• 23.8.7.36 mysql_init()

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 nonmulti-threaded environment, mysql_init() invokes mysql_library_init() automatically as necessary. However, mysql_library_init() is not thread-safe in a multi-threaded 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.

• 23.8.7.52 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)

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

For host, specify a value of NULL or the empty string ("").

For user, specify a value of NULL or the empty string.

For passwd, specify a value of NULL. (For the password, a value of the empty string in the mysql_real_connect() call cannot be overridden in an option file, because the empty string indicates explicitly that the MySQL account must have an empty password.)

For db, specify a value of NULL or the empty string.

For port, specify a value of 0.

For unix_socket, specify a value of NULL.

If no value is found in an option file for a parameter, its default value is used as indicated in the descriptions given earlier in this section.

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

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 library reads the [client] and [your_prog_name] sections in the my.cnf file which ensures that your program works, even if someone has set up MySQL in some nonstandard way.

Upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1 in versions of the API older than 5.0.3, or 0 in newer versions. A value of 1 for this flag indicates that if a statement cannot be performed because of a lost connection, to try reconnecting to the server before giving up. You can use the MYSQL_OPT_RECONNECT option to mysql_options() to control reconnection behavior.

• 23.8.7.51 mysql_query()

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 Section 23.8.17, “C API Support for Multiple Statement Execution”.

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 should return a result set, you can use mysql_field_count() to check for this. See Section 23.8.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.

• 23.8.7.49 mysql_options()

int mysql_options(MYSQL *mysql, enum mysql_option option, const void *arg)

Description

Can be used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options.

Return Values

Zero for success. Nonzero if you specify an unknown option.

Example

The following mysql_options() calls request the use of compression in the client/server protocol, cause options to be read from the [odbc] group of option files, and disable transaction autocommit mode:

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");
mysql_options(&mysql,MYSQL_INIT_COMMAND,"SET autocommit=0");
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));
}
This code requests that the client use the compressed client/server protocol and read the additional options from the odbc section in the my.cnf file.

• 23.8.7.61 mysql_set_character_set()

int mysql_set_character_set(MYSQL *mysql, const char *csname)

Description

This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. The connection collation becomes the default collation of the character set. This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

Return Values

Zero for success. Nonzero if an error occurred.

Example

MYSQL mysql;

mysql_init(&mysql);
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));
}

if (!mysql_set_character_set(&mysql, "utf8"))
{
    printf("New client character set: %s\n",
           mysql_character_set_name(&mysql));
}

• 23.8.7.51 mysql_query()

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 Section 23.8.17, “C API Support for Multiple Statement Execution”.

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 should return a result set, you can use mysql_field_count() to check for this. See Section 23.8.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.

• 23.8.7.71 mysql_use_result()

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.

When using the libmysqld embedded server, the memory benefits are essentially lost because memory usage incrementally increases with each row retrieved until mysql_free_result() is called.

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.

• 23.8.7.21 mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

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.

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. NULL if there are no more rows to retrieve or if an error occurred.

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");
}

• 23.8.7.25 mysql_free_result()

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

• 23.8.7.5 mysql_close()

void mysql_close(MYSQL *mysql)

Description

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

Return Values

None.

Errors

None.