Important

You are viewing the documentation of TiDB v6.0 (DMR). PingCAP does not provide bug fixes for v6.0. Any bugs will be fixed in future releases.

For general purposes, please use the latest LTS version of the TiDB database.

TABLES

The TABLES table provides information about tables in databases:

USE information_schema;
DESC tables;
+---------------------------+---------------+------+------+----------+-------+
| Field                     | Type          | Null | Key  | Default  | Extra |
+---------------------------+---------------+------+------+----------+-------+
| TABLE_CATALOG             | varchar(512)  | YES  |      | NULL     |       |
| TABLE_SCHEMA              | varchar(64)   | YES  |      | NULL     |       |
| TABLE_NAME                | varchar(64)   | YES  |      | NULL     |       |
| TABLE_TYPE                | varchar(64)   | YES  |      | NULL     |       |
| ENGINE                    | varchar(64)   | YES  |      | NULL     |       |
| VERSION                   | bigint(21)    | YES  |      | NULL     |       |
| ROW_FORMAT                | varchar(10)   | YES  |      | NULL     |       |
| TABLE_ROWS                | bigint(21)    | YES  |      | NULL     |       |
| AVG_ROW_LENGTH            | bigint(21)    | YES  |      | NULL     |       |
| DATA_LENGTH               | bigint(21)    | YES  |      | NULL     |       |
| MAX_DATA_LENGTH           | bigint(21)    | YES  |      | NULL     |       |
| INDEX_LENGTH              | bigint(21)    | YES  |      | NULL     |       |
| DATA_FREE                 | bigint(21)    | YES  |      | NULL     |       |
| AUTO_INCREMENT            | bigint(21)    | YES  |      | NULL     |       |
| CREATE_TIME               | datetime      | YES  |      | NULL     |       |
| UPDATE_TIME               | datetime      | YES  |      | NULL     |       |
| CHECK_TIME                | datetime      | YES  |      | NULL     |       |
| TABLE_COLLATION           | varchar(32)   | NO   |      | utf8_bin |       |
| CHECKSUM                  | bigint(21)    | YES  |      | NULL     |       |
| CREATE_OPTIONS            | varchar(255)  | YES  |      | NULL     |       |
| TABLE_COMMENT             | varchar(2048) | YES  |      | NULL     |       |
| TIDB_TABLE_ID             | bigint(21)    | YES  |      | NULL     |       |
| TIDB_ROW_ID_SHARDING_INFO | varchar(255)  | YES  |      | NULL     |       |
+---------------------------+---------------+------+------+----------+-------+
23 rows in set (0.00 sec)
SELECT * FROM tables WHERE table_schema='mysql' AND table_name='user'\G
*************************** 1. row ***************************
            TABLE_CATALOG: def
             TABLE_SCHEMA: mysql
               TABLE_NAME: user
               TABLE_TYPE: BASE TABLE
                   ENGINE: InnoDB
                  VERSION: 10
               ROW_FORMAT: Compact
               TABLE_ROWS: 0
           AVG_ROW_LENGTH: 0
              DATA_LENGTH: 0
          MAX_DATA_LENGTH: 0
             INDEX_LENGTH: 0
                DATA_FREE: 0
           AUTO_INCREMENT: NULL
              CREATE_TIME: 2020-07-05 09:25:51
              UPDATE_TIME: NULL
               CHECK_TIME: NULL
          TABLE_COLLATION: utf8mb4_bin
                 CHECKSUM: NULL
           CREATE_OPTIONS: 
            TABLE_COMMENT: 
            TIDB_TABLE_ID: 5
TIDB_ROW_ID_SHARDING_INFO: NULL
1 row in set (0.00 sec)

The following statements are equivalent:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']

The description of columns in the TABLES table is as follows:

  • TABLE_CATALOG: The name of the catalog which the table belongs to. The value is always def.
  • TABLE_SCHEMA: The name of the schema which the table belongs to.
  • TABLE_NAME: The name of the table.
  • TABLE_TYPE: The type of the table.
  • ENGINE: The type of the storage engine. The value is currently InnoDB.
  • VERSION: Version. The value is 10 by default.
  • ROW_FORMAT: The row format. The value is currently Compact.
  • TABLE_ROWS: The number of rows in the table in statistics.
  • AVG_ROW_LENGTH: The average row length of the table. AVG_ROW_LENGTH = DATA_LENGTH / TABLE_ROWS.
  • DATA_LENGTH: Data length. DATA_LENGTH = TABLE_ROWS * the sum of storage lengths of the columns in the tuple. The replicas of TiKV are not taken into account.
  • MAX_DATA_LENGTH: The maximum data length. The value is currently 0, which means the data length has no upper limit.
  • INDEX_LENGTH: The index length. INDEX_LENGTH = TABLE_ROWS * the sum of lengths of the columns in the index tuple. The replicas of TiKV are not taken into account.
  • DATA_FREE: Data fragment. The value is currently 0.
  • AUTO_INCREMENT: The current step of the auto- increment primary key.
  • CREATE_TIME: The time at which the table is created.
  • UPDATE_TIME: The time at which the table is updated.
  • CHECK_TIME: The time at which the table is checked.
  • TABLE_COLLATION: The collation of strings in the table.
  • CHECKSUM: Checksum.
  • CREATE_OPTIONS: Creates options.
  • TABLE_COMMENT: The comments and notes of the table.

Most of the information in the table is the same as MySQL. Only two columns are newly defined by TiDB:

  • TIDB_TABLE_ID: to indicate the internal ID of a table. This ID is unique in a TiDB cluster.
  • TIDDB_ROW_ID_SHARDING_INFO: to indicate the sharding type of a table. The possible values are as follows:
    • "NOT_SHARDED": the table is not sharded.
    • "NOT_SHARDED(PK_IS_HANDLE)": the table that defines an integer Primary Key as its row id is not sharded.
    • "PK_AUTO_RANDOM_BITS={bit_number}": the table that defines an integer Primary Key as its row id is sharded because the Primary Key is assigned with AUTO_RANDOM attribute.
    • "SHARD_BITS={bit_number}": the table is sharded using SHARD_ROW_ID_BITS={bit_number}.
    • NULL: the table is a system table or view, and thus cannot be sharded.
Was this page helpful?