Miscellaneous Functions

TiDB supports most of the miscellaneous functions available in MySQL 8.0.

Supported functions

ANY_VALUE()

The ANY_VALUE() function returns any value from a group of values. Typically, it is used in scenarios where you need to include non-aggregated columns in your SELECT statement along with a GROUP BY clause.

CREATE TABLE fruits (id INT PRIMARY KEY, name VARCHAR(255));
Query OK, 0 rows affected (0.14 sec)
INSERT INTO fruits VALUES (1,'apple'),(2,'apple'),(3,'pear'),(4,'banana'),(5, 'pineapple');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
SELECT id,name FROM fruits GROUP BY name;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.fruits.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT ANY_VALUE(id),GROUP_CONCAT(id),name FROM fruits GROUP BY name;
+---------------+------------------+-----------+
| ANY_VALUE(id) | GROUP_CONCAT(id) | name      |
+---------------+------------------+-----------+
|             1 | 1,2              | apple     |
|             3 | 3                | pear      |
|             4 | 4                | banana    |
|             5 | 5                | pineapple |
+---------------+------------------+-----------+
4 rows in set (0.00 sec)

In the preceding example, TiDB returns an error for the first SELECT statement because the id column is non-aggregated and not included in the GROUP BY clause. To address the issue, the second SELECT query uses ANY_VALUE() to get any value from each group and uses GROUP_CONCAT() to concatenate all values of the id column within each group into a single string. This approach enables you to get one value from each group and all values of the group without changing the SQL mode for non-aggregated columns.

BIN_TO_UUID()

BIN_TO_UUID() and UUID_TO_BIN() can be used to convert between a textual format UUID and a binary format. Both functions accept two arguments.

• The first argument specifies the value to be converted.
• The second argument (optional) controls the ordering of the fields in the binary format.

SET @a := UUID();
Query OK, 0 rows affected (0.00 sec)
SELECT @a;
+--------------------------------------+
| @a                                   |
+--------------------------------------+
| 9a17b457-eb6d-11ee-bacf-5405db7aad56 |
+--------------------------------------+
1 row in set (0.00 sec)
SELECT UUID_TO_BIN(@a);
+------------------------------------+
| UUID_TO_BIN(@a)                    |
+------------------------------------+
| 0x9A17B457EB6D11EEBACF5405DB7AAD56 |
+------------------------------------+
1 row in set (0.00 sec)
SELECT BIN_TO_UUID(0x9A17B457EB6D11EEBACF5405DB7AAD56);
+-------------------------------------------------+
| BIN_TO_UUID(0x9A17B457EB6D11EEBACF5405DB7AAD56) |
+-------------------------------------------------+
| 9a17b457-eb6d-11ee-bacf-5405db7aad56            |
+-------------------------------------------------+
1 row in set (0.00 sec)
SELECT UUID_TO_BIN(@a, 1);
+----------------------------------------+
| UUID_TO_BIN(@a, 1)                     |
+----------------------------------------+
| 0x11EEEB6D9A17B457BACF5405DB7AAD56     |
+----------------------------------------+
1 row in set (0.00 sec)
SELECT BIN_TO_UUID(0x11EEEB6D9A17B457BACF5405DB7AAD56, 1);
+----------------------------------------------------+
| BIN_TO_UUID(0x11EEEB6D9A17B457BACF5405DB7AAD56, 1) |
+----------------------------------------------------+
| 9a17b457-eb6d-11ee-bacf-5405db7aad56               |
+----------------------------------------------------+
1 row in set (0.00 sec)

See also UUID() and Best practices for UUID.

DEFAULT()

The DEFAULT() function is used to get the default value of a column.

CREATE TABLE t1 (id INT PRIMARY KEY, c1 INT DEFAULT 5);
Query OK, 0 rows affected (0.15 sec)
INSERT INTO t1 VALUES (1, 1);
Query OK, 1 row affected (0.01 sec)
UPDATE t1 SET c1=DEFAULT(c1)+3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
TABLE t1;
+----+------+
| id | c1   |
+----+------+
|  1 |    8 |
+----+------+
1 row in set (0.00 sec)

In the preceding example, the UPDATE statement sets the value of the c1 column to the default value of the column (which is 5) plus 3, resulting in a new value of 8.

GROUPING()

See GROUP BY modifiers.

INET_ATON()

The INET_ATON() function converts an IPv4 address in dotted-quad notation into a binary version that can be stored efficiently.

SELECT INET_ATON('127.0.0.1');

+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)

INET_NTOA()

The INET_NTOA() function converts a binary IPv4 address into a dotted-quad notation.

SELECT INET_NTOA(2130706433);

+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

INET6_ATON()

The INET6_ATON() function is similar to INET_ATON(), but INET6_ATON() can also handle IPv6 addresses.

SELECT INET6_ATON('::1');

+--------------------------------------+
| INET6_ATON('::1')                    |
+--------------------------------------+
| 0x00000000000000000000000000000001   |
+--------------------------------------+
1 row in set (0.00 sec)

INET6_NTOA()

The INET6_NTOA() function is similar to INET_NTOA(), but INET6_NTOA() can also handle IPv6 addresses.

SELECT INET6_NTOA(0x00000000000000000000000000000001);

+------------------------------------------------+
| INET6_NTOA(0x00000000000000000000000000000001) |
+------------------------------------------------+
| ::1                                            |
+------------------------------------------------+
1 row in set (0.00 sec)

IS_IPV4()

The IS_IPV4() function tests whether the given argument is an IPv4 address or not.

SELECT IS_IPV4('127.0.0.1');

+----------------------+
| IS_IPV4('127.0.0.1') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

SELECT IS_IPV4('300.0.0.1');

+----------------------+
| IS_IPV4('300.0.0.1') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

IS_IPV4_COMPAT()

The IS_IPV4_COMPAT() function tests whether the given argument is an IPv4-compatible address.

SELECT IS_IPV4_COMPAT(INET6_ATON('::127.0.0.1'));

+-------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::127.0.0.1')) |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

IS_IPV4_MAPPED()

The IS_IPV4_MAPPED() function tests whether the given argument is an IPv4-mapped address.

SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:127.0.0.1'));

+------------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::ffff:127.0.0.1')) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.00 sec)

IS_IPV6()

The IS_IPV6() function tests whether the given argument is an IPv6 address.

SELECT IS_IPV6('::1');

+----------------+
| IS_IPV6('::1') |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

IS_UUID()

The IS_UUID() function tests whether the given argument is a UUID.

SELECT IS_UUID('eb48c08c-eb71-11ee-bacf-5405db7aad56');

+-------------------------------------------------+
| IS_UUID('eb48c08c-eb71-11ee-bacf-5405db7aad56') |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
1 row in set (0.00 sec)

NAME_CONST()

The NAME_CONST() function is used to name columns. It is recommended to use column aliases instead.

SELECT NAME_CONST('column name', 'value') UNION ALL SELECT 'another value';

+---------------+
| column name   |
+---------------+
| another value |
| value         |
+---------------+
2 rows in set (0.00 sec)

The preceding statement uses NAME_CONST() and the following statement uses the recommended way of aliasing columns.

SELECT 'value' AS 'column name' UNION ALL SELECT 'another value';

+---------------+
| column name   |
+---------------+
| value         |
| another value |
+---------------+
2 rows in set (0.00 sec)

SLEEP()

The SLEEP() function is used to pause the execution of queries for a specified number of seconds.

SELECT SLEEP(1.5);

+------------+
| SLEEP(1.5) |
+------------+
|          0 |
+------------+
1 row in set (1.50 sec)

UUID()

The UUID() function returns a universally unique identifier (UUID) version 1 as defined in RFC 4122.

SELECT UUID();

+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| cb4d5ae6-eb6b-11ee-bacf-5405db7aad56 |
+--------------------------------------+
1 row in set (0.00 sec)

See also Best practices for UUID.

UUID_TO_BIN

See BIN_TO_UUID().

VALUES()

The VALUES(col_name) function is used to reference the value of a specific column in the ON DUPLICATE KEY UPDATE clause of an INSERT statement.

CREATE TABLE t1 (id INT PRIMARY KEY, c1 INT);
Query OK, 0 rows affected (0.17 sec)
INSERT INTO t1 VALUES (1,51),(2,52),(3,53),(4,54),(5,55);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
INSERT INTO t1 VALUES(2,22),(4,44) ON DUPLICATE KEY UPDATE c1=VALUES(id)+100;
Query OK, 4 rows affected (0.01 sec)
Records: 2  Duplicates: 2  Warnings: 0
TABLE t1;
+----+------+
| id | c1   |
+----+------+
|  1 |   51 |
|  2 |  102 |
|  3 |   53 |
|  4 |  104 |
|  5 |   55 |
+----+------+
5 rows in set (0.00 sec)

Unsupported functions