MySQL Study--mysql schema_information Database

MySQL Study--mysql schema_information Database

The INFORMATION_SCHEMA database is generated after MySQL version 5.0, a virtual database that does not physically exist.

The INFORMATION_SCHEMA database is similar to the data dictionary, providing a way to access the metadata of the database, which is the data of the data. such as database name or table name, column type, access permissions (more granular access).

Case:

mysql> show databases;

+--------------------+| Database           |+--------------------+| information_schema | | | mysql              | | performance_schema | | PROD               | | Test               | +--------------------+5 rows in Set (0.00 sec)

visit 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                |  NO | | | |       | Default_character_set_name | varchar (+)  | NO | | | |       | Default_collation_name     -varchar |  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 (+) unsigned |     YES | |       NULL | || Row_format | varchar (10) |     YES | |       NULL | || Table_rows | bigint (+) unsigned |     YES | |       NULL | || Avg_row_length | bigint (+) unsigned |     YES | |       NULL | || Data_length | bigint (+) unsigned |     YES | |       NULL | || Max_data_length | bigint (+) unsigned |     YES | | NULL |       || Index_length | bigint (+) unsigned |     YES | |       NULL | || Data_free | bigint (+) unsigned |     YES | |       NULL | || auto_increment | bigint (+) 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 (+) 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)

Among the important tables are :

Schemata table:

The Schemata table provides information about all databases in the current MySQL instance. This table is the result of show databases.

Tables table:

The Tables table provides information about the tables in the database, including views. Describes in detail which schema, table type, table engine, creation time, and so on a table belongs to. This table is the result of show tables from SchemaName.

Columns table:

The columns table provides the column information in the table. Describes in detail all the columns of a table and the information for each column. This table is the result of show columns from Schemaname.tablename.

Statistics Table:

The statistics table provides information about the index of the table. This table is the result of show index from Schemaname.tablename.

User_privileges table:

The User_privileges (User Rights) table gives information about the full range of permissions. This information is derived from the Mysql.user authorization form. Non-standard tables.

schema_privileges Table:

The Schema_privileges (Program permissions) table gives information about the schema (database) permissions. This information is from the Mysql.db authorization form. Non-standard tables.

table_privileges Table:

The Table_privileges (table permissions) table gives information about table permissions. This information is derived from the Mysql.tables_priv authorization form. Non-standard tables.

column_privileges table;

The column_privileges (column permissions) table gives information about column permissions. This information is derived from the Mysql.columns_priv authorization form. Non-standard tables.

character_sets Table:

The Character_sets (character Set) table provides information about the available character sets for MySQL instances. This table is the show CHARACTER set result set.

collations table;

The collations table provides cross-reference information about each character set.

Collation_character_set_applicabilit Y table:

The Collation_character_set_applicability table indicates the character set that can be used for proofreading. These columns are equivalent to the first two display fields of show collation.

table_constraints table;

The Table_constraints table describes the tables in which the constraint exists. And the constraint type of the table.

key_column_usage Table:

The Key_column_usage table describes the key columns that have constraints.

ROUTINES Table:

The Routines table provides information about storing subroutines (stored programs and functions). At this point, the routines table does not contain a custom function (UDF). A column named "Mysql.proc name" indicates the corresponding INFORMATION_SCHEMA. The Mysql.proc table column for the routines table.

views Table:

The Views table gives information about the view in the database. You need to have show views permission, otherwise you cannot view the view information.

TRIGGERS table;

The Triggers table provides information about the triggering program. You must have super permissions to view the table.

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

Note: The INFORMATION_SCHEMA is a database that consists of the metadata of the database. The basic information of MySQL database is stored inside. and change at any time. Used to view information and system decisions as important information providers.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Study--mysql schema_information Database