数据库中的表信息
The TABLES
table provides information about tables in databases.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
TABLE_CATALOG | def | |
TABLE_SCHEMA | Table_ ... | |
TABLE_NAME | Table_ ... | |
TABLE_TYPE | ||
ENGINE | Engine | MySQL extension |
VERSION | Version | The version number of the table's .frm file, MySQL extension |
ROW_FORMAT | Row_format | MySQL extension |
TABLE_ROWS | Rows | MySQL extension |
AVG_ROW_LENGTH | Avg_row_length | MySQL extension |
DATA_LENGTH | Data_length | MySQL extension |
MAX_DATA_LENGTH | Max_data_length | MySQL extension |
INDEX_LENGTH | Index_length | MySQL extension |
DATA_FREE | Data_free | MySQL extension |
AUTO_INCREMENT | Auto_increment | MySQL extension |
CREATE_TIME | Create_time | MySQL extension |
UPDATE_TIME | Update_time | MySQL extension |
CHECK_TIME | Check_time | MySQL extension |
TABLE_COLLATION | Collation | MySQL extension |
CHECKSUM | Checksum | MySQL extension |
CREATE_OPTIONS | Create_options | MySQL extension |
TABLE_COMMENT | Comment | MySQL extension |
Notes:
-
Refer to
SHOW TABLE STATUS
for field descriptions. -
TABLE_SCHEMA
andTABLE_NAME
are a single field in aSHOW
display, for exampleTable_in_db1
. -
TABLE_TYPE
should beBASE TABLE
orVIEW
. TheTABLES
table does not listTEMPORARY
tables. -
For partitioned tables, the
ENGINE
column shows the name of the storage engine used by all partitions. (Previously, this column showedPARTITION
for such tables.) -
The
TABLE_ROWS
column isNULL
if the table is in theINFORMATION_SCHEMA
database.For
InnoDB
tables, the row count is only a rough estimate used in SQL optimization. (This is also true if theInnoDB
table is partitioned.) -
The
DATA_FREE
column shows the free space in bytes forInnoDB
tables. -
UPDATE_TIME
displays a timestamp value for the lastUPDATE
,INSERT
, orDELETE
performed onInnoDB
tables that are not partitioned. For MVCC, the timestamp value reflects theCOMMIT
time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from theInnoDB
data dictionary cache.The
UPDATE_TIME
column also shows this information for partitionedInnoDB
tables. -
For partitioned
InnoDB
tables, theCHECK_TIME
column is alwaysNULL
. -
We have nothing for the table's default character set.
TABLE_COLLATION
is close, because collation names begin with a character set name. -
The
CREATE_OPTIONS
column showspartitioned
if the table is partitioned.
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']