Table 12.22 Miscellaneous Functions
Name | Description |
---|---|
DEFAULT() |
Return the default value for a table column |
INET_ATON() |
Return the numeric value of an IP address |
INET_NTOA() |
Return the IP address from a numeric value |
MASTER_POS_WAIT() |
Block until the slave has read and applied all updates up to the specified position |
NAME_CONST() |
Cause the column to have the given name |
SLEEP() |
Sleep for a number of seconds |
UUID() |
Return a Universal Unique Identifier (UUID) |
UUID_SHORT() |
Return an integer-valued universal identifier |
VALUES() |
Define the values to be used during an INSERT |
Returns the default value for a table column. An error results if the column has no default value.
mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
Formats the number
X
to a format like'#,###,###.##'
, rounded toD
decimal places, and returns the result as a string. For details, see Section 12.5, “String Functions and Operators”.Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian).
INET_ATON()
returnsNULL
if it does not understand its argument.mysql> SELECT INET_ATON('10.0.5.9'); -> 167773449
For this example, the return value is calculated as 10×2563 + 0×2562 + 5×256 + 9.
INET_ATON()
may or may not return a non-NULL
result for short-form IP addresses (such as'127.1'
as a representation of'127.0.0.1'
). Because of this,INET_ATON()
a should not be used for such addresses.NoteTo store values generated by
INET_ATON()
, use anINT UNSIGNED
column rather thanINT
, which is signed. If you use a signed column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly. See Section 11.2.6, “Out-of-Range and Overflow Handling”.Given a numeric IPv4 network address in network byte order, returns the dotted-quad representation of the address as a string.
INET_NTOA()
returnsNULL
if it does not understand its argument.The return value is a string in the connection character set.
mysql> SELECT INET_NTOA(167773449); -> '10.0.5.9'
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 whileMASTER_POS_WAIT()
is waiting, the function returnsNULL
. If the slave is past the specified position, the function returns immediately.If a
timeout
value is specified,MASTER_POS_WAIT()
stops waiting whentimeout
seconds have elapsed.timeout
must be greater than 0; a zero or negativetimeout
means no timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name.This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Returns the given value. When used to produce a result set column,
NAME_CONST()
causes the column to have the given name. The arguments should be constants.mysql> SELECT NAME_CONST('myname', 14); +--------+ | myname | +--------+ | 14 | +--------+
This function is for internal use only. The server uses it when writing statements from stored programs that contain references to local program variables, as described in Section 20.7, “Stored Program Binary Logging”. You might see this function in the output from mysqlbinlog.
For your applications, you can obtain exactly the same result as in the example just shown by using simple aliasing, like this:
mysql> SELECT 14 AS myname; +--------+ | myname | +--------+ | 14 | +--------+ 1 row in set (0.00 sec)
See Section 13.2.9, “SELECT Statement”, for more information about column aliases.
Sleeps (pauses) for the number of seconds given by the
duration
argument, then returns 0. IfSLEEP()
is interrupted, it returns 1. The duration may have a fractional part.When sleep returns normally (without interruption), it returns 0:
mysql> SELECT SLEEP(1000); +-------------+ | SLEEP(1000) | +-------------+ | 0 | +-------------+
When
SLEEP()
is the only thing invoked by a query that is interrupted, it returns 1 and the query itself returns no error. This statement is interrupted usingKILL QUERY
from another session:mysql> SELECT SLEEP(1000); +-------------+ | SLEEP(1000) | +-------------+ | 1 | +-------------+
When
SLEEP()
is only part of a query that is interrupted, the query returns an error. This statement is interrupted usingKILL QUERY
from another session:mysql> SELECT 1 FROM t1 WHERE SLEEP(1000); ERROR 1317 (70100): Query execution was interrupted
This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Returns a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace” (http://www.ietf.org/rfc/rfc4122.txt).
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 devices not connected to each other.WarningAlthough
UUID()
values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.UUID()
returns a value that conforms to UUID version 1 as described in RFC 4122. The value is a 128-bit number represented as autf8
string of five hexadecimal numbers inaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
format:The first three numbers are generated from the low, middle, and high parts of a timestamp. The high part also includes the UUID version number.
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 device has no Ethernet card, or it is unknown how to find the hardware address of an interface on the host operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.
The MAC address of an interface is taken into account only on FreeBSD, Linux, and Windows. On other operating systems, MySQL uses a randomly generated 48-bit number.
mysql> SELECT UUID(); -> '6ccd780c-baba-1026-9564-5b8c656024db'
This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Returns a “short” universal identifier as a 64-bit unsigned integer. Values returned by
UUID_SHORT()
differ from the string-format 128-bit identifiers returned by theUUID()
function and have different uniqueness properties. The value ofUUID_SHORT()
is guaranteed to be unique if the following conditions hold:The
server_id
value of the current server is between 0 and 255 and is unique among your set of master and slave serversYou do not set back the system time for your server host between mysqld restarts
You invoke
UUID_SHORT()
on average fewer than 16 million times per second between mysqld restarts
The
UUID_SHORT()
return value is constructed this way:(server_id & 255) << 56 + (server_startup_time_in_seconds << 24) + incremented_variable++;
mysql> SELECT UUID_SHORT(); -> 92395783831158784
NoteUUID_SHORT()
does not work with statement-based replication.In an
INSERT ... ON DUPLICATE KEY UPDATE
statement, you can use theVALUES(
function in thecol_name
)UPDATE
clause to refer to column values from theINSERT
portion of the statement. In other words,VALUES(
in thecol_name
)UPDATE
clause refers to the value ofcol_name
that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. TheVALUES()
function is meaningful only in theON DUPLICATE KEY UPDATE
clause ofINSERT
statements and returnsNULL
otherwise. See Section 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);