Detailed explanation of MySQL information_schema database

Detailed explanation of MySQL information_schema database

1. Overview

The information_schema database is the same as the performance_schema database, both of which are built-in information databases of MySQL. performance_schema is used for performance analysis, while information_schema is used to store database metadata (data about data), such as database name, table name, column data type, access permissions, etc.

The tables in information_schema are actually views, not base tables, and therefore, there are no files associated with them on the file system.

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.00 sec)

2. Commonly used tables in the information_schema library

CHARACTER_SETS table

Provides information about the character sets available for MySQL. The SHOW CHARACTER SET; command gets results from this table.

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
...
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.07 sec)

mysql> SELECT * FROM CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
...
| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |
| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)

SCHEMATA table

Information about all databases in the current MySQL instance. The SHOW DATABASES; command gets data from this table.

mysql> SELECT * FROM SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | sys | utf8 | utf8_general_ci | NULL |
| def | test | utf8 | utf8_unicode_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
10 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test |
+--------------------+
10 rows in set (0.00 sec)

TABLES

Stores table information (including views) in the database, including which database the table belongs to, the table type, storage engine, creation time, and other information. The SHOW TABLES FROM XX; command gets the results from this table.

mysql> SELECT * FROM TABLES;
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+------------+------------+------------+----------------+-------------+-----------+----------------+---------------------+---------------------+---------------------+----------+--------------------+--------------------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+------------+------------+------------+----------------+-------------+-----------+----------------+---------------------+---------------------+---------------------+----------+--------------------+--------------------------------------------+
| def | information_schema | CHARACTER_SETS | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 384 | 0 | 16434816 | 0 | 0 | NULL | 2018-04-23 11:51:32 | NULL | NULL | utf8_general_ci | NULL | max_rows=43690 | |
| def | information_schema | COLLATIONS | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 231 | 0 | 16704765 | 0 | 0 | NULL | 2018-04-23 11:51:32 | NULL | NULL | utf8_general_ci | NULL | max_rows=72628 | |
...
| def | zentao | zt_usertpl | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2017-08-16 16:36:45 | 2017-08-16 16:36:45 | NULL | utf8_general_ci | NULL | | |
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+------------+------------+------------+----------------+-------------+-----------+----------------+---------------------+---------------------+---------------------+----------+--------------------+--------------------------------------------+
525 rows in set (3.03 sec)

mysql> SHOW TABLES FROM zentao;
+-------------------+
| Tables_in_zentao |
+-------------------+
| zt_action |
| zt_block |
| zt_branch |
...
| zt_usertpl |
+-------------------+
48 rows in set (0.00 sec)

COLUMNS table

Stores column information in the table, including how many columns the table has, the type of each column, etc. The SHOW COLUMNS FROM schemaname.tablename command gets results from this table.

mysql> SELECT * FROM COLUMNS LIMIT 2,5;
+---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+---------------+--------------------+---------------------+-------------+------------+-------+------------+----------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION |
+---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+---------------+--------------------+---------------------+-------------+------------+-------+------------+----------------+-----------------------+
| def | information_schema | CHARACTER_SETS | DESCRIPTION | 3 | | NO | varchar | 60 | 180 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(60) | | | select | | |
| def | information_schema | CHARACTER_SETS | MAXLEN | 4 | 0 | NO | bigint | NULL | NULL | 19 | 0 | NULL | NULL | NULL | bigint(3) | | | select | | |
| def | information_schema | COLLATIONS | COLLATION_NAME | 1 | | NO | varchar | 32 | 96 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(32) | | | select | | |
| def | information_schema | COLLATIONS | CHARACTER_SET_NAME | 2 | | NO | varchar | 32 | 96 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(32) | | | select | | |
| def | information_schema | COLLATIONS | ID | 3 | 0 | NO | bigint | NULL | NULL | 19 | 0 | NULL | NULL | NULL | bigint(11) | | | select | | |
+---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+---------------+--------------------+---------------------+-------------+------------+-------+------------+----------------+-----------------------+
5 rows in set (0.08 sec)

STATISTICS table

Information about table indexes. The SHOW INDEX FROM schemaname.tablename; command gets the results from this table.

mysql> SHOW INDEX FROM szhuizhong.users;
+-------+------------+---------------+--------------+--------------+---------------+-------------+------+--------+------+------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+--------------+---------------+-------------+------+--------+------+------------+---------+------------+
| users | 0 | PRIMARY | 1 | UserID | A | 1460 | NULL | NULL | | BTREE | | |
| users | 0 | Account_index | 1 | Account | A | 1460 | NULL | NULL | | BTREE | | |
| users | 1 | CorpID | 1 | FromID | A | 2 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+--------------+---------------+-------------+------+--------+------+------------+---------+------------+
3 rows in set (0.00 sec)

USER_PRIVILEGES table

User permissions table. The content comes from the mysql.user authorization table. This is a non-standard table.

mysql> SELECT * FROM USER_PRIVILEGES;
+-------------------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def | USAGE | NO |
| 'root'@'%' | def | SELECT | YES |
| 'root'@'%' | def | INSERT | YES |
| 'root'@'%' | def | UPDATE | YES |
| 'root'@'%' | def | DELETE | YES |
| 'root'@'%' | def | CREATE | YES |
| 'root'@'%' | def | DROP | YES |
| 'root'@'%' | def | RELOAD | YES |
| 'root'@'%' | def | SHUTDOWN | YES |
| 'root'@'%' | def | PROCESS | YES |
| 'root'@'%' | def | FILE | YES |
| 'root'@'%' | def | REFERENCES | YES |
| 'root'@'%' | def | INDEX | YES |
| 'root'@'%' | def | ALTER | YES |
| 'root'@'%' | def | SHOW DATABASES | YES |
| 'root'@'%' | def | SUPER | YES |
| 'root'@'%' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | LOCK TABLES | YES |
| 'root'@'%' | def | EXECUTE | YES |
| 'root'@'%' | def | REPLICATION SLAVE | YES |
| 'root'@'%' | def | REPLICATION CLIENT | YES |
| 'root'@'%' | def | CREATE VIEW | YES |
| 'root'@'%' | def | SHOW VIEW | YES |
| 'root'@'%' | def | CREATE ROUTINE | YES |
| 'root'@'%' | def | ALTER ROUTINE | YES |
| 'root'@'%' | def | CREATE USER | YES |
| 'root'@'%' | def | EVENT | YES |
| 'root'@'%' | def | TRIGGER | YES |
| 'root'@'%' | def | CREATE TABLESPACE | YES |
+-------------------------+---------------+-------------------------+--------------+
29 rows in set (0.00 sec)

SCHEMA_PRIVILEGES table

Program permissions table. Gives information about schema (database) permissions. The content comes from the mysql.db authorization table. This is a non-standard table.

mysql> SELECT * FROM SCHEMA_PRIVILEGES;
+-------------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO |
| 'root'@'%' | def | mysql | SELECT | YES |
| 'root'@'%' | def | mysql | INSERT | YES |
| 'root'@'%' | def | mysql | UPDATE | YES |
| 'root'@'%' | def | mysql | DELETE | YES |
| 'root'@'%' | def | mysql | CREATE | YES |
| 'root'@'%' | def | mysql | DROP | YES |
| 'root'@'%' | def | mysql | REFERENCES | YES |
| 'root'@'%' | def | mysql | INDEX | YES |
| 'root'@'%' | def | mysql | ALTER | YES |
| 'root'@'%' | def | mysql | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | mysql | LOCK TABLES | YES |
| 'root'@'%' | def | mysql | EXECUTE | YES |
| 'root'@'%' | def | mysql | CREATE VIEW | YES |
| 'root'@'%' | def | mysql | SHOW VIEW | YES |
| 'root'@'%' | def | mysql | CREATE ROUTINE | YES |
| 'root'@'%' | def | mysql | ALTER ROUTINE | YES |
| 'root'@'%' | def | mysql | EVENT | YES |
| 'root'@'%' | def | mysql | TRIGGER | YES |
+-------------------------+---------------+--------------+-------------------------+--------------+
19 rows in set (0.00 sec)

TABLE_PRIVILEGES table

Table permissions table. Gives information about table privileges. Contents are taken from the mysql.tables_priv grant table. This is a non-standard table.

mysql> SELECT * FROM TABLE_PRIVILEGES;
+-------------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+------------+----------------+--------------+
| 'mysql.sys'@'localhost' | def | sys | sys_config | SELECT | NO |
+-------------------------+---------------+--------------+------------+----------------+--------------+
1 row in set (0.00 sec)

COLUMN_PRIVILEGES table

Column permissions table. Gives information about column permissions. Contents are taken from the mysql.columns_priv grant table. This is a non-standard table.

mysql> SELECT * FROM COLUMN_PRIVILEGES;
Empty set (0.00 sec)

COLLATIONS table

Provides comparative information about various character sets. The SHOW COLLATION; command gets the results from this table.

mysql> SELECT * FROM COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
...
| gb18030_bin | gb18030 | 249 | | Yes | 1 |
| gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |
+--------------------------+--------------------+-----+------------+-------------+---------+
222 rows in set (0.03 sec)

COLLATION_CHARACTER_SET_APPLICABILITY table

Specifies the character set that can be used for collation. Equivalent to the first two fields of the SHOW COLLATION command result.

mysql> SELECT * FROM COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------------+--------------------+
| COLLATION_NAME | CHARACTER_SET_NAME |
+--------------------------+--------------------+
| big5_chinese_ci | big5 |
| big5_bin | big5 |
| dec8_swedish_ci | dec8 |
...
| gb18030_bin | gb18030 |
| gb18030_unicode_520_ci | gb18030 |
+--------------------------+--------------------+
222 rows in set (0.00 sec)

TABLE_CONSTRAINTS table

Describes the table on which the constraint exists. and the constraint type of the table.

mysql> SELECT * FROM TABLE_CONSTRAINTS;
+--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+
| def | mysql | PRIMARY | mysql | columns_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | db | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | engine_cost | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | event | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | func | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | gtid_executed | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | help_category | PRIMARY KEY |
| def | mysql | name | mysql | help_category | UNIQUE |
| def | mysql | PRIMARY | mysql | help_keyword | PRIMARY KEY |
| def | mysql | name | mysql | help_keyword | UNIQUE |
| def | mysql | PRIMARY | mysql | help_relation | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | help_topic | PRIMARY KEY |
| def | mysql | name | mysql | help_topic | UNIQUE |
| def | mysql | PRIMARY | mysql | innodb_index_stats | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | innodb_table_stats | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | ndb_binlog_index | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | plugin | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | proc | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | procs_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | proxies_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | server_cost | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | servers | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | slave_master_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | slave_relay_log_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | slave_worker_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | tables_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_leap_second | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_name | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_transition | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_transition_type | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | user | PRIMARY KEY |
| def | sys | PRIMARY | sys | sys_config | PRIMARY KEY |
| def | zentao | PRIMARY | zentao | zt_action | PRIMARY KEY |
...
| def | zentao | account | zentao | zt_usergroup | UNIQUE |
| def | zentao | PRIMARY | zentao | zt_userquery | PRIMARY KEY |
| def | zentao | PRIMARY | zentao | zt_usertpl | PRIMARY KEY |
+--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+
213 rows in set (0.37 sec)

KEY_COLUMN_USAGE table

Describes the key columns that have constraints.

mysql> SELECT * FROM KEY_COLUMN_USAGE;
+--------------------+-------------------+--------------------+---------------+---------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+---+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+--------------------+---------------+---------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+---+------------------------+
| def | mysql | PRIMARY | def | mysql | columns_priv | Host | 1 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | columns_priv | Db | 2 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | columns_priv | User | 3 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | columns_priv | Table_name | 4 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | columns_priv | Column_name | 5 | NULL | NULL | NULL | NULL |
...
| def | mysql | PRIMARY | def | mysql | time_zone_leap_second | Transition_time | 1 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | time_zone_name | Name | 1 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | time_zone_transition | Time_zone_id | 1 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | time_zone_transition | Transition_time | 2 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | time_zone_transition_type | Time_zone_id | 1 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | time_zone_transition_type | Transition_type_id | 2 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | user | Host | 1 | NULL | NULL | NULL | NULL |
| def | mysql | PRIMARY | def | mysql | user | User | 2 | NULL | NULL | NULL | NULL |
| def | sys | PRIMARY | def | sys | sys_config | variable | 1 | NULL | NULL | NULL |
+--------------------+-------------------+--------------------+---------------+---------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+---+------------------------+
278 rows in set (0.03 sec)

ROUTINES table

Provides information about stored routines (stored procedures and functions). At this time, the ROUTINES table does not contain user-defined functions (UDFs). The column named "mysql.proc name" identifies the mysql.proc column that corresponds to the INFORMATION_SCHEMA.ROUTINES table.

VIEWS table

Gives information about views in the database. You need to have the show views permission, otherwise you cannot view the view information.

mysql> SELECT * FROM VIEWS LIMIT 1\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sys
          TABLE_NAME: host_summary
     VIEW_DEFINITION: select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: mysql.sys@localhost
       SECURITY_TYPE: INVOKER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.03 sec)

TRIGGERS table

Provides information about the triggering procedure. You must have the super privilege to view this table.

mysql> SELECT * FROM TRIGGERS LIMIT 1\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_insert_set_user
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2017-05-27 11:18:43.60
                  SQL_MODE: 
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

This is the end of this article on the detailed explanation of MySQL's information_schema database. For more relevant MySQL information_schema database content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL bypasses granting information_schema objects and reports ERROR 1044 (4200) error
  • Can information_schema and mysql in mysql database be deleted?
  • Parsing MySQL's information_schema database

<<:  Detailed explanation of the wonderful CSS attribute MASK

>>:  Docker Compose practice and summary

Recommend

MySQL SQL Optimization Tutorial: IN and RANGE Queries

First, let's talk about the in() query. It is...

Implement 24+ array methods in JavaScript by hand

Table of contents 1. Traversal Class 1. forEach 2...

HTML page native VIDEO tag hides the download button function

When writing a web project, I encountered an intr...

Summary of methods to prevent users from submitting forms repeatedly

Duplicate form submission is the most common and ...

The magic of tr command in counting the frequency of English words

We are all familiar with the tr command, which ca...

Mobile web screen adaptation (rem)

Preface I recently sorted out my previous notes o...

Detailed explanation of direct routing in cross-host communication of Docker containers

Overview As for the current default network of Do...

List of commonly used escape codes for HTML greater than, less than, spaces, quotation marks, etc.

The table is as follows: HTML source code Display...

Detailed explanation of dragging table columns using Vue Element Sortablejs

1. css: dragTable.css @charset "UTF-8";...

JavaScript form validation example

HTML forms are commonly used to collect user info...

A very detailed explanation of Linux C++ multi-thread synchronization

Table of contents 1. Mutex 1. Initialization of m...

Implementation of breakpoint resume in Node.js

Preface Normal business needs: upload pictures, E...

Understanding of the synchronous or asynchronous problem of setState in React

Table of contents 1. Is setState synchronous? asy...

npm Taobao mirror modification explanation

1. Top-level usage 1. Install cnpm npm i -g cnpm ...

Detailed explanation of iframe tag usage (attributes, transparency, adaptive height)

1. iframe definition and usage The iframe element...