MySQL – information_schema

MySQL - information_schema

MySQL：8.0.26

information_schema 是 MySQL 内建存放 中继资料（Metadata） 的资料库，例如你所有资料库的名称、Table 资讯、栏位的资料型别、访问存取权等等，都会纪录在 information_schema 里。

Basic Usage

由于 information_schema 是内建资料库，一般状况下并不会操作到它。不过，如果你拥有的资料量很大，需要进行资料的 migration、sunset 特定栏位或资料表时，透过 information_schema 去做整体的清查就会非常快速且方便。

以下为 information_schema 较可能会使用到的 table：

Name	Description
SCHEMATA	纪录资料库资讯，`show databases` 指令就是会走访此表。
TABLES	纪录 Table 资讯，可以看到表名称、所属哪个资料库、表的型态和使用的 `Engine` 等等。
COLUMNS	纪录栏位资讯，可以看到所有栏位的资讯。
STATISTICS	纪录所有 Index 资讯。
USER_PRIVILEGES	纪录使用者权限资讯。
SCHEMA_PRIVILEGES	纪录资料库权限资讯。
TABLE_PRIVILEGES	纪录 Table 权限资讯。
COLUMN_PRIVILEGES	纪录栏位权限资讯。
VIEWS	纪录所有 View 的资讯。

Usage Scenario

以下就列几个常见的需求情境的使用方法：

查询资料表资讯

# 取得资料表资讯
SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE, ENGINE FROM information_schema.TABLES ORDER BY TABLE_NAME;

# 取得资料表的 Size
# DATA_LENGTH + INDEX_LENGTH 就是大小，单位是 bytes.
SELECT TABLE_NAME, round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = 'my_db_name' AND table_name = 'album';

查询 View 资讯

# 取得 View 资讯
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'my_db_name';

# 寻找哪张 View 里有使用到 album_name 栏位
SELECT views.TABLE_NAME, information_schema.COLUMNS.COLUMN_NAME FROM (SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'my_db_name') AS views LEFT JOIN information_schema.COLUMNS ON information_schema.COLUMNS.TABLE_NAME = views.TABLE_NAME WHERE COLUMN_NAME = 'album_name';

Categories: MySQL

Tags: MySQL