MySQL :: MySQL 8.4 Reference Manual :: 14.23 Miscellaneous Functions (original) (raw)

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, the address value for rows within each name group is nondeterministic. There are multiple ways to cause MySQL to accept the query: 

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;  
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()](miscellaneous-functions.html#function%5Fany-value) is not an aggregate function, unlike functions such as[SUM()](aggregate-functions.html#function%5Fsum) or[COUNT()](aggregate-functions.html#function%5Fcount). It simply acts to suppress the test for nondeterminism.  
SELECT age FROM t GROUP BY age-1;

To cause MySQL to accept the query, useANY_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 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, usingANY_VALUE() for the effectively functionally dependent column may make sense.
For additional discussion, seeSection 14.19.3, “MySQL Handling of GROUP BY”.

For usage examples and information about time-part swapping, see the UUID_TO_BIN() function description.

mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;  
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 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: 

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 super-aggregate rows from those in regular grouped rows, useGROUPING(), which returns 1 only for super-aggregate NULL 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(): 

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 |  
+-----------+-----------+----------+  
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(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 howGROUPING() 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, theGROUPING() 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: 

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: 

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  
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(), seeSection 14.19.2, “GROUP BY Modifiers”.

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.

mysql> SELECT INET_NTOA(167773449);  
        -> '10.0.5.9'  
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. 

INET6_ATON(INET_NTOA(expr))

For example: 

mysql> SELECT HEX(INET6_ATON(INET_NTOA(167773449)));  
        -> '0A000509'

If INET6_ATON() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

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'

If INET6_NTOA() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

mysql> SELECT IS_IPV4('10.0.5.9'), IS_IPV4('10.0.5.256');  
        -> 1, 0

For a given argument, ifIS_IPV4() returns 1,INET_ATON() (andINET6_ATON()) returns 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 as IS_IPV4() about checking IPv4 addresses.

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  
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  
mysql> SELECT IS_IPV6('10.0.5.9'), IS_IPV6('::1');  
        -> 0, 1

For a given argument, ifIS_IPV6() returns 1,INET6_ATON() returns non-NULL.

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 theUUID() 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 |  
+-----------------+  
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 inSection 27.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 15.2.13, “SELECT Statement”, for more information about column aliases.

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: 

mysql> SELECT SLEEP(1000);  
+-------------+  
| SLEEP(1000) |  
+-------------+  
|           1 |  
+-------------+  
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: 

mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);  
ERROR 1317 (70100): Query execution was interrupted  
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 whenbinlog_format is set toSTATEMENT.

mysql> SELECT UUID();  
        -> '6ccd780c-baba-1026-9564-5b8c656024db'

To convert between string and binary UUID values, use theUUID_TO_BIN() andBIN_TO_UUID() functions. To check whether a string is a valid UUID value, use theIS_UUID() function.
This function is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_format is set toSTATEMENT.

  (server_id & 255) << 56  
+ (server_startup_time_in_seconds << 24)  
+ incremented_variable++;  
mysql> SELECT UUID_SHORT();  
        -> 92395783831158784

Note
UUID_SHORT() does not work with statement-based replication.

Time-part swapping assumes the use of UUID version 1 values, such as are generated by theUUID() 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 byUUID_TO_BIN() to a string UUID, use BIN_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 is not 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 |  
+--------------------------------------+

If UUID_TO_BIN() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);