Table 11.18 Miscellaneous Functions
|Return the default value for a table column|
|Get a named lock|
|Return the numeric value of an IP address|
|Return the IP address from a numeric value|
|Checks whether the named lock is free|
|Checks whether the named lock is in use. Return connection identifier if true.|
|Block until the slave has read and applied all updates up to the specified position|
|Return a random floating-point value|
|Releases the named lock|
|Return a Universal Unique Identifier (UUID)|
|Defines the values to be used during an INSERT|
Returns the default value for a table column.
UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
DEFAULT() was added in MySQL
Formats the number
X to a format
'#,###,###.##', rounded to
D decimal places, and returns the
result as a string. For details, see
Section 11.5, “String Functions”.
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
GET_LOCK(), it is
released when you execute
RELEASE_LOCK(), execute a new
GET_LOCK(), or your connection
terminates (either normally or abnormally). Locks obtained
GET_LOCK() do not interact
with transactions. That is, committing a transaction does not
release any such locks obtained during the transaction.
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 enables
clients that agree on a given lock name to use the name to
perform cooperative advisory locking. But be aware that it
also enables 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
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
If multiple clients are waiting for a lock, the order in which they will acquire it is undefined and depends on factors such as the thread library in use. In particular, applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.
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 (fixed in MySQL 5.5).
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.
SELECT INET_ATON('220.127.116.11');-> 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.
As of MySQL 4.1.2,
also understands short-form IP addresses:
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
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
Section 10.2.5, “Out-of-Range and Overflow Handling”.
INET_ATON() was added in MySQL
Given a numeric network address in network byte order (4 or 8 byte), returns the dotted-quad representation of the address as a binary string.
SELECT INET_NTOA(3520061480);-> '18.104.22.168'
INET_NTOA() was added in MySQL
Checks whether the lock named
is free to use (that is, not locked). Returns
1 if the lock is free (no one is using the
0 if the lock is in use, and
NULL if an error occurs (such as an
IS_FREE_LOCK() was added in
Checks whether the lock named
is in use (that is, locked). If so, it returns the connection
identifier of the client that holds the lock. Otherwise, it
IS_USED_LOCK() was added in
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
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.
-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.
timeout value is specified,
MASTER_POS_WAIT() stops waiting
timeout seconds have elapsed.
timeout must be greater than 0; a
zero or negative
timeout means no
MASTER_POS_WAIT() was added in
MySQL 3.23.32. The
was added in 4.0.10.
Releases the lock named by the string
str that was obtained with
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.
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
utf8 string of five hexadecimal numbers in
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.
SELECT UUID();-> '6ccd780c-baba-1026-9564-0040f4311e29'
UUID() function returns a
string using the character set defined by the
parameter. If you are using UUID values in your tables and
these columns are indexed the character set of your column
or table should match the character set used when the
UUID() was called. If you do
not use the same character set for the column and the UUID
value, the indexes on those columns will not be used, which
may lead to a reduction in performance and locked tables
during operations as the table is searched sequentially for
You can convert between different character sets when using
UUID-based strings using the
UUID() does not work with
UUID() was added in MySQL
... ON DUPLICATE KEY UPDATE statement, you can use
function in the
to refer to column values from the
INSERT portion of the
statement. In other words,
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 the
ON DUPLICATE KEY UPDATE clause
INSERT statements and
NULL otherwise. See
Section 22.214.171.124, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)->
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
VALUES() was added in MySQL