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.


 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.


 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



 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( );  root@mel601439.quest.com



 database_name =DATABASE ( )

DATABASE returns the name of the database currently in use.

 USE prod;
 SET var1=database( );  prod


9.4.5. GET_LOCK


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( )

 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');
 SELECT 'failed to acquire lock';
 /* Error handling here */

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.


 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



 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.


 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.


Synonym for 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( );  root@mel601439.quest.com


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( )

 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';
 SELECT 'This version of MySQL does not support stored procedures',
 'you must be dreaming';


This function returns the MySQL server version. There are no arguments for the function.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

show all menu

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208
Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net