_tidb_rowid
_tidb_rowid is a hidden system column automatically generated by TiDB. For tables that do not use a clustered index, it serves as the internal row ID of the table. You cannot declare or modify this column in the table schema, but you can reference it in SQL when the table uses _tidb_rowid as its internal row ID.
In the current implementation, _tidb_rowid is an extra BIGINT NOT NULL column automatically managed by TiDB.
When _tidb_rowid is available
TiDB uses _tidb_rowid to identify each row when a table does not use a clustered primary key as the unique row identifier. In practice, this means that the following types of tables use _tidb_rowid:
- Tables without primary keys
- Tables with primary keys that are explicitly defined as
NONCLUSTERED
_tidb_rowid is not available for tables that use a clustered index (that is, tables whose primary key is defined as CLUSTERED, regardless of whether it is a single-column or composite primary key).
The following example shows the difference:
CREATE TABLE t1 (a INT, b VARCHAR(20));
CREATE TABLE t2 (id BIGINT PRIMARY KEY NONCLUSTERED, a INT);
CREATE TABLE t3 (id BIGINT PRIMARY KEY CLUSTERED, a INT);
For t1 and t2, you can query _tidb_rowid because these tables do not use a clustered index as the row identifier:
SELECT _tidb_rowid, a, b FROM t1;
SELECT _tidb_rowid, id, a FROM t2;
For t3, _tidb_rowid is unavailable because the clustered primary key is already the row identifier:
SELECT _tidb_rowid, id, a FROM t3;
ERROR 1054 (42S22): Unknown column '_tidb_rowid' in 'field list'
Read _tidb_rowid
For tables that use _tidb_rowid, you can query _tidb_rowid in SELECT statements. This is useful for tasks such as pagination, troubleshooting, and batch processing.
Example:
CREATE TABLE t (a INT, b VARCHAR(20));
INSERT INTO t VALUES (1, 'x'), (2, 'y');
SELECT _tidb_rowid, a, b FROM t ORDER BY _tidb_rowid;
+-------------+---+---+
| _tidb_rowid | a | b |
+-------------+---+---+
| 1 | 1 | x |
| 2 | 2 | y |
+-------------+---+---+
To view the next value that TiDB will allocate for the row ID, use SHOW TABLE ... NEXT_ROW_ID:
SHOW TABLE t NEXT_ROW_ID;
+-----------------------+------------+-------------+--------------------+-------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE |
+-----------------------+------------+-------------+--------------------+-------------+
| update_doc_rowid_test | t | _tidb_rowid | 30001 | _TIDB_ROWID |
+-----------------------+------------+-------------+--------------------+-------------+
Write _tidb_rowid
By default, TiDB does not allow INSERT, REPLACE, or UPDATE statements to write _tidb_rowid directly.
INSERT INTO t(_tidb_rowid, a, b) VALUES (101, 4, 'w');
ERROR 1105 (HY000): insert, update and replace statements for _tidb_rowid are not supported
If you need to preserve the original row IDs during data import or migration, enable the tidb_opt_write_row_id system variable first:
SET @@tidb_opt_write_row_id = ON;
INSERT INTO t(_tidb_rowid, a, b) VALUES (100, 3, 'z');
SET @@tidb_opt_write_row_id = OFF;
SELECT _tidb_rowid, a, b FROM t WHERE _tidb_rowid = 100;
+-------------+---+---+
| _tidb_rowid | a | b |
+-------------+---+---+
| 100 | 3 | z |
+-------------+---+---+
Restrictions
- You cannot create a user column named
_tidb_rowid. - You cannot rename an existing user column to
_tidb_rowid. _tidb_rowidis an internal column in TiDB. Do not treat it as a business primary key or a long-term identifier.- On partitioned non-clustered tables,
_tidb_rowidvalues are not guaranteed to be unique across partitions. After you executeEXCHANGE PARTITION, different partitions can contain rows with the same_tidb_rowidvalue. - Whether
_tidb_rowidexists depends on the table schema. For tables with clustered indexes, use the primary key as the row identifier.
Address hotspot issues
For tables that use _tidb_rowid, TiDB allocates row IDs in increasing order by default. In write-intensive workloads, this can create write hotspots.
To mitigate this issue (for tables that rely on _tidb_rowid as the row ID), consider using SHARD_ROW_ID_BITS to distribute row IDs more evenly, and use PRE_SPLIT_REGIONS to pre-split Regions when necessary.
Example:
CREATE TABLE t (
id BIGINT PRIMARY KEY NONCLUSTERED,
c INT
) SHARD_ROW_ID_BITS = 4;
SHARD_ROW_ID_BITS applies only to tables that use _tidb_rowid and does not apply to tables with clustered indexes.
Related statements and variables
SHOW TABLE NEXT_ROW_ID: shows the next row ID that TiDB will allocateSHARD_ROW_ID_BITS: shards implicit row IDs to reduce hotspotsClustered Indexes: explains when a table uses the primary key instead of_tidb_rowidtidb_opt_write_row_id: controls whether writes to_tidb_rowidare allowed