Skip to content

Latest commit

 

History

History
68 lines (52 loc) · 2.19 KB

sql-statement-show-table-next-rowid.md

File metadata and controls

68 lines (52 loc) · 2.19 KB
Raw
title summary aliases
SHOW TABLE NEXT_ROW_ID
Learn the usage of `SHOW TABLE NEXT_ROW_ID` in TiDB.
/docs/dev/sql-statements/sql-statement-show-table-next-rowid/

SHOW TABLE NEXT_ROW_ID

SHOW TABLE NEXT_ROW_ID is used to show the details of some special columns of a table, including:

  • AUTO_INCREMENT column automatically created by TiDB, namely, _tidb_rowid column.
  • AUTO_INCREMENT column created by users.
  • AUTO_RANDOM column created by users.
  • SEQUENCE created by users.

Synopsis

ShowTableNextRowIDStmt ::=
    "SHOW" "TABLE" (SchemaName ".")? TableName "NEXT_ROW_ID"

Examples

For newly created tables, NEXT_GLOBAL_ROW_ID is 1 because no Row ID is allocated.

CREATE TABLE t(a int);
Query OK, 0 rows affected (0.06 sec)
SHOW TABLE t NEXT_ROW_ID;
+---------+------------+-------------+--------------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID |
+---------+------------+-------------+--------------------+
| test    | t          | _tidb_rowid |                  1 |
+---------+------------+-------------+--------------------+
1 row in set (0.00 sec)

Data have been written to the table. The TiDB server that inserts the data allocates and caches 30000 IDs at once. Thus, NEXT_GLOBAL_ROW_ID is 30001 now. The number of IDs is controlled by AUTO_ID_CACHE.

INSERT INTO t VALUES (), (), ();
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
SHOW TABLE t NEXT_ROW_ID;
+---------+------------+-------------+--------------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID |
+---------+------------+-------------+--------------------+
| test    | t          | _tidb_rowid |              30001 |
+---------+------------+-------------+--------------------+
1 row in set (0.00 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also