TABLES

The TABLES table provides information about tables in databases:

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

The following statements are equivalent:

  1. SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  2.   WHERE table_schema = 'db_name'
  3.   [AND table_name LIKE 'wild']
  5. SHOW TABLES
  6.   FROM db_name
  7.   [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.
  • TIDB_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.