Other Functions
The miscellaneous built-in functions described in the following sections perform operations that do not fall into the categories described in earlier sections.
9.4.1. BENCHMARK
zero =BENCHMARK (no_of_repeats , expression )
BENCHMARK executes the specified expression repeatedly. It is intended to be used to benchmark MySQL performance. This function has very little applicability in a stored program context, although in theory you could use it to repeatedly execute a stored program.
9.4.2. COALESCE
value =COALESCE (value [,...])
COALESCE returns the first non-NULL value in the provided list of values.
SET var1=1;
9.4.3. CURRENT_USER
username =CURRENT_USER ( )
CURRENT_USER returns the username and hostname of the current MySQL user. It may report a different value from that returned by USER, since the USER function reports the connection requested by the user, rather than the connection that was actually used.
SET var1=CURRENT_USER( );
9.4.4. DATABASE
database_name =DATABASE ( )
DATABASE returns the name of the database currently in use.
USE prod;
SET var1=database( );
9.4.5. GET_LOCK
return_code=GET_LOCK(lock_name,timeout)
GET_LOCK allows you to define and acquire a user-defined lock. The lock_name can be a string of your choice. GET_LOCK will attempt to acquire the lock; then, if no other session holds the lock, it will return 1. If the lock is held by another session, GET_LOCK will wait until timeout seconds has elapsed; then, if the lock can still not be acquired, it will return 0.
Only one "user" lock can be held at any timethat is, each invocation of GET_LOCK releases any previous locks.
GET_LOCK can be used to ensure that only one copy of a stored program is executing a particular segment of code at any one time. Note, however, that for most activities that might be performed by a stored program, table locking is preferable.
Example 9-12 provides an example of both the GET_LOCK and RELEASE_LOCK functions.
Example 9-12. Example of GET_LOCK and RELEASE_LOCK
|
CREATE PROCEDURE sp_critical_section( ) BEGIN DECLARE lock_result INT; IF get_lock('sp_critical_section_lock',60) THEN /* This block can only be run by one user at a time*/ SELECT 'got lock'; /* Critical code here */ SET lock_result=release_lock('sp_critical_section_lock'); ELSE SELECT 'failed to acquire lock'; /* Error handling here */ END IF; END; |
9.4.6. IFNULL
value1 =IFNULL (value2 ,nullvalue )
IFNULL returns the value provided as value2. If that value is NULL, it returns the value provided in the second argument.
9.4.7. INTERVAL
position =INTERVAL (search ,number , ...)
INTERVAL returns the position (starting at 0) that the search value would take within the specified list of numbers. The list must be in ascending order.
SET var2=INTERVAL(20,5,10,30,50);
9.4.8. IS_FREE_LOCK
integer =IS_FREE_LOCK (string )
IF_FREE_LOCK returns 1 if the specified user-defined lock is available (e.g., not locked) and 0 if the lock is taken. See GET_LOCK.
9.4.9. ISNULL
integer =ISNULL (value )
ISNULL returns 1 if the parameter value is NULL and returns 0 otherwise.
9.4.10. NULLIF
value1 =NULLIF (value2 ,value3 )
NULLIF returns NULL if the two values provided are equal. Otherwise, it returns the first value.
9.4.11. RELEASE_LOCK
integer =RELEASE_LOCK (string )
RELEASE_LOCK releases a lock acquired by the GET_LOCK function. See GET_LOCK for more details and an example of usage.
9.4.12. SESSION_USER
Synonym for USER .
9.4.13. SYSTEM_USER
Synonym for USER.
9.4.14. USER
username =USER ( )
USER returns the username and hostname for the current MySQL connection. This function reports the username and hostname that were used to establish the connection, while the CURRENT_USER function reports the username from the mysql.user table that is actually in use.
SET var1=CURRENT_USER( );
9.4.15. UUID
string =UUID ( )
UUID returns a 128-bit Universal Unique Identifier (UUID). Each invocation of UUID returns a unique value. Part of the UUID is generated from your computer name and part from the current date and time. Therefore, you can be quite confident that UUIDs are unique across the world (subject to the very small chance that a computer with your exact configuration generated a UUID at the exact same time).
SET var1=UUID( );
9.4.16. VERSION
string =VERSION ( )
VERSION reports the current version of the MySQL server software.
SET var1=VERSION( );
In Example 9-13 we extract the major version of the version string and print an (impossible) error message if the version does not support stored programs.
Example 9-13. Using the VERSION function
|
CREATE PROCEDURE sp_mysql_version( ) BEGIN DECLARE major_version INT; SET major_version=SUBSTR(version(),1,INSTR(version( ),'.')-1); IF major_version>=5 THEN SELECT 'Good thing you are using version 5 or later'; ELSE SELECT 'This version of MySQL does not support stored procedures', 'you must be dreaming'; END IF; END; |
This function returns the MySQL server version. There are no arguments for the function.