MySQL 中 information_schema 简介

information_schema 是 MySQL 数据库中的一个虚拟数据库，它提供了关于数据库、表、列、索引、约束等所有数据库对象的元数据（metadata）。information_schema 不包含实际的数据，而是包含了 MySQL 系统中所有数据库对象的结构信息。它类似于其他数据库系统中的系统目录或数据字典。

通过查询 information_schema，你可以获取有关 MySQL 数据库的详细信息，比如表的结构、列的定义、约束、索引、权限等。

1. information_schema 的用途

  • 数据库和表结构：查看数据库、表、字段及其数据类型、默认值等。
  • 索引信息：查看表的索引信息，如索引名称、列顺序等。
  • 列的约束：获取字段的约束信息，如是否可以为 NULL，主键、外键等。
  • 权限管理：获取数据库和表级的权限信息。
  • 存储引擎：查看表使用的存储引擎、表的物理文件位置等。

2. information_schema 中的常见表

information_schema 包含多个表，每个表提供不同种类的元数据。以下是一些常见的表：

1. TABLES

  • 作用：存储所有数据库中表的信息。
  • 常用字段：
    • TABLE_NAME：表名。
    • TABLE_SCHEMA：数据库名。
    • TABLE_TYPE：表的类型，通常是 BASE TABLE（基本表）或 VIEW（视图）。
    • ENGINE：表的存储引擎（如 InnoDB、MyISAM 等）。
    • CREATE_TIME：表的创建时间。
    • UPDATE_TIME：表的最后更新时间。
SELECT * FROM information_schema.tables WHERE table_schema = 'your_database_name';

2. COLUMNS

  • 作用：存储关于所有表的列的信息。
  • 常用字段：
    • TABLE_NAME：表名。
    • COLUMN_NAME：列名。
    • DATA_TYPE：列的数据类型（如 VARCHAR、INT、DATE 等）。
    • IS_NULLABLE：列是否允许 NULL。
    • COLUMN_DEFAULT：列的默认值。
    • CHARACTER_MAXIMUM_LENGTH：如果列的数据类型是字符类型（如 VARCHAR），则记录最大长度。
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM information_schema.columns
WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name';

3. KEY_COLUMN_USAGE

  • 作用：列出每个表的键（主键、外键）信息。
  • 常用字段：
    • TABLE_NAME：表名。
    • COLUMN_NAME：列名。
    • CONSTRAINT_NAME：约束名称（如主键、外键约束名）。
    • REFERENCED_TABLE_NAME：如果是外键，则为引用的表名。
    • REFERENCED_COLUMN_NAME：如果是外键，则为引用的列名。
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.key_column_usage
WHERE table_schema = 'your_database_name';

4. STATISTICS

  • 作用：提供有关表的索引信息。
  • 常用字段：
    • TABLE_NAME：表名。
    • INDEX_NAME：索引名称。
    • COLUMN_NAME：索引列名。
    • NON_UNIQUE：如果索引不唯一，值为 1，否则为 0。
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM information_schema.statistics
WHERE table_schema = 'your_database_name';

5. SCHEMATA

  • 作用：列出所有数据库的信息。
  • 常用字段：
    • SCHEMA_NAME：数据库名。
    • DEFAULT_CHARACTER_SET_NAME：默认字符集。
    • DEFAULT_COLLATION_NAME：默认排序规则。
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
FROM information_schema.schemata;

6. USER_PRIVILEGES

  • 作用：列出当前用户的权限信息。
  • 常用字段：
    • GRANTEE：授权对象（用户）。
    • TABLE_CATALOG：表目录。
    • PRIVILEGE_TYPE：权限类型，如 SELECT、INSERT、UPDATE 等。
SELECT GRANTEE, PRIVILEGE_TYPE
FROM information_schema.user_privileges;

7. VIEWS

  • 作用：列出所有视图的信息。
  • 常用字段：
    • TABLE_NAME：视图名。
    • VIEW_DEFINITION：视图的定义（查询语句）。
    • CHECK_OPTION：检查选项（如是否允许修改视图数据）。
SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.views
WHERE table_schema = 'your_database_name';

8. TABLE_CONSTRAINTS

  • 作用：列出所有表的约束信息。
  • 常用字段：
    • TABLE_NAME：表名。
    • CONSTRAINT_NAME：约束名。
    • CONSTRAINT_TYPE：约束类型（如 PRIMARY KEY、FOREIGN KEY、UNIQUE）。
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE table_schema = 'your_database_name';

3. information_schema 中的数据的实时性

  • 实时性：information_schema 中的元数据是实时的，意味着你可以随时查询数据库对象的当前状态。它不缓存任何信息，因此每次查询时都会从 MySQL 系统内部获取最新的数据。

4. 如何使用 information_schema

information_schema 提供了丰富的系统元数据，适用于以下常见场景：

  • 数据库结构文档生成：自动化生成数据库架构文档，列出所有表、字段、数据类型等。
  • 性能调优：通过查看索引、表大小、表的行数等，帮助优化查询性能。
  • 数据库分析和迁移：获取表的元数据，用于数据库迁移或备份。
  • 权限审计：通过查询权限相关表，审核和管理数据库的用户权限。

5. 总结

information_schema 是一个强大的工具，允许用户和开发者查询 MySQL 数据库的元数据。在管理和分析数据库结构、权限、表的索引、列的定义等方面，它提供了非常详细的视图。通过对这些表的查询，你可以获得数据库的结构信息、优化数据库性能以及审计和管理数据库对象。