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;  1
 SET var2=2;  2
 SET var3=NULL; 
 SET var4=COALESCE(var1,var2,var3);  1
 SET var5=COALESCE(var3,var2,var1) ;  2

 

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( );  root@%
 SET var2=USER( );  [e-mail]

 

9.4.4. DATABASE

 database_name =DATABASE ( )

DATABASE returns the name of the database currently in use.

 USE prod;
 SET var1=database( );  prod

 

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);  2

 

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( );  root@%
 SET var2=USER( );  [e-mail]

 

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( );  7a89e3d9-52ea-1028-abea-122ba2ad7d69
 SET var2=UUID( );  7a9ca65d-52ea-1028-abea-122ba2ad7d69
 SET var3=UUID( );  7aa78e82-52ea-1028-abea-122ba2ad7d69

 

9.4.16. VERSION

 string =VERSION ( )

VERSION reports the current version of the MySQL server software.

 SET var1=VERSION( );  5.0.18-nightly-20051211-log

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.