| Functions not covered in earlier chapters are described in the following sections.
ANALYSE([column[, memory]]) | |
This function returns an analysis of a results table from a SELECT statement. The \G at the end of the following statement provides a vertical output. Use this function only as part of a PROCEDURE clause. The default values are 256 per column and 8,192 for memory. SELECT col1 FROM table1 PROCEDURE ANALYSE( ) \G *************************** 1. row *************************** Field_name: table1.col1 Min_value: 1 Max_value: 82 Min_length: 1 Max_length: 2 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 42.8841 Std: 24.7600 Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL
You can specify the maximum number of elements to analyze for each column in the optional first argument, and the maximum amount of memory that may be used in the optional second argument.
BENCHMARK(number, expression) | |
Use this function to evaluate the performance of a MySQL server. The expression given as the second argument of the function is repeated for the number of times given in the first argument. The results are always 0. It's the processing time reported that is meaningful. This function is meant to be used from within the mysql client. SELECT BENCHMARK(1000000,PI( )); +-------------------------+ | BENCHMARK(1000000,PI( )) | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.04 sec) SELECT BENCHMARK(1000000,PI( )); +-------------------------+ | BENCHMARK(1000000,PI( )) | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.02 sec)
In these two examples, the first SQL statement took .04 seconds and the second one took .02 seconds to determine the value of pi 1 million times. The difference is due to a variety of factors unrelated to the SQL statement. The function is useful when run several times to determine an average time before making changes to a server and then afterward to see the effect of the changes. For a much better performance indicator, try the Perl scripts provided in the sql-bench directory of the MySQL source files. For more information on these benchmark scripts, see High Performance MySQL (O'Reilly) by Jeremy D. Zawodny and Derek J. Balling.
This function returns the number of bits set in a given number, an integer that is treated as a binary number. SELECT BIT_COUNT(10), BIT_COUNT(11); +---------------+---------------+ | BIT_COUNT(10) | BIT_COUNT(11) | +---------------+---------------+ | 2 | 3 | +---------------+---------------+
Use this function to convert a value from one datatype to another. This function is available as of Version 4.0.2 of MySQL. For the first syntax shown, the datatype given as the second argument can be BINARY, CHAR, DATE, DATETIME, SIGNED [INTEGER], TIME, or UNSIGNED [INTEGER]. BINARY converts a string to a binary string. CHAR conversion is available as of Version 4.0.6 of MySQL. This function is synonymous with CONVERT(), although the syntax is different.
This function returns the character set used by a given string. This function is available as of Version 4.1.0 of MySQL. SELECT CHARSET('test'); +-----------------+ | CHARSET('test') | +-----------------+ | latin1 | +-----------------+
COALESCE(column[, . . . ]) | |
This function returns the leftmost non-NULL string or column in a comma-separated list. If all elements are NULL, the function returns NULL. SELECT COALESCE(col1, col2, col3) FROM table1;
COERCIBILITY(string)
This function returns the collation coercibility value of a given string. A value of 0 is returned for explicit collation, 1 for no collation, 2 for implicit collation, and 3 for coercible. This function is available as of Version 4.1.1 of MySQL. SELECT COERCIBILITY('test'); +----------------------+ | COERCIBILITY('test') | +----------------------+ | 3 | +----------------------+
This function returns the collation for the character set of a given string. This function is available as of Version 4.1.0 of MySQL. SELECT COLLATION('test'); +-------------------+ | COLLATION('test') | +-------------------+ | latin1_swedish_ci | +-------------------+
This function returns the MySQL connection identification number for the MySQL session. There are no arguments. Connection identifiers are unique. SELECT CONNECTION_ID( ); +-----------------+ | CONNECTION_ID( ) | +-----------------+ | 11266 | +-----------------+
CONVERT(expression, type) CONVERT(expression USING transcoding) | |
Use this function to convert a value from one datatype to another. This function is available as of Version 4.0.2 of MySQL. For the first syntax shown, the datatype given as the second argument can be BINARY, CHAR, DATE, DATETIME, SIGNED [INTEGER], TIME, or UNSIGNED [INTEGER]. BINARY converts a string to a binary string. CHAR conversion is available as of Version 4.0.6 of MySQL. The second syntax shown allows for transcoding (character set) names to be given instead of datatypes. This function is synonymous with CAST( ), although the syntax is different.
This function returns the username and the hostname that were given by the user for the current MySQL connection. There are no arguments for the function. This function may not always return the same results as USER( ). SELECT CURRENT_USER( ), USER( ); +----------------+------------------+ | CURRENT_USER( ) | USER( ) | +----------------+------------------+ | ''@localhost | russel@localhost | +----------------+------------------+
In this example, the user logged in to the mysql client with the username russel (missing one l in the name), but because there isn't an account for that user, the client logged in with the anonymous (i.e., " ) account.
This function returns the name of the database currently in use for the session. There are no arguments. If no database has been set to default yet, it returns NULL; prior to Version 4.1.1 of MySQL, it returns an empty string. SELECT DATABASE( ); +------------+ | DATABASE( ) | +------------+ | bookstore | +------------+
Use this function in conjunction with the SQL_CALC_FOUND_ROWS option of a SELECT statement to determine the number of rows an SQL statement using a LIMIT clause would have generated without the limitation. There are no arguments for the function. It's available as of Version 4 of MySQL. SELECT SQL_CALC_FOUND_ROWS * FROM table1 LIMIT 5; ... SELECT FOUND_ROWS( );
GET_LOCK(string, seconds) | |
This function attempts to get a lock on the name given in the first argument. The number of seconds to attempt the lock is given in the second argument. If successful, 1 is returned. If the function is unsuccessful due to the attempt timing out, 0 is returned. If the lock fails due to an error of any kind, NULL is returned. The function RELEASE_LOCK( ), may be used to release a lock. It's also released when the same client issues another GET_LOCK( ) or when the client's connection is terminated. SELECT GET_LOCK('my_lock', 10); +-------------------------+ | GET_LOCK('my_lock', 10) | +-------------------------+ | 1 | +-------------------------+
INTERVAL(search, column, . . . ) | |
This function returns the index of the value of the string or column given in the first argument for where it would be located in the list of strings or columns given in subsequent arguments, which must be listed from lowest to highest. If the search element would be located before the first element, 0 is returned. All arguments are treated as integers. SELECT INTERVAL('4', '1','3','5','7','9'); +------------------------------------+ | INTERVAL('4', '1','3','5','7','9') | +------------------------------------+ | 2 | +------------------------------------+
In this example, the value of 4 would fall after the second position, after the 3 in the list.
Use this function to determine if the name of the lock given in parentheses is free and available as a lock name. It returns 1 if the lock name is free; 0 if it's not free. It returns NULL if there is an error. This function is available as of Version 4.0.2 of MySQL. Locks are created by GET_LOCK( ). SELECT IS_FREE_LOCK('my_lock'); +-------------------------+ | IS_FREE_LOCK('my_lock') | +-------------------------+ | 0 | +-------------------------+
Use this function to determine if the name given is already in use as a lock name. If the lock name is in use, it returns the connection identifier of the client holding the lock. It returns NULL if it is not in use. Locks are created by GET_LOCK( ). SELECT IS_USED_LOCK('my_lock'); +-------------------------+ | IS_USED_LOCK('my_lock') | +-------------------------+ | 1 | +-------------------------+
Use this function to determine if the value of the argument given in parentheses is NULL. Returns 1 if it is NULL; 0 if it is not NULL. SELECT ISNULL(col2) FROM table1;
This function returns the identification number of the last row inserted using the MySQL connection. The identification number for rows inserted by other clients will not be returned to the other connections. Identification numbers that were set manually when rows are inserted, without the aid of AUTO_INCREMENT, won't register and therefore won't be returned by LAST_INSERT_ID( ). If multiple rows are inserted by one SQL statement, LAST_INSERT_ID( ) returns the identification number for the first row inserted. SELECT LAST_INSERT_ID( ); +------------------+ | LAST_INSERT_ID( ) | +------------------+ | 1039 | +------------------+
MASTER_POS_WAIT(filename, position[, timeout]) | |
Use this function to synchronize MySQL master and slave server logging. The function causes the master to wait until the slave server has read and applied all updates to the position (given in the second argument) in the master log (named in the first argument). You can specify a third argument to set the number of seconds the master will wait. The function returns the number of log entries that were made by the slave while the master was waiting. A value of NULL is returned if there is an error.
This function releases a lock created by GET_LOCK(). The name of the lock is given in parentheses. If successful, 1 is returned; if unsuccessful, 0 is returned. If the lock specified does not exist, NULL is returned. SELECT RELEASE_LOCK('my_lock'); +-------------------------+ | RELEASE_LOCK('my_lock') | +-------------------------+ | 1 | +-------------------------+
As an alternative to using SELECT, you can use DO; no results are returned. DO RELEASE_LOCK('my_lock');
This function returns the username and the hostname for the current MySQL connection. There are no arguments for the function. It's synonymous with SYSTEM_USER( ) and USER( ).
This function returns the username and the hostname for the current MySQL connection. There are no arguments for the function. It's synonymous with SESSION_USER() and USER( ).
This function returns the username and the hostname for the current MySQL connection. There are no arguments for the function. It's synonymous with SESSION_USER( ) and SYSTEM_USER(). SELECT USER( ); +-------------------+ | USER( ) | +-------------------+ | russell@localhost | +-------------------+
This function returns a Universal Unique Identifier (UUID), a 128-bit number composed of five hexadecimal numbers. This number is intended to be unique per invocation and is based on values that are both temporal and spatial. There are no arguments for the function. It's available as of Version 4.1.2 of MySQL. SELECT UUID( ); +----------------------------------------+ | UUID( ) | +----------------------------------------+ | '8bde367a-caeb-0933-1031-7730g3321c32' | +----------------------------------------+
This function returns the MySQL server version. There are no arguments for the function. SELECT VERSION( ); +---------------------------+ | VERSION( ) | +---------------------------+ | 5.0.2-alpha-standard-log |
+---------------------------+
| 