Mysql Information Database: Information_schema

1. Information_schemainformation_schema is the mysql information database. You can use this database to view the databases, tables, permissions, and other information under mysql. This database is generated by default in the database. Information_schema is actually a view, not a real table. You can view all databases on the mysql terminal.

mysql> show databases;

2. view the following tables in the contained tables

mysql> use information_schema;    Database changedmysql> show tables;

3. SCHEMATA contains information about all databases

select * from SCHEMATA;

The information of all databases is listed, which is consistent with show databases. 4. The TABLESinformation_schema.tables table is used to save information about all tables in the database.

TABLE_SCHEMA: Database Name TABLE_NAME: TABLE Name TABLE_TYPE: base table is the basic data TABLE, and VIEW is the view engine: storage ENGINE InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: Number of TABLE rows. For InnoDB tables, in SQL optimization, the row count is only an approximate estimate. AVG_ROW_LENGTH: Average row length DATA_LENGTH: data storage size MAX_DATA_LENGTH: 0 INDEX_LENGTH: Index size DATA_FREE: innodb table display space AUTO_INCREMENT: auto-increment id CREATE_TIME: Creation Time TABLE_COLLATION

Queries the space occupied by a table.

select concat(round(((INDEX_LENGTH+DATA_LENGTH)/1024/1024),2),'MB') as data from TABLES where table_schema='my_db' and table_name='my_table';

The size of indexes and total data occupied by my_db.my_table. 5. The COLUMNScolumns table contains information about fields in all databases. Information including the database, table, verification code, and permissions of fields 6. TRIGGERS information about the mysql trigger program.