关于mysql各种函数介绍

dagun

介绍Mysql函数介绍：
注释：英语不好的兄弟，对不起啦。
mysql_affected_rows

SYNOPSIS:

int mysql_affected_rows(MYSQL *mysql)

DESCRIPTION:

Retrieves the number of rows affected by the last UPDATE, DELETE or INSERT.

RETURN VALUE:

• An integer >0 indicating the number of changed/retrieved rows.
• Zero if no records matched the WHERE clause in an UPDATE or DELETE.
• -1 if the query returned an error, for example and attempt was made to add a duplicate primary key during an INSERT.

EXAMPLE:

mysql_query( &mysql, "INSERT INTO gl_transact (acctnbr, amount) VALUES (12345, 651.30)" );

if ( mysql_affected_rows( &mysql ) < 0 )
fprintf( stderr, "Attempted to add duplicate primary key\n" );

MySQL is optimized for the 'delete all records in a table' case. A side effect of this optimization is that MySQL will return zero for the number of rows affected in this situation. Doing a 'select count(*) from the_table' before deleting all records will give you a value for the number of rows affected, though this value may change between the SELECT and and DELETE since MySQL 3.20.X does not support table locking. This is fixed in version 3.21.X

mysql_close

SYNOPSIS:

void mysql_close(MYSQL *mysql);

DESCRIPTION:

Closes a previously opened connection.

mysql_close must be called after completing all operations performed via the MySQL connection. If not done, the thread created by mysql_connect will linger until the built-in timeout is reached. On a busy server this can quickly eat a lot of memory, although it should take very little CPU time.

If a connection is closed before a running query has completed, the query will continue until it attempts to return any part of the result set to the client. It will then die upon noticing that the connection is no longer active.

The default timeout is 30 seconds for an active query and 8 hours for an open connection.

RETURN VALUE:

None.

mysql_connect

SYNOPSIS:

MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)

DESCRIPTION:

Attempts to establish a connection to a MySQL database engine running on host. The value of host may be either a hostname or an IP address. The user parameter contains the user's MySQL login ID, and the passwd parameter contains the password for user. NOTE: Do not attempt to encrypt passwd before calling mysql_connect. Encryption is handled automatically by the client API.

• If host is NULL, 'localhost' is assumed.
• if user is NULL, 'current user' is assumed. Under Windows ODBC, the current user must be explicitly specified. Under Unix the current login ID is assumed.
• If password is NULL then only records in the user table without a password entry will be checked for a match. This allows the db-admin to setup the MySQL privilege system in such a way that a user gets different privileges depending on whether they have specified a password or not.

mysql_connect must complete successfully before any action is allowed to be performed on a database.

You may optionally specify the first argument of mysql_connect to be (MYSQL*) 0 This will force the C API to automatically alloc memory for the connection structure and free it on close. The downside of this approach is that you can't retrieve an error message from mysql_connect when you use this option.

RETURN VALUE:

• mysql if the connection was successful.
• NULL if unsuccessful.

EXAMPLE:

MYSQL *mysql;

if ( ( mysql = malloc( sizeof( MYSQL ) ) ) != NULL )
{
if ( ! ( mysql_connect( mysql, "pr_server", "jqpublic", "mypasswd" ) ) )
{
free( mysql );
exit( 1 );
}
}
free( mysql );

mysql_create_db

SYNOPSIS:

int mysql_create_db(MYSQL *mysql, const char *db);

DESCRIPTION:

Creates the database named in db on the machine pointed to by mysql. The MySQL connection must have been made with a user ID that has create privileges. (Refer to chapter 5 for more details on user privileges.)

RETURN VALUE:

• Zero if the database was successfully created.
• Non-zero if an error occurred. The error message may be retrieved by calling mysql_error.

mysql_data_seek

SYNOPSIS:

void mysql_data_seek(MYSQL_RES *res, uint offset);

DESCRIPTION:

Seeks to an arbitrary row in a query result set. May not be used in conjunction with mysql_use_result

RETURN VALUE:

None

mysql_drop_db

SYNOPSIS:

int mysql_drop_db(MYSQL *mysql, const char *db);

DESCRIPTION:

Drop the database named in db from the machine pointed to by mysql. The connection must have been made with a user ID that has drop privileges for the specified database. (Refer to chapter 6 for more details on user privileges.)

RETURN VALUE:

• Zero if the database was successfully dropped.
• Non-zero if an error occurred. The error message may be retrieved by calling mysql_error.

mysql_eof

SYNOPSIS:

int mysql_eof(MYSQL_RES *)

DESCRIPTION:

Returns a value != 0 if the last call to mysql_fetch_row returned nothing because the end of the result set has been reached.
mysql_error

SYNOPSIS:

char *mysql_error(MYSQL *mysql)

DESCRIPTION:

The error message, if any, returned by the last MySQL function call on connection mysql.

An empty string will be returned if no error occurred.

mysql_fetch_field

SYNOPSIS:

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle);

DESCRIPTION:

Find out what type a table field is.

mysql_fetch_lengths

SYNOPSIS:

unsigned int *mysql_fetch_lengths(MYSQL_RES *mysql)

DESCRIPTION:

Returns the length of all columns in a query result set. If you plan on retrieving data that contains a \0 you must use this function to get the actual length of the field value.

mysql_fetch_row

SYNOPSIS:

MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql);

DESCRIPTION:

Fetch the 'next' row in the query result. Will return a NULL pointer when all rows have been retrieved.

mysql_field_seek

SYNOPSIS:

void mysql_field_seek(MYSQL_RES *result, int field)

DESCRIPTION:

Put the column cursor on column number field, which should be within the range from 0 to mysql_num_fields(MYSQL_RES*)-1
mysql_free_result

SYNOPSIS:

void mysql_free_result(MYSQL_RES *result);

DESCRIPTION:

Free memory used to store a query result. Should be called whenever you have finished using the results of a mysql_store_result() call

mysql_get_client_info

SYNOPSIS:

char *mysql_get_client_info(void);

DESCRIPTION:

This function simply returns a string containing version information for the client library currently in use.

mysql_get_host_info

SYNOPSIS:

char *mysql_get_host_info(MYSQL *mysql);

DESCRIPTION:

Returns name of host (same as the host argument to mysql_connect).

mysql_get_proto_info

SYNOPSIS:

int mysql_get_proto_info(MYSQL *mysql);

DESCRIPTION:

Get protocol version used by connection. MySQL implements dynamic protocols based on client capabilities. In version 3.20.X this doesn't really do anything, but in future versions it will for example allow one client to connect using the current protocol, while another connects using encryption and compression.

mysql_get_server_info

SYNOPSIS:

char *mysql_get_server_info(MYSQL *mysql);

DESCRIPTION:

Returns the version number of the server.

mysql_insert_id

SYNOPSIS:

int mysql_insert_id(MYSQL *mysql)

DESCRIPTION:

Returns ID generated for AUTO_INCREMENT field on result variable 'res'.
mysql_list_dbs

SYNOPSIS:

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild);

DESCRIPTION:

Provided to ease porting of mSQL applications.

Similar to doing 'SHOW databases [ LIKE wild-card ]' as a query.

mysql_list_fields

SYNOPSIS:

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild);

DESCRIPTION:

Provided to ease porting of mSQL applications.

Similar to doing 'SHOW fields [FROM table] [FROM database] [LIKE wild-card]' in a query.

mysql_list_processes

SYNOPSIS:

MYSQL_RES *mysql_list_processes(MYSQL *mysql);

DESCRIPTION:

Get a list of the thread currently running on the MySQL database engine. You must have process privileges.

mysql_list_tables

SYNOPSIS:

MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild);

DESCRIPTION:

Provided to ease porting of mSQL applications.

Similar to doing 'SHOW tables [FROM database]' as a query.


mysql_num_fields

SYNOPSIS:

int mysql_num_fields(MYSQL_RES *result);

DESCRIPTION:

This macro returns the number of columns (fields) in a query result.

EXAMPLE:

MYSQL mysql;
MYSQL_RES *result;
int fields;

if ( mysql_query( &mysql, "SELECT * FROM emp_master" ) == 0 )
{
result = mysql_store_result( &mysql );
if ( result != NULL )
{
fields = mysql_num_fields( result );
printf( "Retrieved %u fields\n", fields );
}
else
printf( "Query failed\n" );
}
else
abort();


SEE ALSO:

mysql_list_dbs, mysql_list_fields, mysql_list_processes, mysql_list_tables, mysql_store_result, mysql_use_result

mysql_num_rows

SYNOPSIS:

int mysql_num_rows(MYSQL_RES *result);

DESCRIPTION:

This macro returns the number of rows returned by the last mysql_list_dbs, mysql_list_fields, mysql_list_processes, mysql_list_tables or mysql_store_result.

EXAMPLE:

MYSQL mysql;
MYSQL_RES *result;
int rows;

if ( mysql_query( &mysql, "SELECT * FROM emp_master" ) == 0 )
{
result = mysql_store_result( &mysql );
if ( result != NULL )
{
rows = mysql_num_rows( result );
printf( "Retrieved %u rows\n", rows );
}
else
printf( "Query failed\n" );
}
else
abort();


SEE ALSO:

mysql_list_dbs, mysql_list_fields, mysql_list_processes, mysql_list_tables, mysql_store_result, mysql_use_result

mysql_query

SYNOPSIS:

int mysql_query(MYSQL *mysql, const char *query);

DESCRIPTION:

Executes the SQL query pointed to by query on the database pointed to by mysql. This function will return zero if the query executed properly. A non-zero result indicates an error. A call to mysql_error will retrieve the error message.

Calling mysql_num_rows will give you the number of rows returned by the query.

If you have an AUTO_INCREMENT field in the table being updated and you are doing an INSERT, you can find the newly assigned field value by checking mysql_insert_id.

mysql_real_query

SYNOPSIS:

int mysql_real_query(MYSQL *mysql, const char *query, uint length);

DESCRIPTION:

This function is called by mysql_query after it does a strlen function call to calculate the length of the query string. It could be used if your program allocates a fixed buffer for the query string.

You'll have to use this function if you have data with un-escaped \0 values.

mysql_reload

SYNOPSIS:

int mysql_reload(MYSQL *mysql);

DESCRIPTION:

Have the MySQL database engine reload the user permissions table, flush all caches and close all open tables not in use. This should be done before running isamchk on any table.

Requires user to have reload privileges.

mysql_select_db

SYNOPSIS:

int mysql_select_db(MYSQL *mysql, const char *db);

DESCRIPTION:

Attempt to connect to the database pointed to by db, on the machine pointed to by mysql. The MySQL database engine on the server will use the login and password contained in mysql to authenticate the connection.

A successful call to mysql_connect is necessary before mysql_select_db can be used.

In general mysql_select_db must be called successfully before attempting to query a database. The exceptions are queries such as the following.

SHOW DATABASES like 'A%';

SELECT 1+1; # SELECT without using tables.

mysql_shutdown

SYNOPSIS:

int mysql_shutdown(MYSQL *mysql);

DESCRIPTION:

Shut down a MySQL database engine. User must have shutdown privileges.

mysql_stat

SYNOPSIS:

char *mysql_stat(MYSQL *mysql);

DESCRIPTION:

Returns the info similar to 'mysqladmin version' as a character string. This includes uptime in seconds, running threads, questions, reloads and open tables information. This is essentially the same as the mysqladmin programs stat option.

mysql_store_result

SYNOPSIS:

MYSQL_RES *mysql_store_result(MYSQL *mysql);

DESCRIPTION:

Reads the result to the client. You must use this or mysql_use_result() to get the result from the server. You must always use mysql_store_result() or mysql_use_result() after you have executed a successful query.

mysql_store_result() returns NULL on error or if the statement didn't return any data. You can check for errors with:


if (!(result=mysql_store_result(&mysql)) && mysql_num_fields(&mysql))
fputs(mysql_error(&mysql),stderr);


A call to mysql_free_result() must be made when you're done to free memory.

mysql_use_result

SYNOPSIS:

MYSQL_RES *mysql_use_result(MYSQL *mysql);

DESCRIPTION:

The same as mysql_store_result(), except that the result is fetched dynamically from the server for each 'mysql_fetch_row()'. This should not be used on interactive applications since it ties up the server. This helps to hold down the memory usage on the client side.