MySQL Stored Procedure Programming

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.

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.

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

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]

DATABASE

     database_name=DATABASE(  )

DATABASE returns the name of the database currently in use.

    USE prod;
    SET var1=database(  );            →  prod

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 ...