在 MySQL 中，information_schema 是一个虚拟的数据库，它提供了有关 MySQL 服务器和数据库本身的元数据（即关于数据库的数据）。这个数据库不包含用户数据，而是存储着关于数据库结构、表、列、索引、权限等方面的详细信息。
information_schema 由 MySQL 自动管理，并且对所有用户都是只读的。它允许你查询数据库对象的信息，而不需要直接访问系统表，因此 information_schema 为开发者和数据库管理员提供了强大的信息查询功能。
1. information_schema 的用途
- 查看数据库、表、列等信息：你可以查询数据库中的所有表、列、数据类型等信息。
- 获取元数据：可以查看数据库的结构，表之间的关系，索引的使用情况，以及列的约束条件等。
- 安全性检查：通过查询
information_schema，可以检查用户权限、锁的状态等。
2. information_schema 的结构
information_schema 数据库包含多个表，这些表存储了不同类型的元数据。常见的表如下：
| 表名 | 描述 |
|---|---|
| TABLES | 存储数据库中的所有表的信息 |
| COLUMNS | 存储表中列的信息，包括列名、数据类型、默认值等 |
| KEY_COLUMN_USAGE | 存储表中主键、外键等约束信息 |
| SCHEMATA | 存储 MySQL 中所有数据库的信息 |
| USER_PRIVILEGES | 存储用户权限相关信息 |
| TABLE_CONSTRAINTS | 存储表中约束（如主键、外键、唯一键）的信息 |
| VIEWS | 存储视图的信息 |
| ROUTINES | 存储存储过程和函数的信息 |
| PROCESSLIST | 存储当前正在运行的线程信息 |
| CHARACTER_SETS | 存储 MySQL 支持的字符集信息 |
| COLLATIONS | 存储 MySQL 支持的字符集排序规则信息 |
| ENGINES | 存储 MySQL 支持的存储引擎信息 |
| INNODB_STATUS | 存储 InnoDB 存储引擎的状态信息 |
| STATISTICS | 存储表的索引统计信息 |
3. 常见查询示例
以下是一些常用的 information_schema 查询，帮助你从中获取数据库元数据。
3.1 查询数据库中的所有表
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
此查询将返回指定数据库 (your_database_name) 中所有表的名称。
3.2 查询某个表的所有列信息
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
此查询将返回指定表的所有列的信息，包括列名、数据类型、是否允许为 NULL 和默认值等。
3.3 查询表的主键信息
SELECT COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name' AND CONSTRAINT_NAME = 'PRIMARY';
此查询将返回指定表的主键列。
3.4 查询表的索引信息
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
此查询将返回指定表的所有索引信息，其中 NON_UNIQUE 为 0 表示该索引是唯一索引。
3.5 查询所有数据库信息
SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA;
此查询将列出 MySQL 中的所有数据库。
3.6 查询所有用户权限信息
SELECT *
FROM information_schema.USER_PRIVILEGES;
此查询返回所有用户在 MySQL 上的权限信息。
3.7 查询所有存储过程和函数
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database_name';
此查询返回指定数据库中的所有存储过程和函数。
4. information_schema 表的详细介绍
以下是 information_schema 中一些重要表的详细介绍：
4.1 SCHEMATA 表
该表存储了 MySQL 中所有数据库的信息。
CATALOG_NAME：默认值为def，表示数据库目录的名称。SCHEMA_NAME：数据库的名称。DEFAULT_CHARACTER_SET_NAME：数据库使用的默认字符集。DEFAULT_COLLATION_NAME：数据库的默认排序规则。
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
FROM information_schema.SCHEMATA;
4.2 TABLES 表
该表存储了 MySQL 数据库中所有表的信息。
TABLE_NAME：表的名称。TABLE_SCHEMA：表所属的数据库。TABLE_TYPE：表的类型，通常为BASE TABLE或VIEW。ENGINE：表使用的存储引擎。
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
4.3 COLUMNS 表
该表存储了 MySQL 数据库中所有表的列信息。
TABLE_NAME：表的名称。COLUMN_NAME：列的名称。DATA_TYPE：列的数据类型。IS_NULLABLE：列是否允许为 NULL。COLUMN_DEFAULT：列的默认值。CHARACTER_MAXIMUM_LENGTH：字符列的最大长度。
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
4.4 KEY_COLUMN_USAGE 表
该表提供了关于表约束的详细信息，特别是关于主键、外键的列信息。
TABLE_NAME：表的名称。COLUMN_NAME：列的名称。CONSTRAINT_NAME：约束的名称，通常为PRIMARY（主键）或外键约束。REFERENCED_TABLE_NAME：如果是外键，则为引用的表的名称。
SELECT COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
4.5 STATISTICS 表
该表包含了表索引的信息。
TABLE_NAME：表的名称。INDEX_NAME：索引的名称。COLUMN_NAME：列的名称。NON_UNIQUE：索引是否唯一。
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
5. 总结
information_schema 是一个非常强大的工具，它提供了有关数据库结构和对象的详细信息。通过查询 information_schema，我们可以获得关于数据库、表、列、索引、约束、用户权限等的详细数据，极大地帮助了数据库管理员和开发者进行数据库管理和优化。
发表回复