MySQL :: MySQL 5.7 Reference Manual :: 12.20 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 12.19.3, “MySQL Handling of GROUP BY”.

mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;  
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 4.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 4.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 a value that is not NULL. The converse statement is not true: In some cases,INET_ATON() returns a value other than 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 a value tht si not NULL.

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 23.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.

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'

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.

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