MySQL Study -- MySQL schema_information database, mysql database schema

MySQL Study -- MySQL schema_information database, mysql database schema

MySQL Study-MySQL schema_information Database

The information_schema database is generated after mysql 5.0. A virtual database does not exist physically.

The information_schema database is similar to the "Data Dictionary" and provides a way to access database metadata, that is, Data. For example, database name or table name, column type, and access permission (more detailed access method ).

Case:

Mysql> show databases;

+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || prod               || test               |+--------------------+5 rows in set (0.00 sec)

Access information_schema:
Mysql> use information_schema;
Database changed
Mysql> show tables;

+---------------------------------------+| Tables_in_information_schema          |+---------------------------------------+| CHARACTER_SETS                        || COLLATIONS                            || COLLATION_CHARACTER_SET_APPLICABILITY || COLUMNS                               || COLUMN_PRIVILEGES                     || ENGINES                               || EVENTS                                || FILES                                 || GLOBAL_STATUS                         || GLOBAL_VARIABLES                      || KEY_COLUMN_USAGE                      || OPTIMIZER_TRACE                       || PARAMETERS                            || PARTITIONS                            || PLUGINS                               || PROCESSLIST                           || PROFILING                             || REFERENTIAL_CONSTRAINTS               || ROUTINES                              || SCHEMATA                              || SCHEMA_PRIVILEGES                     || SESSION_STATUS                        || SESSION_VARIABLES                     || STATISTICS                            || TABLES                                || TABLESPACES                           || TABLE_CONSTRAINTS                     || TABLE_PRIVILEGES                      || TRIGGERS                              || USER_PRIVILEGES                       || VIEWS                                 || INNODB_LOCKS                          || INNODB_TRX                            || INNODB_SYS_DATAFILES                  || INNODB_LOCK_WAITS                     || INNODB_SYS_TABLESTATS                 || INNODB_CMP                            || INNODB_METRICS                        || INNODB_CMP_RESET                      || INNODB_CMP_PER_INDEX                  || INNODB_CMPMEM_RESET                   || INNODB_FT_DELETED                     || INNODB_BUFFER_PAGE_LRU                || INNODB_SYS_FOREIGN                    || INNODB_SYS_COLUMNS                    || INNODB_SYS_INDEXES                    || INNODB_FT_DEFAULT_STOPWORD            || INNODB_SYS_FIELDS                     || INNODB_CMP_PER_INDEX_RESET            || INNODB_BUFFER_PAGE                    || INNODB_CMPMEM                         || INNODB_FT_INDEX_TABLE                 || INNODB_FT_BEING_DELETED               || INNODB_SYS_TABLESPACES                || INNODB_FT_INDEX_CACHE                 || INNODB_SYS_FOREIGN_COLS               || INNODB_SYS_TABLES                     || INNODB_BUFFER_POOL_STATS              || INNODB_FT_CONFIG                      |+---------------------------------------+59 rows in set (0.00 sec)

Mysql> desc SCHEMATA;

+----------------------------+--------------+------+-----+---------+-------+| Field                      | Type         | Null | Key | Default | Extra |+----------------------------+--------------+------+-----+---------+-------+| CATALOG_NAME               | varchar(512) | NO   |     |         |       || SCHEMA_NAME                | varchar(64)  | NO   |     |         |       || DEFAULT_CHARACTER_SET_NAME | varchar(32)  | NO   |     |         |       || DEFAULT_COLLATION_NAME     | varchar(32)  | NO   |     |         |       || SQL_PATH                   | varchar(512) | YES  |     | NULL    |       |+----------------------------+--------------+------+-----+---------+-------+5 rows in set (0.00 sec)

Mysql> select SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME from SCHEMATA;

+--------------------+----------------------------+| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME |+--------------------+----------------------------+| information_schema | utf8                       || mysql              | latin1                     || performance_schema | utf8                       || prod               | latin1                     || test               | latin1                     |+--------------------+----------------------------+5 rows in set (0.00 sec)

Mysql> desc tables;

+-----------------+---------------------+------+-----+---------+-------+| Field           | Type                | Null | Key | Default | Extra |+-----------------+---------------------+------+-----+---------+-------+| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       || TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       || TABLE_NAME      | varchar(64)         | NO   |     |         |       || TABLE_TYPE      | varchar(64)         | NO   |     |         |       || ENGINE          | varchar(64)         | YES  |     | NULL    |       || VERSION         | bigint(21) unsigned | YES  |     | NULL    |       || ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       || TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       || AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       || DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       || MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       || INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       || DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       || AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       || CREATE_TIME     | datetime            | YES  |     | NULL    |       || UPDATE_TIME     | datetime            | YES  |     | NULL    |       || CHECK_TIME      | datetime            | YES  |     | NULL    |       || TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       || CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       || CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       || TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |+-----------------+---------------------+------+-----+---------+-------+21 rows in set (0.00 sec)

Mysql> select table_schema, table_name, table_type, engine, table_rows from tables where table_schema = 'prod ';

+--------------+------------+------------+--------+------------+| table_schema | table_name | table_type | engine | table_rows |+--------------+------------+------------+--------+------------+| prod         | t1         | BASE TABLE | InnoDB |          2 || prod         | t2         | BASE TABLE | InnoDB |          2 |+--------------+------------+------------+--------+------------+2 rows in set (0.00 sec)

Mysql> select * from prod. t1;

+------+-------+| id   | name  |+------+-------+|   10 | tom   ||   20 | jerry ||   30 | rose  ||   40 | ellen |+------+-------+4 rows in set (0.00 sec)

Mysql> explain select * from prod. t1;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)

Important tables include:

SCHEMATA table:

The SCHEMATA table provides information about all databases in the current mysql instance. Is the result of show databases.

TABLES Table:

The TABLES Table provides information about TABLES in the database (including views ). Describes in detail the schema, table type, table engine, creation time, and other information of a table. Is the result of show tables from schemaname.

COLUMNS table:

The COLUMNS table provides the column information in the table. Describes all the columns of a table and the information of each column in detail. Is the result of show columns from schemaname. tablename.

STATISTICS table:

The STATISTICS table provides information about table indexes. Is the result of show index from schemaname. tablename.

USER_PRIVILEGES table:

The USER_PRIVILEGES table provides information about full-process permissions. This information is from the mysql. user authorization table. Non-standard table.

SCHEMA_PRIVILEGESTable:

The SCHEMA_PRIVILEGES table provides information about the schema (database) permission. This information is from the mysql. db authorization table. Non-standard table.

TABLE_PRIVILEGESTable:

The TABLE_PRIVILEGES table provides information about table permissions. This information is from the mysql. tables_priv authorization table. Non-standard table.

COLUMN_PRIVILEGESTable;

The COLUMN_PRIVILEGES table provides information about column permissions. This information is derived from the mysql. columns_priv authorization table. Non-standard table.

CHARACTER_SETSTable:

The CHARACTER_SETS table provides information about available character sets of mysql instances. This table is obtained from the show character set result SET.

COLLATIONSTable;

The COLLATIONS table provides control information about each character set.

COLLATION_CHARACTER_SET_APPLICABILITY table:

The COLLATION_CHARACTER_SET_APPLICABILITY table specifies the character sets that can be used for verification. These columns are equivalent to the first two display fields of show collation.

TABLE_CONSTRAINTSTable;

The TABLE_CONSTRAINTS table describes tables with constraints. And the constraint type of the table.

KEY_COLUMN_USAGETable:

The KEY_COLUMN_USAGE table describes restricted key columns.

ROUTINESTable:

The ROUTINES table provides information about stored subroutines (stored programs and functions. In this case, the ROUTINES table does not contain udfs ). The column named "mysql. proc name" specifies the mysql. proc table column corresponding to the INFORMATION_SCHEMA.ROUTINES table.

VIEWSTable:

The VIEWS table provides information about VIEWS in the database. You must have the show views permission. Otherwise, you cannot view information.

TRIGGERSTable;

The TRIGGERS table provides information about the trigger program. You must have the super permission to view the table.

In addition, new tables will be added in future versions.

[Note] information_schema is a database composed of metadata of the database. It stores basic information about the mysql database. And change at any time. Used to view information and make system decisions as an important information provider.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.