information_schema简介

information_schema 是 MySQL 中的一个系统数据库，它提供了关于数据库元数据的信息。元数据是指描述数据库结构和内容的数据，例如表、列、索引、用户权限等。information_schema 数据库是只读的，用户不能对其进行修改。

位置

information_schema 数据库存在于每个 MySQL 实例中，不需要单独创建。当你连接到 MySQL 服务器时，可以通过以下方式访问 information_schema 数据库：

USE information_schema;

或者在查询中直接指定 information_schema 数据库：

SELECT * FROM information_schema.tables;

主要表

information_schema 数据库包含多个表，每个表都提供了不同类型的信息。以下是一些常用的表及其描述：

1. TABLES

   • 描述：包含所有表的信息。
   • 常用字段：
     • TABLE_CATALOG：表所属的目录（通常是 def）。
     • TABLE_SCHEMA：表所属的数据库名称。
     • TABLE_NAME：表的名称。
     • TABLE_TYPE：表的类型（如 BASE TABLE 或 VIEW）。
     • ENGINE：表的存储引擎（如 InnoDB、MyISAM 等）。
     • TABLE_ROWS：表中的行数（近似值）。
     • AUTO_INCREMENT：自动递增字段的下一个值。

2. COLUMNS

   • 描述：包含所有列的信息。
   • 常用字段：
     • TABLE_CATALOG：列所属的目录（通常是 def）。
     • TABLE_SCHEMA：列所属的数据库名称。
     • TABLE_NAME：列所属的表名称。
     • COLUMN_NAME：列的名称。
     • ORDINAL_POSITION：列在表中的位置。
     • COLUMN_DEFAULT：列的默认值。
     • IS_NULLABLE：列是否允许为空。
     • DATA_TYPE：列的数据类型。
     • CHARACTER_MAXIMUM_LENGTH：字符列的最大长度。
     • NUMERIC_PRECISION：数值列的精度。
     • NUMERIC_SCALE：数值列的小数位数。

3. SCHEMATA

   • 描述：包含所有数据库的信息。
   • 常用字段：
     • CATALOG_NAME：数据库所属的目录（通常是 def）。
     • SCHEMA_NAME：数据库的名称。
     • DEFAULT_CHARACTER_SET_NAME：数据库的默认字符集。
     • DEFAULT_COLLATION_NAME：数据库的默认排序规则。

4. STATISTICS

   • 描述：包含所有索引的信息。
   • 常用字段：
     • TABLE_CATALOG：索引所属的目录（通常是 def）。
     • TABLE_SCHEMA：索引所属的数据库名称。
     • TABLE_NAME：索引所属的表名称。
     • INDEX_NAME：索引的名称。
     • NON_UNIQUE：索引是否允许重复值。
     • SEQ_IN_INDEX：索引字段在索引中的位置。
     • COLUMN_NAME：索引字段的名称。
     • CARDINALITY：索引的基数（近似值）。
     • SUB_PART：索引字段的前缀长度（如果有的话）。

5. ROUTINES

   • 描述：包含所有存储过程和函数的信息。
   • 常用字段：
     • SPECIFIC_NAME：存储过程或函数的名称。
     • ROUTINE_CATALOG：存储过程或函数所属的目录（通常是 def）。
     • ROUTINE_SCHEMA：存储过程或函数所属的数据库名称。
     • ROUTINE_NAME：存储过程或函数的名称。
     • ROUTINE_TYPE：存储过程或函数的类型（如 PROCEDURE 或 FUNCTION）。
     • DATA_TYPE：返回值的数据类型（仅适用于函数）。
     • IS_DETERMINISTIC：存储过程或函数是否是确定性的。
     • SQL_DATA_ACCESS：存储过程或函数对数据的访问级别。

6. VIEWS

   • 描述：包含所有视图的信息。
   • 常用字段：
     • TABLE_CATALOG：视图所属的目录（通常是 def）。
     • TABLE_SCHEMA：视图所属的数据库名称。
     • TABLE_NAME：视图的名称。
     • VIEW_DEFINITION：视图的定义SQL语句。
     • CHECK_OPTION：视图的检查选项（如 NONE、LOCAL、CASCADED）。
     • IS_UPDATABLE：视图是否可更新。

7. USER_PRIVILEGES

   • 描述：包含所有用户的全局权限信息。
   • 常用字段：
     • GRANTEE：用户的名称和主机。
     • TABLE_CATALOG：权限所属的目录（通常是 def）。
     • PRIVILEGE_TYPE：权限类型（如 SELECT、INSERT、UPDATE 等）。
     • IS_GRANTABLE：是否可以授予他人。

8. SCHEMA_PRIVILEGES

   • 描述：包含所有数据库的权限信息。
   • 常用字段：
     • GRANTEE：用户的名称和主机。
     • TABLE_CATALOG：权限所属的目录（通常是 def）。
     • TABLE_SCHEMA：数据库的名称。
     • PRIVILEGE_TYPE：权限类型（如 SELECT、INSERT、UPDATE 等）。
     • IS_GRANTABLE：是否可以授予他人。

9. TABLE_PRIVILEGES

   • 描述：包含所有表的权限信息。
   • 常用字段：
     • GRANTEE：用户的名称和主机。
     • TABLE_CATALOG：权限所属的目录（通常是 def）。
     • TABLE_SCHEMA：数据库的名称。
     • TABLE_NAME：表的名称。
     • PRIVILEGE_TYPE：权限类型（如 SELECT、INSERT、UPDATE 等）。
     • IS_GRANTABLE：是否可以授予他人。

10. COLUMN_PRIVILEGES

    • 描述：包含所有列的权限信息。
    • 常用字段：
      • GRANTEE：用户的名称和主机。
      • TABLE_CATALOG：权限所属的目录（通常是 def）。
      • TABLE_SCHEMA：数据库的名称。
      • TABLE_NAME：表的名称。
      • COLUMN_NAME：列的名称。
      • PRIVILEGE_TYPE：权限类型（如 SELECT、INSERT、UPDATE 等）。
      • IS_GRANTABLE：是否可以授予他人。

示例查询

以下是一些常见的查询示例，展示了如何使用 information_schema 数据库获取元数据信息：

1. 查询所有数据库：

   SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;

2. 查询特定数据库中的所有表：

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

3. 查询特定表的所有列：

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

4. 查询特定表的所有索引：

   SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, SEQ_IN_INDEX 
   FROM information_schema.STATISTICS 
   WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

5. 查询特定用户的全局权限：

   SELECT PRIVILEGE_TYPE, IS_GRANTABLE 
   FROM information_schema.USER_PRIVILEGES 
   WHERE GRANTEE = '''username''@''host''';

通过 information_schema 数据库，你可以轻松地获取和管理数据库的元数据信息，这对于数据库管理和优化非常有用。