MySQL 实验室 因为专注，所以专业。

  • 首页
  • 博客
  • 下载
  • 文档
  • 工具
  • 知识库
  • 培训及服务
  • MySQL 5.1 Reference Manual :: 20 INFORMATION_SCHEMA Tables :: 20.2 The INFORMATION_SCHEMA TABLES Table
    • MySQL 5.1 Reference Manual
    • Preface, Notes, Licenses
    • 1 General Information
    • 2 Installing and Upgrading MySQL
    • 3 Tutorial
    • 4 MySQL Programs
    • 5 MySQL Server Administration
    • 6 Backup and Recovery
    • 7 Optimization
    • 8 Language Structure
    • 9 Internationalization and Localization
    • 10 Data Types
    • 11 Functions and Operators
    • 12 SQL Statement Syntax
    • 13 Storage Engines
    • 14 High Availability and Scalability
    • 15 MySQL Enterprise Monitor
    • 16 Replication
    • 17 MySQL Cluster NDB 6.X/7.X
    • 18 Partitioning
    • 19 Stored Programs and Views
    • 20 INFORMATION_SCHEMA Tables
    • 21 Connectors and APIs
    • 22 Extending MySQL
    • A MySQL 5.1 Frequently Asked Questions
    • B Errors, Error Codes, and Common Problems
    • C MySQL Change History
    • D Restrictions and Limits
    • Index
    • Standard Index
    • C Function Index
    • Command Index
    • Function Index
    • INFORMATION_SCHEMA Index
    • Transaction Isolation Level Index
    • JOIN Types Index
    • Operator Index
    • Option Index
    • Privileges Index
    • SQL Modes Index
    • Status Variable Index
    • Statement/Syntax Index
    • System Variable Index

    20.2. The INFORMATION_SCHEMA TABLES Table

    The TABLES table provides information about tables in databases.

    INFORMATION_SCHEMA Name SHOW Name Remarks
    TABLE_CATALOG   NULL
    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:

    • TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.

    • TABLE_TYPE should be BASE TABLE or VIEW. Currently, the TABLES table does not list TEMPORARY tables.

    • For partitioned tables, beginning with MySQL 5.1.9, the ENGINE column shows the name of the storage engine used by all partitions. (Previously, this column showed PARTITION for such tables.)

    • The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.

      For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

    • Prior to MySQL 5.1.12, MySQL Cluster allocated storage for variable-width columns in 10-page extents of 32 kilobytes each; thus, the DATA_LENGTH for such columns was reported in increments of 320 KB. Beginning with MySQL 5.1.12, the DATA_LENGTH column reflects the true amount of storage for variable-width columns of NDB tables. (Bug#18413)

    • Beginning with MySQL 5.1.28, the DATA_FREE column shows the free space in bytes for InnoDB tables.

    • We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

    • Beginning with MySQL 5.1.9, the CREATE_OPTIONS column shows partitioned 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']