Table 12.28 Miscellaneous Functions
Name | Description |
---|---|
ANY_VALUE() |
Suppress ONLY_FULL_GROUP_BY value rejection |
BIN_TO_UUID() |
Convert binary UUID to string |
DEFAULT() |
Return the default value for a table column |
GET_LOCK() |
Get a named lock |
GROUPING() |
Distinguish super-aggregate ROLLUP rows from regular rows |
INET_ATON() |
Return the numeric value of an IP address |
INET_NTOA() |
Return the IP address from a numeric value |
INET6_ATON() |
Return the numeric value of an IPv6 address |
INET6_NTOA() |
Return the IPv6 address from a numeric value |
IS_FREE_LOCK() |
Whether the named lock is free |
IS_IPV4() |
Whether argument is an IPv4 address |
IS_IPV4_COMPAT() |
Whether argument is an IPv4-compatible address |
IS_IPV4_MAPPED() |
Whether argument is an IPv4-mapped address |
IS_IPV6() |
Whether argument is an IPv6 address |
IS_USED_LOCK() |
Whether the named lock is in use; return connection identifier if true |
IS_UUID() |
Whether argument is a valid UUID |
MASTER_POS_WAIT() |
Block until the slave has read and applied all updates up to the specified position |
NAME_CONST() |
Causes the column to have the given name |
RAND() |
Return a random floating-point value |
RELEASE_ALL_LOCKS() |
Releases all current named locks |
RELEASE_LOCK() |
Releases the named lock |
SLEEP() |
Sleep for a number of seconds |
UUID() |
Return a Universal Unique Identifier (UUID) |
UUID_SHORT() |
Return an integer-valued universal identifier |
UUID_TO_BIN() |
Convert string UUID to binary |
VALUES() |
Defines the values to be used during an INSERT |
This function is useful for
GROUP BY
queries when theONLY_FULL_GROUP_BY
SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for theONLY_FULL_GROUP_BY
SQL mode.For example, if
name
is a nonindexed column, the following query fails withONLY_FULL_GROUP_BY
enabled:mysql> SELECT name, address, MAX(age) FROM t GROUP BY name; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The failure occurs because
address
is a nonaggregated column that is neither named amongGROUP BY
columns nor functionally dependent on them. As a result, theaddress
value for rows within eachname
group is nondeterministic. There are multiple ways to cause MySQL to accept the query:Alter the table to make
name
a primary key or a uniqueNOT NULL
column. This enables MySQL to determine thataddress
is functionally dependent onname
; that is,address
is uniquely determined byname
. (This technique is inapplicable ifNULL
must be permitted as a validname
value.)Use
ANY_VALUE()
to refer toaddress
:SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
In this case, MySQL ignores the nondeterminism of
address
values within eachname
group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group.ANY_VALUE()
is not an aggregate function, unlike functions such asSUM()
orCOUNT()
. It simply acts to suppress the test for nondeterminism.Disable
ONLY_FULL_GROUP_BY
. This is equivalent to usingANY_VALUE()
withONLY_FULL_GROUP_BY
enabled, as described in the previous item.
ANY_VALUE()
is also useful if functional dependence exists between columns but MySQL cannot determine it. The following query is valid becauseage
is functionally dependent on the grouping columnage-1
, but MySQL cannot tell that and rejects the query withONLY_FULL_GROUP_BY
enabled:SELECT age FROM t GROUP BY age-1;
To cause MySQL to accept the query, use
ANY_VALUE()
:SELECT ANY_VALUE(age) FROM t GROUP BY age-1;
ANY_VALUE()
can be used for queries that refer to aggregate functions in the absence of aGROUP BY
clause:mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this is incompatible with sql_mode=only_full_group_by
Without
GROUP BY
, there is a single group and it is nondeterministic whichname
value to choose for the group.ANY_VALUE()
tells MySQL to accept the query:SELECT ANY_VALUE(name), MAX(age) FROM t;
It may be that, due to some property of a given data set, you know that a selected nonaggregated column is effectively functionally dependent on a
GROUP BY
column. For example, an application may enforce uniqueness of one column with respect to another. In this case, usingANY_VALUE()
for the effectively functionally dependent column may make sense.For additional discussion, see Section 12.19.3, “MySQL Handling of GROUP BY”.
BIN_TO_UUID(
,binary_uuid
)BIN_TO_UUID(
binary_uuid
,swap_flag
)BIN_TO_UUID()
is the inverse ofUUID_TO_BIN()
. It converts a binary UUID to a string UUID and returns the result. The binary value should be a UUID as aVARBINARY(16)
value. The return value is autf8
string of five hexadecimal numbers separated by dashes. (For details about this format, see theUUID()
function description.) If the UUID argument isNULL
, the return value isNULL
. If any argument is invalid, an error occurs.BIN_TO_UUID()
takes one or two arguments:The one-argument form takes a binary UUID value. The UUID value is assumed not to have its time-low and time-high parts swapped. The string result is in the same order as the binary argument.
The two-argument form takes a binary UUID value and a swap-flag value:
If
swap_flag
is 0, the two-argument form is equivalent to the one-argument form. The string result is in the same order as the binary argument.If
swap_flag
is 1, the UUID value is assumed to have its time-low and time-high parts swapped. These parts are swapped back to their original position in the result value.
For usage examples and information about time-part swapping, see the
UUID_TO_BIN()
function description.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”.Tries to obtain a lock with a name given by the string
str
, using a timeout oftimeout
seconds. A negativetimeout
value means infinite timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name.Returns
1
if the lock was obtained successfully,0
if the attempt timed out (for example, because another client has previously locked the name), orNULL
if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill).A lock obtained with
GET_LOCK()
is released explicitly by executingRELEASE_LOCK()
or implicitly when your session terminates (either normally or abnormally). Lock release may also occur with another call toGET_LOCK()
:GET_LOCK()
is implemented using the metadata locking (MDL) subsystem. Multiple simultaneous locks can be acquired andGET_LOCK()
does not release any existing locks. It is even possible for a given session to acquire multiple locks for the same name. Other sessions cannot acquire a lock with that name until the acquiring session releases all its locks for the name.Locks acquired with
GET_LOCK()
appear in the Performance Schemametadata_locks
table. TheOBJECT_TYPE
column saysUSER LEVEL LOCK
and theOBJECT_NAME
column indicates the lock name. Also, the capability of acquiring multiple locks introduces the possibility of deadlock among clients. When this happens, the server chooses a caller and terminates its lock-acquisition request with anER_USER_LOCK_DEADLOCK
error. This error does not cause transactions to roll back.
For example, suppose that you execute these statements:
SELECT GET_LOCK('lock1',10); SELECT GET_LOCK('lock2',10); SELECT RELEASE_LOCK('lock2'); SELECT RELEASE_LOCK('lock1');
The second
GET_LOCK()
acquires a second lock and bothRELEASE_LOCK()
calls return 1 (success).MySQL enforces a maximum length on lock names of 64 characters.
Locks obtained with
GET_LOCK()
are not released when transactions commit or roll back.GET_LOCK()
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 within one session,GET_LOCK()
blocks any request by another session 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 formdb_name.str
orapp_name.str
.If multiple clients are waiting for a lock, the order in which they will acquire it is undefined. Applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.
GET_LOCK()
is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_format
is set toSTATEMENT
.CautionWith the capability of acquiring multiple named locks in MySQL 5.7.5, it is possible for a single statement to acquire a large number of locks. For example:
INSERT INTO ... SELECT GET_LOCK(t1.col_name) FROM t1;
These types of statements may have certain adverse effects. For example, if the statement fails part way through and rolls back, locks acquired up to the point of failure will still exist. If the intent is for there to be a correspondence between rows inserted and locks acquired, that intent will not be satisfied. Also, if it is important that locks are granted in a certain order, be aware that result set order may differ depending on which execution plan the optimizer chooses. For these reasons, it may be best to limit applications to a single lock-acquisition call per statement.
A different locking interface is available as either a plugin service or a set of user-defined functions. This interface provides lock namespaces and distinct read and write locks, unlike the interface provided by
GET_LOCK()
and related functions. For details, see Section 28.3.1, “The Locking Service”.For
GROUP BY
queries that include aWITH ROLLUP
modifier, theROLLUP
operation produces super-aggregate output rows whereNULL
represents the set of all values. TheGROUPING()
function enables you to distinguishNULL
values for super-aggregate rows fromNULL
values in regular grouped rows.GROUPING()
is permitted only in the select list orHAVING
clause.Each argument to
GROUPING()
must be an expression that exactly matches an expression in theGROUP BY
clause. The expression cannot be a positional specifier. For each expression,GROUPING()
produces 1 if the expression value in the current row is aNULL
representing a super-aggregate value. Otherwise,GROUPING()
produces 0, indicating that the expression value is aNULL
for a regular result row or is notNULL
.Suppose that table
t1
contains these rows, whereNULL
indicates something like “other” or “unknown”:mysql> SELECT * FROM t1; +------+-------+----------+ | name | size | quantity | +------+-------+----------+ | ball | small | 10 | | ball | large | 20 | | ball | NULL | 5 | | hoop | small | 15 | | hoop | large | 5 | | hoop | NULL | 3 | +------+-------+----------+
A summary of the table without
WITH ROLLUP
looks like this:mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size; +------+-------+----------+ | name | size | quantity | +------+-------+----------+ | ball | small | 10 | | ball | large | 20 | | ball | NULL | 5 | | hoop | small | 15 | | hoop | large | 5 | | hoop | NULL | 3 | +------+-------+----------+
The result contains
NULL
values, but those do not represent super-aggregate rows because the query does not includeWITH ROLLUP
.Adding
WITH ROLLUP
produces super-aggregate summary rows containing additionalNULL
values. However, without comparing this result to the previous one, it is not easy to see whichNULL
values occur in super-aggregate rows and which occur in regular grouped rows:mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP; +------+-------+----------+ | name | size | quantity | +------+-------+----------+ | ball | NULL | 5 | | ball | large | 20 | | ball | small | 10 | | ball | NULL | 35 | | hoop | NULL | 3 | | hoop | large | 5 | | hoop | small | 15 | | hoop | NULL | 23 | | NULL | NULL | 58 | +------+-------+----------+
To distinguish
NULL
values in in super-aggregate rows from those in regular grouped rows, useGROUPING()
, which returns 1 only for super-aggregateNULL
values:mysql> SELECT name, size, SUM(quantity) AS quantity, GROUPING(name) AS grp_name, GROUPING(size) AS grp_size FROM t1 GROUP BY name, size WITH ROLLUP; +------+-------+----------+----------+----------+ | name | size | quantity | grp_name | grp_size | +------+-------+----------+----------+----------+ | ball | NULL | 5 | 0 | 0 | | ball | large | 20 | 0 | 0 | | ball | small | 10 | 0 | 0 | | ball | NULL | 35 | 0 | 1 | | hoop | NULL | 3 | 0 | 0 | | hoop | large | 5 | 0 | 0 | | hoop | small | 15 | 0 | 0 | | hoop | NULL | 23 | 0 | 1 | | NULL | NULL | 58 | 1 | 1 | +------+-------+----------+----------+----------+
Common uses for
GROUPING()
:Substitute a label for super-aggregate
NULL
values:mysql> SELECT IF(GROUPING(name) = 1, 'All items', name) AS name, IF(GROUPING(size) = 1, 'All sizes', size) AS size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP; +-----------+-----------+----------+ | name | size | quantity | +-----------+-----------+----------+ | ball | NULL | 5 | | ball | large | 20 | | ball | small | 10 | | ball | All sizes | 35 | | hoop | NULL | 3 | | hoop | large | 5 | | hoop | small | 15 | | hoop | All sizes | 23 | | All items | All sizes | 58 | +-----------+-----------+----------+
Return only super-aggregate lines by filtering out the regular grouped lines:
mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP HAVING GROUPING(name) = 1 OR GROUPING(size) = 1; +------+------+----------+ | name | size | quantity | +------+------+----------+ | ball | NULL | 35 | | hoop | NULL | 23 | | NULL | NULL | 58 | +------+------+----------+
GROUPING()
permits multiple expression arguments. In this case, theGROUPING()
return value represents a bitmask combined from the results for each expression, where the lowest-order bit corresponds to the result for the rightmost expression. For example, with three expression arguments,GROUPING(
is evaluated like this:expr1
,expr2
,expr3
)result for GROUPING(expr3) + result for GROUPING(expr2) << 1 + result for GROUPING(expr1) << 2
The following query shows how
GROUPING()
results for single arguments combine for a multiple-argument call to produce a bitmask value:mysql> SELECT name, size, SUM(quantity) AS quantity, GROUPING(name) AS grp_name, GROUPING(size) AS grp_size, GROUPING(name, size) AS grp_all FROM t1 GROUP BY name, size WITH ROLLUP; +------+-------+----------+----------+----------+---------+ | name | size | quantity | grp_name | grp_size | grp_all | +------+-------+----------+----------+----------+---------+ | ball | NULL | 5 | 0 | 0 | 0 | | ball | large | 20 | 0 | 0 | 0 | | ball | small | 10 | 0 | 0 | 0 | | ball | NULL | 35 | 0 | 1 | 1 | | hoop | NULL | 3 | 0 | 0 | 0 | | hoop | large | 5 | 0 | 0 | 0 | | hoop | small | 15 | 0 | 0 | 0 | | hoop | NULL | 23 | 0 | 1 | 1 | | NULL | NULL | 58 | 1 | 1 | 3 | +------+-------+----------+----------+----------+---------+
With multiple expression arguments, the
GROUPING()
return value is nonzero if any expression represents a super-aggregate value. Multiple-argumentGROUPING()
syntax thus provides a simpler way to write the earlier query that returned only super-aggregate rows, by using a single multiple-argumentGROUPING()
call rather than multiple single-argument calls:mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP HAVING GROUPING(name, size) <> 0; +------+------+----------+ | name | size | quantity | +------+------+----------+ | ball | NULL | 35 | | hoop | NULL | 23 | | NULL | NULL | 58 | +------+------+----------+
Use of
GROUPING()
is subject to these limitations:Do not use subquery
GROUP BY
expressions asGROUPING()
arguments because matching might fail. For example, matching fails for this query:mysql> SELECT GROUPING((SELECT MAX(name) FROM t1)) FROM t1 GROUP BY (SELECT MAX(name) FROM t1) WITH ROLLUP; ERROR 3580 (HY000): Argument #1 of GROUPING function is not in GROUP BY
GROUP BY
literal expressions should not be used within aHAVING
clause asGROUPING()
arguments. Due to differences between when the optimizer evaluatesGROUP BY
andHAVING
, matching may succeed butGROUPING()
evaluation does not produce the expected result. Consider this query:SELECT a AS f1, 'w' AS f2 FROM t GROUP BY f1, f2 WITH ROLLUP HAVING GROUPING(f2) = 1;
GROUPING()
is evaluated earlier for the literal constant expression than for theHAVING
clause as a whole and returns 0. To check whether a query such as this is affected, useEXPLAIN
and look forImpossible having
in theExtra
column.
For more information about
WITH ROLLUP
andGROUPING()
, see Section 12.19.2, “GROUP BY Modifiers”.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 string representation of the address as a string in the connection character set.
INET_NTOA()
returnsNULL
if it does not understand its argument.mysql> SELECT INET_NTOA(167773449); -> '10.0.5.9'
Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address in network byte order (big endian). Because numeric-format IPv6 addresses require more bytes than the largest integer type, the representation returned by this function has the
VARBINARY
data type:VARBINARY(16)
for IPv6 addresses andVARBINARY(4)
for IPv4 addresses. If the argument is not a valid address,INET6_ATON()
returnsNULL
.The following examples use
HEX()
to display theINET6_ATON()
result in printable form:mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089')); -> 'FDFE0000000000005A55CAFFFEFA9089' mysql> SELECT HEX(INET6_ATON('10.0.5.9')); -> '0A000509'
INET6_ATON()
observes several constraints on valid arguments. These are given in the following list along with examples.A trailing zone ID is not permitted, as in
fe80::3%1
orfe80::3%eth0
.A trailing network mask is not permitted, as in
2001:45f:3:ba::/64
or198.51.100.0/24
.For values representing IPv4 addresses, only classless addresses are supported. Classful addresses such as
198.51.1
are rejected. A trailing port number is not permitted, as in198.51.100.2:8080
. Hexadecimal numbers in address components are not permitted, as in198.0xa0.1.2
. Octal numbers are not supported:198.51.010.1
is treated as198.51.10.1
, not198.51.8.1
. These IPv4 constraints also apply to IPv6 addresses that have IPv4 address parts, such as IPv4-compatible or IPv4-mapped addresses.
To convert an IPv4 address
expr
represented in numeric form as anINT
value to an IPv6 address represented in numeric form as aVARBINARY
value, use this expression:INET6_ATON(INET_NTOA(expr))
For example:
mysql> SELECT HEX(INET6_ATON(INET_NTOA(167773449))); -> '0A000509'
Given an IPv6 or IPv4 network address represented in numeric form as a binary string, returns the string representation of the address as a string in the connection character set. If the argument is not a valid address,
INET6_NTOA()
returnsNULL
.INET6_NTOA()
has these properties:It does not use operating system functions to perform conversions, thus the output string is platform independent.
The return string has a maximum length of 39 (4 x 8 + 7). Given this statement:
CREATE TABLE t AS SELECT INET6_NTOA(expr) AS c1;
The resulting table would have this definition:
CREATE TABLE t (c1 VARCHAR(39) CHARACTER SET utf8 DEFAULT NULL);
The return string uses lowercase letters for IPv6 addresses.
mysql> SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089')); -> 'fdfe::5a55:caff:fefa:9089' mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9')); -> '10.0.5.9' mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089')); -> 'fdfe::5a55:caff:fefa:9089' mysql> SELECT INET6_NTOA(UNHEX('0A000509')); -> '10.0.5.9'
Checks whether the lock named
str
is free to use (that is, not locked). Returns1
if the lock is free (no one is using the lock),0
if the lock is in use, andNULL
if an error occurs (such as an incorrect argument).This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Returns 1 if the argument is a valid IPv4 address specified as a string, 0 otherwise.
mysql> SELECT IS_IPV4('10.0.5.9'), IS_IPV4('10.0.5.256'); -> 1, 0
For a given argument, if
IS_IPV4()
returns 1,INET_ATON()
(andINET6_ATON()
) will return non-NULL
. The converse statement is not true: In some cases,INET_ATON()
returns non-NULL
whenIS_IPV4()
returns 0.As implied by the preceding remarks,
IS_IPV4()
is more strict thanINET_ATON()
about what constitutes a valid IPv4 address, so it may be useful for applications that need to perform strong checks against invalid values. Alternatively, useINET6_ATON()
to convert IPv4 addresses to internal form and check for aNULL
result (which indicates an invalid address).INET6_ATON()
is equally strong asIS_IPV4()
about checking IPv4 addresses.This function takes an IPv6 address represented in numeric form as a binary string, as returned by
INET6_ATON()
. It returns 1 if the argument is a valid IPv4-compatible IPv6 address, 0 otherwise. IPv4-compatible addresses have the form::
.ipv4_address
mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9')); -> 1 mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9')); -> 0
The IPv4 part of an IPv4-compatible address can also be represented using hexadecimal notation. For example,
198.51.100.1
has this raw hexadecimal value:mysql> SELECT HEX(INET6_ATON('198.51.100.1')); -> 'C6336401'
Expressed in IPv4-compatible form,
::198.51.100.1
is equivalent to::c0a8:0001
or (without leading zeros)::c0a8:1
mysql> SELECT -> IS_IPV4_COMPAT(INET6_ATON('::198.51.100.1')), -> IS_IPV4_COMPAT(INET6_ATON('::c0a8:0001')), -> IS_IPV4_COMPAT(INET6_ATON('::c0a8:1')); -> 1, 1, 1
This function takes an IPv6 address represented in numeric form as a binary string, as returned by
INET6_ATON()
. It returns 1 if the argument is a valid IPv4-mapped IPv6 address, 0 otherwise. IPv4-mapped addresses have the form::ffff:
.ipv4_address
mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.5.9')); -> 0 mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.5.9')); -> 1
As with
IS_IPV4_COMPAT()
the IPv4 part of an IPv4-mapped address can also be represented using hexadecimal notation:mysql> SELECT -> IS_IPV4_MAPPED(INET6_ATON('::ffff:198.51.100.1')), -> IS_IPV4_MAPPED(INET6_ATON('::ffff:c0a8:0001')), -> IS_IPV4_MAPPED(INET6_ATON('::ffff:c0a8:1')); -> 1, 1, 1
Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise. This function does not consider IPv4 addresses to be valid IPv6 addresses.
mysql> SELECT IS_IPV6('10.0.5.9'), IS_IPV6('::1'); -> 0, 1
For a given argument, if
IS_IPV6()
returns 1,INET6_ATON()
will return non-NULL
.Checks whether the lock named
str
is in use (that is, locked). If so, it returns the connection identifier of the client session that holds the lock. Otherwise, it returnsNULL
.This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Returns 1 if the argument is a valid string-format UUID, 0 if the argument is not a valid UUID, and
NULL
if the argument isNULL
.“Valid” means that the value is in a format that can be parsed. That is, it has the correct length and contains only the permitted characters (hexadecimal digits in any lettercase and, optionally, dashes and curly braces). This format is most common:
aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
These other formats are also permitted:
aaaaaaaabbbbccccddddeeeeeeeeeeee {aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}
For the meanings of fields within the value, see the
UUID()
function description.mysql> SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db'); +-------------------------------------------------+ | IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db') | +-------------------------------------------------+ | 1 | +-------------------------------------------------+ mysql> SELECT IS_UUID('6CCD780C-BABA-1026-9564-5B8C656024DB'); +-------------------------------------------------+ | IS_UUID('6CCD780C-BABA-1026-9564-5B8C656024DB') | +-------------------------------------------------+ | 1 | +-------------------------------------------------+ mysql> SELECT IS_UUID('6ccd780cbaba102695645b8c656024db'); +---------------------------------------------+ | IS_UUID('6ccd780cbaba102695645b8c656024db') | +---------------------------------------------+ | 1 | +---------------------------------------------+ mysql> SELECT IS_UUID('{6ccd780c-baba-1026-9564-5b8c656024db}'); +---------------------------------------------------+ | IS_UUID('{6ccd780c-baba-1026-9564-5b8c656024db}') | +---------------------------------------------------+ | 1 | +---------------------------------------------------+ mysql> SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c6560'); +---------------------------------------------+ | IS_UUID('6ccd780c-baba-1026-9564-5b8c6560') | +---------------------------------------------+ | 0 | +---------------------------------------------+ mysql> SELECT IS_UUID(RAND()); +-----------------+ | IS_UUID(RAND()) | +-----------------+ | 0 | +-----------------+
MASTER_POS_WAIT(
log_name
,log_pos
[,timeout
][,channel
])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.On a multi-threaded slave, the function waits until expiry of the limit set by the
slave_checkpoint_group
orslave_checkpoint_period
system variable, when the checkpoint operation is called to update the status of the slave. Depending on the setting for the system variables, the function might therefore return some time after the specified position was reached.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 optional
channel
value enables you to name which replication channel the function applies to. See Section 17.2.3, “Replication Channels” for more information.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 23.7, “Binary Logging of Stored Programs”. 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.10, “SELECT Syntax”, for more information about column aliases.
Releases all named locks held by the current session and returns the number of locks released (0 if there were none)
This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Releases the lock named by the string
str
that was obtained withGET_LOCK()
. Returns1
if the lock was released,0
if the lock was not established by this thread (in which case the lock is not released), andNULL
if the named lock did not exist. The lock does not exist if it was never obtained by a call toGET_LOCK()
or if it has previously been released.The
DO
statement is convenient to use withRELEASE_LOCK()
. See Section 13.2.3, “DO Syntax”.This function is unsafe for statement-based replication. A warning is logged if you use this function when
binlog_format
is set toSTATEMENT
.Sleeps (pauses) for the number of seconds given by the
duration
argument, then returns 0. The duration may have a fractional part. If the argument isNULL
or negative,SLEEP()
produces a warning, or an error in strict SQL mode.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 is true whether the query is killed or times out:This statement is interrupted using
KILL QUERY
from another session:mysql> SELECT SLEEP(1000); +-------------+ | SLEEP(1000) | +-------------+ | 1 | +-------------+
This statement is interrupted by timing out:
mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ 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 using
KILL QUERY
from another session:mysql> SELECT 1 FROM t1 WHERE SLEEP(1000); ERROR 1317 (70100): Query execution was interrupted
This statement is interrupted by timing out:
mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 FROM t1 WHERE SLEEP(1000); ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
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 and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number.
mysql> SELECT UUID(); -> '6ccd780c-baba-1026-9564-5b8c656024db'
To convert between string and binary UUID values, use the
UUID_TO_BIN()
andBIN_TO_UUID()
functions. To check whether a string is a valid UUID value, use theIS_UUID()
function.NoteUUID()
does not work with statement-based replication.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.UUID_TO_BIN(
,string_uuid
)UUID_TO_BIN(
string_uuid
,swap_flag
)Converts a string UUID to a binary UUID and returns the result. (The
IS_UUID()
function description lists the permitted string UUID formats.) The return binary UUID is aVARBINARY(16)
value. If the UUID argument isNULL
, the return value isNULL
. If any argument is invalid, an error occurs.UUID_TO_BIN()
takes one or two arguments:The one-argument form takes a string UUID value. The binary result is in the same order as the string argument.
The two-argument form takes a string UUID value and a flag value:
If
swap_flag
is 0, the two-argument form is equivalent to the one-argument form. The binary result is in the same order as the string argument.If
swap_flag
is 1, the format of the return value differs: The time-low and time-high parts (the first and third groups of hexadecimal digits, respectively) are swapped. This moves the more rapidly varying part to the right and can improve indexing efficiency if the result is stored in an indexed column.
Time-part swapping assumes the use of UUID version 1 values, such as are generated by the
UUID()
function. For UUID values produced by other means that do not follow version 1 format, time-part swapping provides no benefit. For details about version 1 format, see theUUID()
function description.Suppose that you have the following string UUID value:
mysql> SET @uuid = '6ccd780c-baba-1026-9564-5b8c656024db';
To convert the string UUID to binary with or without time-part swapping, use
UUID_TO_BIN()
:mysql> SELECT HEX(UUID_TO_BIN(@uuid)); +----------------------------------+ | HEX(UUID_TO_BIN(@uuid)) | +----------------------------------+ | 6CCD780CBABA102695645B8C656024DB | +----------------------------------+ mysql> SELECT HEX(UUID_TO_BIN(@uuid, 0)); +----------------------------------+ | HEX(UUID_TO_BIN(@uuid, 0)) | +----------------------------------+ | 6CCD780CBABA102695645B8C656024DB | +----------------------------------+ mysql> SELECT HEX(UUID_TO_BIN(@uuid, 1)); +----------------------------------+ | HEX(UUID_TO_BIN(@uuid, 1)) | +----------------------------------+ | 1026BABA6CCD780C95645B8C656024DB | +----------------------------------+
To convert a binary UUID returned by
UUID_TO_BIN()
to a string UUID, useBIN_TO_UUID()
. If you produce a binary UUID by callingUUID_TO_BIN()
with a second argument of 1 to swap time parts, you should also pass a second argument of 1 toBIN_TO_UUID()
to unswap the time parts when converting the binary UUID back to a string UUID:mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid)); +--------------------------------------+ | BIN_TO_UUID(UUID_TO_BIN(@uuid)) | +--------------------------------------+ | 6ccd780c-baba-1026-9564-5b8c656024db | +--------------------------------------+ mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0); +--------------------------------------+ | BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0) | +--------------------------------------+ | 6ccd780c-baba-1026-9564-5b8c656024db | +--------------------------------------+ mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1); +--------------------------------------+ | BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1) | +--------------------------------------+ | 6ccd780c-baba-1026-9564-5b8c656024db | +--------------------------------------+
If the use of time-part swapping is not the same for the conversion in both directions, the original UUID will not be recovered properly:
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1); +--------------------------------------+ | BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1) | +--------------------------------------+ | baba1026-780c-6ccd-9564-5b8c656024db | +--------------------------------------+ mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0); +--------------------------------------+ | BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0) | +--------------------------------------+ | 1026baba-6ccd-780c-9564-5b8c656024db | +--------------------------------------+
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.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
$ip = "127.0.0.0"; // as an example
$integer_ip = (substr($ip, 0, 3) > 127) ? ((ip2long($ip) & 0x7FFFFFFF) + 0x80000000) : ip2long($ip);
echo $integer_ip; // integer value
echo long2ip($integer_ip); // dotted format
-----------------------
Results are as follows:
-----------------------
2130706432
127.0.0.0
-----------------------
255.255.255.255 (converts to) 4294967295 (and back to) 255.255.255.255
209.65.0.0 (converts to) 3510697984 (and back to) 209.65.0.0
12.0.0.0 (converts to) 201326592 (and back to) 12.0.0.0
1.0.0.0 (converts to) 16777216 (and back to) 1.0.0.0
While i understand that this is a MySQL comment section, it seems that many have the same issue regarding MySQL / PHP IPv4 address handling in databases, and as such have posted this as a way to help those who, like myself, were frustrated with IP addresses that were not converting properly.
where ip='192.168.0.1' is a string
select if(length(CONV(INET_ATON(ip),10,2))<32,LPAD(CONV(INET_ATON(ip),10,2),32,'0'),CONV(INET_ATON(ip),10,2));
so
mysql> SELECT IF(
-> LENGTH( CONV( INET_ATON('192.168.0.1'), 10, 2) ) < 32,
-> LPAD( CONV( INET_ATON('192.168.0.1'), 10, 2), 32, '0'),
-> CONV( INET_ATON('192.168.0.1'), 10, 2)
-> ) as BinaryRep;
1 row in set (0.00 sec)
UNHEX(REPLACE(UUID(),'-',''))
<php>
$query = "SELECT VARIABLE_VALUE FROM information_schema.SESSION_VARIABLES WHERE VARIABLE_NAME = 'SERVER_ID'";
$mysql_server_id = YourImplementedMySQLGetOneResult($query);
$query = "SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'UPTIME'";
$server_startup_time_in_seconds = time() - intval(YourImplementedMySQLGetOneResult($query));
$uuid_short_count = 0;
function uuid_short() {
return ((($mysql_server_id)&255) << 56) + ($server_startup_time_in_seconds << 24) + $uuid_short_count++;
}
</php>
In the end, I guess I am wondering if it would be more efficient to index the reverse of the UUID when using it in it's binary format, or leave it as it is?
So the options I'm interested in are as follows (assumption is that a primary field of BINARY(16) is populated with the function):
CREATE FUNCTION id() RETURNS binary(16) RETURN unhex(REPLACE(UUID(),'-',''));
or
CREATE FUNCTION id() RETURNS binary(16) RETURN unhex(reverse(REPLACE(UUID(),'-','')));
Measuring appears to indicate that the non-reversed function is much, much faster. But I am not sure why that should be.
master> set @safe_uuid := UUID();
master> insert into test.uuid_test values (2, @safe_uuid);
-- The above replicates correctly to slaves even with statement based replication.
See: http://code.openark.org/blog/mysql/making-uuid-and-rand-replication-safe