mysql的information_schema浅析

information_schema 是 MySQL 中的一个虚拟数据库，它包含了关于 MySQL 服务器的所有元数据。

information_schema 作用

元数据管理：提供关于数据库、表、列、索引、权限等的信息。
性能优化：帮助了解数据库结构和索引使用情况，便于进行性能分析和优化。
安全和权限管理：查询用户和权限信息，帮助进行安全管理。
数据库维护：提供数据库、表、列的详细信息，便于进行维护和诊断。

常用表及其结构

information_schema 包含多个表，每个表提供不同方面的信息。以下是一些常用表的详细结构和使用方法。

1. SCHEMATA 表
   存储数据库的相关信息。

结构：

SCHEMA_NAME：数据库名称
DEFAULT_CHARACTER_SET_NAME：默认字符集
DEFAULT_COLLATION_NAME：默认校对规则

查询所有数据库：

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;

2. TABLES 表
   存储表的信息，包括表名、表类型、存储引擎等。

结构：

TABLE_SCHEMA：数据库名称
TABLE_NAME：表名称
TABLE_TYPE：表类型（BASE TABLE或VIEW）
ENGINE：存储引擎
TABLE_ROWS：表中的行数

查询某个数据库中的所有表：

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database';

3. COLUMNS 表
   存储列的信息，包括列名、数据类型、是否可以为空等。

结构：

TABLE_SCHEMA：数据库名称
TABLE_NAME：表名称
COLUMN_NAME：列名称
DATA_TYPE：数据类型
IS_NULLABLE：是否可以为空
COLUMN_DEFAULT：默认值

查询某个表的列信息：

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

4. STATISTICS 表
   存储索引的信息。

结构：

TABLE_SCHEMA：数据库名称
TABLE_NAME：表名称
INDEX_NAME：索引名称
COLUMN_NAME：列名称
NON_UNIQUE：是否唯一索引

查询某个表的索引信息：

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

5. USER_PRIVILEGES 表
   存储用户权限的信息。

结构：

GRANTEE：授予者（用户@主机）
PRIVILEGE_TYPE：权限类型
IS_GRANTABLE：是否可授予

查询当前用户的权限：

SELECT PRIVILEGE_TYPE
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = "'your_user'@'your_host'";

6. PROCESSLIST 表
   存储当前正在运行的查询。

结构：

ID：连接 ID
USER：用户名
HOST：主机名
DB：数据库名称
COMMAND：命令类型
TIME：执行时间
STATE：状态
INFO：查询信息

查询当前正在运行的查询：

SELECT * FROM information_schema.PROCESSLIST;

获取所有数据库名称

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;

获取某个数据库的所有表

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database';

获取某个表的列信息

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

获取某个表的索引信息

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

获取当前用户的权限

SELECT PRIVILEGE_TYPE
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = "'your_user'@'your_host'";

其他重要表
CHARACTER_SETS 表
提供服务器支持的字符集信息。

SELECT * FROM information_schema.CHARACTER_SETS;

COLLATIONS 表
提供服务器支持的校对规则信息。

SELECT * FROM information_schema.COLLATIONS;

ENGINES 表
提供服务器支持的存储引擎信息。

SELECT * FROM information_schema.ENGINES;

VIEWS 表
提供视图的信息。

SELECT * FROM information_schema.VIEWS;

ROUTINES 表
提供存储过程和函数的信息。

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'your_database';

总结
information_schema 是一个非常强大的工具，提供了对 MySQL 数据库元数据的全面访问。通过熟练使用 information_schema，可以更好地理解和管理你的数据库，从而提高性能和安全性。无论是日常管理、性能优化还是安全性检查，information_schema 都提供了必要的信息支持。