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 alwaysdef
.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 currentlyInnoDB
.VERSION
: Version. The value is10
by default.ROW_FORMAT
: The row format. The value is currentlyCompact
.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 currently0
, 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 currently0
.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 withAUTO_RANDOM
attribute."SHARD_BITS={bit_number}"
: the table is sharded usingSHARD_ROW_ID_BITS={bit_number}
.- NULL: the table is a system table or view, and thus cannot be sharded.