This function is useful for GROUP BY
queries when the
ONLY_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 the ONLY_FULL_GROUP_BY
SQL mode.

For example, if name is a nonindexed
column, the following query fails with
ONLY_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 among
GROUP BY columns nor functionally dependent
on them. As a result, the address value for
rows within each name 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 unique NOT NULL column. This
enables MySQL to determine that address
is functionally dependent on name; that
is, address is uniquely determined by
name. (This technique is inapplicable
if NULL must be permitted as a valid
name value.)

In this case, MySQL ignores the nondeterminism of
address values within each
name 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 as
SUM() or
COUNT(). It simply acts to
suppress the test for nondeterminism.

ANY_VALUE() is also useful if
functional dependence exists between columns but MySQL cannot
determine it. The following query is valid because
age is functionally dependent on the
grouping column age-1, but MySQL cannot
tell that and rejects the query with
ONLY_FULL_GROUP_BY enabled:

Without GROUP BY, there is a single group
and it is nondeterministic which name 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, using
ANY_VALUE() for the effectively
functionally dependent column may make sense.

BIN_TO_UUID() is the inverse of
UUID_TO_BIN(). It converts a
binary UUID to a string UUID and returns the result. The
binary value should be a UUID as a
VARBINARY(16) value. The return
value is a utf8 string of five hexadecimal
numbers separated by dashes. (For details about this format,
see the UUID() function
description.) If the UUID argument is NULL,
the return value is NULL. If any argument
is invalid, an error occurs.

For GROUP BY queries that include a
WITH ROLLUP modifier, the
ROLLUP operation produces super-aggregate
output rows where NULL represents the set
of all values. The GROUPING()
function enables you to distinguish NULL
values for super-aggregate rows from NULL
values in regular grouped rows.

Each argument to GROUPING()
must be an expression that exactly matches an expression in
the GROUP BY clause. The expression cannot
be a positional specifier. For each expression,
GROUPING() produces 1 if the
expression value in the current row is a
NULL representing a super-aggregate value.
Otherwise, GROUPING() produces
0, indicating that the expression value is a
NULL for a regular result row or is not
NULL.

Suppose that table t1 contains these rows,
where NULL indicates something like
“other” or “unknown”:

The result contains NULL values, but those
do not represent super-aggregate rows because the query does
not include WITH ROLLUP.

Adding WITH ROLLUP produces super-aggregate
summary rows containing additional NULL
values. However, without comparing this result to the previous
one, it is not easy to see which NULL
values occur in super-aggregate rows and which occur in
regular grouped rows:

GROUPING() permits multiple
expression arguments. In this case, the
GROUPING() 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(expr1,
expr2,
expr3) is evaluated like
this:

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:

With multiple expression arguments, the
GROUPING() return value is
nonzero if any expression represents a super-aggregate value.
Multiple-argument GROUPING()
syntax thus provides a simpler way to write the earlier query
that returned only super-aggregate rows, by using a single
multiple-argument GROUPING()
call rather than multiple single-argument calls:

Do not use subquery GROUP BY
expressions as GROUPING()
arguments because matching might fail. For example,
matching fails for this query:

mysql> SELECT GROUPING((SELECT MAX(name) FROM t1))FROM t1GROUP 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 a HAVING clause as
GROUPING() arguments. Due
to differences between when the optimizer evaluates
GROUP BY and HAVING,
matching may succeed but
GROUPING() 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 the
HAVING clause as a whole and returns 0.
To check whether a query such as this is affected, use
EXPLAIN and look for
Impossible having in the
Extra column.

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() returns
NULL 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.

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() returns
NULL if it does not understand its
argument.

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 and VARBINARY(4) for
IPv4 addresses. If the argument is not a valid address,
INET6_ATON() returns
NULL.

The following examples use
HEX() to display the
INET6_ATON() result in
printable form:

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 or
fe80::3%eth0.

A trailing network mask is not permitted, as in
2001:45f:3:ba::/64 or
198.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 in
198.51.100.2:8080. Hexadecimal numbers
in address components are not permitted, as in
198.0xa0.1.2. Octal numbers are not
supported: 198.51.010.1 is treated as
198.51.10.1, not
198.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 an
INT value to an IPv6 address
represented in numeric form as a
VARBINARY value, use this
expression:

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() returns
NULL.

As implied by the preceding remarks,
IS_IPV4() is more strict than
INET_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, use
INET6_ATON() to convert IPv4
addresses to internal form and check for a
NULL result (which indicates an invalid
address). INET6_ATON() is
equally strong as IS_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.

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.

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 is NULL.

“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:

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.

On a multithreaded slave, the function waits until expiry of
the limit set by the
slave_checkpoint_group or
slave_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
when timeout seconds have elapsed.
timeout must be greater than 0; a
zero or negative timeout means no
timeout.

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 is
NULL or negative,
SLEEP() produces a warning, or
an error in strict SQL mode.

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.

Warning

Although 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 a utf8
string of five hexadecimal numbers in
aaaaaaaa-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'

To convert between string and binary UUID values, use the
UUID_TO_BIN() and
BIN_TO_UUID() functions. To
check whether a string is a valid UUID value, use the
IS_UUID() function.

This function is unsafe for statement-based replication. A
warning is logged if you use this function when
binlog_format is set to
STATEMENT.

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 the
UUID() function and have
different uniqueness properties. The value of
UUID_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 servers

You 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

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 a
VARBINARY(16) value. If the
UUID argument is NULL, the return value is
NULL. If any argument is invalid, an error
occurs.

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 the
UUID() 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():

To convert a binary UUID returned by
UUID_TO_BIN() to a string UUID,
use BIN_TO_UUID(). If you
produce a binary UUID by calling
UUID_TO_BIN() with a second
argument of 1 to swap time parts, you should also pass a
second argument of 1 to
BIN_TO_UUID() to unswap the
time parts when converting the binary UUID back to a string
UUID: