(mysql.info) miscellaneous-functions
Info Catalog
(mysql.info) information-functions
(mysql.info) other-functions
12.9.4 Miscellaneous Functions
------------------------------
* `DEFAULT(COL_NAME)'
Returns the default value for a table column. Starting with MySQL
5.0.2, an error results if the column has no default value.
mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
* `FORMAT(X,D)'
Formats the number X to a format like `'#,###,###.##'', rounded to
D decimal places, and returns the result as a string. For details,
see string-functions.
* `GET_LOCK(STR,TIMEOUT)'
Tries to obtain a lock with a name given by the string STR, using
a timeout of TIMEOUT seconds. Returns `1' if the lock was obtained
successfully, `0' if the attempt timed out (for example, because
another client has previously locked the name), or `NULL' if an
error occurred (such as running out of memory or the thread was
killed with `mysqladmin kill'). If you have a lock obtained with
`GET_LOCK()', it is released when you execute `RELEASE_LOCK()',
execute a new `GET_LOCK()', or your connection terminates (either
normally or abnormally).
This function can be used to implement application locks or to
simulate record locks. Names are locked on a server-wide basis. If
a name has been locked by one client, `GET_LOCK()' blocks any
request by another client for a lock with the same name. This
allows clients that agree on a given lock name to use the name to
perform cooperative advisory locking. But be aware that it also
allows a client that is not among the set of cooperating clients
to lock a name, either inadvertently or deliberately, and thus
prevent any of the cooperating clients from locking that name. One
way to reduce the likelihood of this is to use lock names that are
database-specific or application-specific. For example, use lock
names of the form DB_NAME.STR or APP_NAME.STR.
mysql> SELECT GET_LOCK('lock1',10);
-> 1
mysql> SELECT IS_FREE_LOCK('lock2');
-> 1
mysql> SELECT GET_LOCK('lock2',10);
-> 1
mysql> SELECT RELEASE_LOCK('lock2');
-> 1
mysql> SELECT RELEASE_LOCK('lock1');
-> NULL
The second `RELEASE_LOCK()' call returns `NULL' because the lock
`'lock1'' was automatically released by the second `GET_LOCK()'
call.
Note: If a client attempts to acquire a lock that is already held
by another client, it blocks according to the TIMEOUT argument. If
the blocked client terminates, its thread does not die until the
lock request times out. This is a known bug.
* `INET_ATON(EXPR)'
Given the dotted-quad representation of a network address as a
string, returns an integer that represents the numeric value of
the address. Addresses may be 4- or 8-byte addresses.
mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480
The generated number is always in network byte order. For the
example just shown, the number is calculated as 209×2563 +
207×2562 + 224×256 + 40.
`INET_ATON()' also understands short-form IP addresses:
mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 2130706433, 2130706433
* When storing values generated by `INET_ATON()', it is
recommended that you use an `INT UNSIGNED' column. If you use a
(signed) `INT' column, values corresponding to IP addresses for
which the first octet is greater than 127 cannot be stored
correctly. See numeric-types.
* `INET_NTOA(EXPR)'
Given a numeric network address (4 or 8 byte), returns the
dotted-quad representation of the address as a string.
mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'
* `IS_FREE_LOCK(STR)'
Checks whether the lock named STR is free to use (that is, not
locked). Returns `1' if the lock is free (no one is using the
lock), `0' if the lock is in use, and `NULL' if an error occurs
(such as an incorrect argument).
* `IS_USED_LOCK(STR)'
Checks whether the lock named STR is in use (that is, locked). If
so, it returns the connection identifier of the client that holds
the lock. Otherwise, it returns `NULL'.
* `MASTER_POS_WAIT(LOG_NAME,LOG_POS[,TIMEOUT])'
This function is useful for control of master/slave
synchronization. It blocks until the slave has read and applied
all updates up to the specified position in the master log. The
return value is the number of log events the slave had to wait for
to advance to the specified position. The function returns `NULL'
if the slave SQL thread is not started, the slave's master
information is not initialized, the arguments are incorrect, or an
error occurs. It returns `-1' if the timeout has been exceeded. If
the slave SQL thread stops while `MASTER_POS_WAIT()' is waiting,
the function returns `NULL'. If the slave is past the specified
position, the function returns immediately.
If a TIMEOUT value is specified, `MASTER_POS_WAIT()' stops waiting
when TIMEOUT seconds have elapsed. TIMEOUT must be greater than
0; a zero or negative TIMEOUT means no timeout.
* `NAME_CONST(NAME,VALUE)'
Returns the given value. When used to produce a result set column,
`NAME_CONST()' causes the column to have the given name.
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+
This function was added in MySQL 5.0.12. It is for internal use
only. The server uses it when writing statements from stored
routines that contain references to local routine variables, as
described in stored-procedure-logging, You might see this
function in the output from `mysqlbinlog'.
* `RELEASE_LOCK(STR)'
Releases the lock named by the string STR that was obtained with
`GET_LOCK()'. Returns `1' if the lock was released, `0' if the lock
was not established by this thread (in which case the lock is not
released), and `NULL' if the named lock did not exist. The lock
does not exist if it was never obtained by a call to `GET_LOCK()'
or if it has previously been released.
The `DO' statement is convenient to use with `RELEASE_LOCK()'. See
do.
* `SLEEP(DURATION)'
Sleeps (pauses) for the number of seconds given by the DURATION
argument, then returns 0. If `SLEEP()' is interrupted, it returns
1. The duration may have a fractional part given in microseconds.
This function was added in MySQL 5.0.12.
* `UUID()'
Returns a Universal Unique Identifier (UUID) generated according
to `DCE 1.1: Remote Procedure Call' (Appendix A) CAE (Common
Applications Environment) Specifications published by The Open
Group in October 1997 (Document Number C706,
`http://www.opengroup.org/public/pubs/catalog/c706.htm').
A UUID is designed as a number that is globally unique in space
and time. Two calls to `UUID()' are expected to generate two
different values, even if these calls are performed on two
separate computers that are not connected to each other.
A UUID is a 128-bit number represented by a string of five
hexadecimal numbers in `aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
format:
* The first three numbers are generated from a timestamp.
* The fourth number preserves temporal uniqueness in case the
timestamp value loses monotonicity (for example, due to
daylight saving time).
* The fifth number is an IEEE 802 node number that provides
spatial uniqueness. A random number is substituted if the
latter is not available (for example, because the host
computer has no Ethernet card, or we do not know how to find
the hardware address of an interface on your operating
system). In this case, spatial uniqueness cannot be
guaranteed. Nevertheless, a collision should have _very_ low
probability.
Currently, the MAC address of an interface is taken into
account only on FreeBSD and Linux. On other operating
systems, MySQL uses a randomly generated 48-bit number.
mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'
Note that `UUID()' does not yet work with replication.
* `VALUES(COL_NAME)'
In an `INSERT ... ON DUPLICATE KEY UPDATE' statement, you can use
the `VALUES(COL_NAME)' function in the `UPDATE' clause to refer to
column values from the `INSERT' portion of the statement. In other
words, `VALUES(COL_NAME)' in the `UPDATE' clause refers to the
value of COL_NAME that would be inserted, had no duplicate-key
conflict occurred. This function is especially useful in
multiple-row inserts. The `VALUES()' function is meaningful only in
`INSERT ... ON DUPLICATE KEY UPDATE' statements and returns `NULL'
otherwise. insert-on-duplicate.
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Info Catalog
(mysql.info) information-functions
(mysql.info) other-functions
automatically generated byinfo2html