Information Functions

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

TiDB supported MySQL functions

BENCHMARK()

The BENCHMARK() function executes the given expression a specified number of times.

Syntax:

BENCHMARK(count, expression)

• count: the number of times the expression to be executed.
• expression: the expression to be executed repeatedly.

Example:

SELECT BENCHMARK(5, SLEEP(2));

+------------------------+
| BENCHMARK(5, SLEEP(2)) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (10.00 sec)

CONNECTION_ID()

The CONNECTION_ID() function returns the ID of the connection. Based on the value of the enable-32bits-connection-id configuration item for TiDB, this function returns a 32-bit or 64-bit connection ID.

If enable-global-kill is enabled, the connection ID can be used to kill queries across multiple TiDB instances of the same cluster.

SELECT CONNECTION_ID();

+-----------------+
| CONNECTION_ID() |
+-----------------+
|       322961414 |
+-----------------+
1 row in set (0.00 sec)

CURRENT_ROLE()

The CURRENT_ROLE() function returns the current role for the current session.

SELECT CURRENT_ROLE();

+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

CURRENT_USER()

The CURRENT_USER() function returns the account that is used in the current session.

SELECT CURRENT_USER();

+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

DATABASE()

The DATABASE() function returns the database schema that the current session is using.

SELECT DATABASE();

+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

FOUND_ROWS()

The FOUND_ROWS() function returns the number of rows in the result set of the last executed SELECT statement.

SELECT 1 UNION ALL SELECT 2;

+------+
| 1    |
+------+
|    2 |
|    1 |
+------+
2 rows in set (0.01 sec)

SELECT FOUND_ROWS();

+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

LAST_INSERT_ID()

The LAST_INSERT_ID() function returns the ID of the last inserted row in a table that contains an AUTO_INCREMENT or AUTO_RANDOM column.

CREATE TABLE t1(id SERIAL);
Query OK, 0 rows affected (0.17 sec)

INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.03 sec)

INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.00 sec)

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

TABLE t1;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

ROW_COUNT()

The ROW_COUNT() function returns the number of affected rows.

CREATE TABLE t1(id BIGINT UNSIGNED PRIMARY KEY AUTO_RANDOM);
Query OK, 0 rows affected, 1 warning (0.16 sec)

INSERT INTO t1() VALUES (),(),();
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

SCHEMA()

The SCHEMA() function is a synonym for DATABASE().

SESSION_USER()

The SESSION_USER() function is a synonym for USER().

SYSTEM_USER()

The SYSTEM_USER() function is a synonym for USER().

USER()

The USER() function returns the user of the current connection. This might differ slightly from the output of CURRENT_USER(), as USER() displays the actual IP address instead of a wildcard.

SELECT USER(), CURRENT_USER();

+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@127.0.0.1 | root@%         |
+----------------+----------------+
1 row in set (0.00 sec)

VERSION()

The VERSION() function returns the TiDB version in a format that is compatible with MySQL. To get a more detailed result, you can use the TIDB_VERSION() function.

SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.00 sec)

SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

The preceding example is from TiDB v7.5.1, which identifies itself as MySQL 8.0.11.

TiDB specific functions

The following function is only supported by TiDB, and there is no equivalent function in MySQL.

Unsupported functions

• CHARSET()
• COERCIBILITY()
• COLLATION()
• ICU_VERSION()
• ROLES_GRAPHML()