Question

DB_NAME	简述	详述	表总数（张）
MySQL 版本 version			5.6	5.7	8.0
mysql	核心数据库，类似于 sql server 中的 master 表。	主要负责存储数据库的用户、权限设置、关键字等 mysql 自己需要使用的控制和管理信息，不可以删除。	28	31	33
information_schema	信息数据库，提供了访问数据库元数据的方式。	保存着关于 MySQL 服务器所维护的所有其他数据库的信息。 MySQL 5.7 后包括了 28 张 INNODB_表。show 表达式扩展多来自这里的视图。	58	61	78
performance_schema	性能数据库。	MySQL 5.5 后新增。用于收集数据库服务器性能参数。	52	87	105
sys		5.7 后新增。	0	101	101

备注：1. MySQL 5.6 的元数据表是 3 张，MySQL5.7+后增加了 sys 表。通常情况下，DB 客户端不显示 mysql 和 information_schema。

2. INFORMATION_SCHEMA，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。每位 MySQL 用户均有权访问这些表，但仅限于表中的特定行，在这类行中含有用户具有恰当访问权限的对象。

表格 获取 MySQL 元数据的命令列表

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

示例：

mysql> SELECT VERSION( );
+------------+
| VERSION( ) |
+------------+
| 8.0.21     |
+------------+
1 row in set (0.00 sec)

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

show 语法

mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where: {
    LIKE 'pattern'
  | WHERE expr
}

information_schema.tables 里的表信息

• table_type 三种：BASE TABLE, SYSTEM VIEW, VIEW
• engine：MEMORY, InnoDB, MyISAM, CSV, PERFORMATION_SCHEMA, NULL（当 table_type=VIEW 时）
• table_schema: MySQL 里的 schema 等同于数据库。

# 以下 2 个 SQL 执行结果一样
mysql> select distinct table_type FROM information_schema.tables;
mysql> select table_type FROM information_schema.tables group by table_type;
+-------------+
| TABLE_TYPE  |
+-------------+
| SYSTEM VIEW |
| BASE TABLE  |
| VIEW        |
+-------------+
3 rows in set (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)