(mysql.info.gz) Miscellaneous functions
Info Catalog
(mysql.info.gz) Information functions
(mysql.info.gz) Other Functions
12.8.4 Miscellaneous Functions
------------------------------
`DEFAULT(COL_NAME)'
Returns the default value for a table column. Starting from MySQL
5.0.2, you will get an error if the column doesn't have a default
value.
mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
`DEFAULT()' was added in MySQL 4.1.0.
`FORMAT(X,D)'
Formats the number X to a format like `'#,###,###.##'', rounded to
D decimals, and returns the result as a string. If D is `0', the
result will have no decimal point or fractional part.
mysql> SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
-> '12,332'
`GET_LOCK(STR,TIMEOUT)'
Tries to obtain a lock with a name given by the string STR, with 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.
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
Note that the second `RELEASE_LOCK()' call returns `NULL' because
the lock `'lock1'' was automatically released by the second
`GET_LOCK()' call.
`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*256^3 +
207*256^2 + 224*256 + 40'.
As of MySQL 4.1.2, `INET_ATON()' also understands short-form IP
addresses:
mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 2130706433, 2130706433
`INET_ATON()' was added in MySQL 3.23.15.
`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'
`INET_NTOA()' was added in MySQL 3.23.15.
`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' on errors (such as
incorrect arguments).
`IS_FREE_LOCK()' was added in MySQL 4.0.2.
`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'.
`IS_USED_LOCK()' was added in MySQL 4.1.0.
`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 it had to wait for to get
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.
`MASTER_POS_WAIT()' was added in MySQL 3.23.32. The TIMEOUT
argument was added in 4.0.10.
`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 wasn't locked by this thread (in which case the lock is not
released), and `NULL' if the named lock didn't exist. The lock
will 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()'.
`DO' DO.
`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).
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.
`UUID()' was added in MySQL 4.1.2.
Info Catalog
(mysql.info.gz) Information functions
(mysql.info.gz) Other Functions
automatically generated byinfo2html