MySQL内置数据库information_schema详解(表说明、表分类)

在关系型数据库管理系统中，information_schema 是一个内置的虚拟数据库，它包含了关于数据库元数据的信息。元数据是指描述数据库结构和对象的数据，如表名、列名、索引、权限等。通过查询 information_schema，用户可以获得关于数据库结构的详细信息，而无需直接访问实际的物理存储文件information_schema 提供了一个标准化的方式来获取数据库的元数据，使得开发者和管理员能够轻松地管理和维护数据库。本文将详细介绍 information_schema 的组成、表分类及其用途，帮助读者更好地理解和利用这一重要的数据库特性。

一、什么是 information_schema

information_schema 是 MySQL 内置的一个虚拟数据库，它并不对应于物理存储的文件，而是由 MySQL 根据当前数据库的状态动态生成的。information_schema 中的每一个表都代表了数据库中的某种元数据信息。通过查询这些表，用户可以获取到数据库中各种对象的详细信息，如表结构、列定义、索引、约束、权限等。

information_schema 的主要作用包括：

获取数据库元数据：通过查询 information_schema 中的表，可以轻松获取数据库的结构信息，如表名、列名、数据类型等。

简化数据库管理：通过 information_schema，管理员可以快速了解数据库的当前状态，便于进行备份、恢复和迁移等操作。

增强开发效率：开发者可以通过 information_schema 自动生成代码，如生成数据库脚本、生成报表等。

支持动态 SQL：在某些情况下，程序可能需要根据运行时的数据库结构动态生成 SQL 查询，information_schema 提供了必要的信息支持。

二、information_schema 的表分类

information_schema 中的表可以按照其功能分为以下几个主要类别：

表结构相关表

权限管理相关表

统计信息相关表

事件调度器相关表

触发器相关表

视图相关表

字符集和校对规则相关表

存储过程和函数相关表

tables 表

tables 表包含了数据库中所有表的元数据信息。通过查询 tables 表，可以获取表的名称、表类型、创建时间、最后修改时间等信息。

字段说明：TABLE_CATALOG: 表所属的目录（通常是 NULL）

TABLE_SCHEMA: 表所属的数据库名称

TABLE_NAME: 表的名称

TABLE_TYPE: 表的类型（如 BASE TABLE 或 VIEW）

ENGINE: 表使用的存储引擎

VERSION: 表的版本号

ROW_FORMAT: 表的行格式

TABLE_ROWS: 表中的行数（估算值）

AVG_ROW_LENGTH: 平均行长度

DATA_LENGTH: 数据长度

MAX_DATA_LENGTH: 最大数据长度

INDEX_LENGTH: 索引长度

DATA_FREE: 未使用的空间

AUTO_INCREMENT: 自增列的下一个值

CREATE_TIME: 表的创建时间

UPDATE_TIME: 表的最后修改时间

CHECK_TIME: 表的最后检查时间

TABLE_COLLATION: 表的默认字符集和校对规则

CHECKSUM: 是否启用校验和

CREATE_OPTIONS: 创建表时的额外选项

TABLE_COMMENT: 表的注释

columns 表

columns 表包含了数据库中所有列的元数据信息。通过查询 columns 表，可以获取列的名称、数据类型、默认值、是否允许为空等信息。

字段说明：TABLE_CATALOG: 列所属的目录（通常是 NULL）

TABLE_SCHEMA: 列所属的数据库名称

TABLE_NAME: 列所属的表名称

COLUMN_NAME: 列的名称

ORDINAL_POSITION: 列在表中的顺序位置

COLUMN_DEFAULT: 列的默认值

IS_NULLABLE: 列是否允许为空

DATA_TYPE: 列的数据类型

CHARACTER_MAXIMUM_LENGTH: 字符列的最大长度

CHARACTER_OCTET_LENGTH: 字符列的最大字节数

NUMERIC_PRECISION: 数值列的精度

NUMERIC_SCALE: 数值列的小数位数

DATETIME_PRECISION: 日期时间列的精度

CHARACTER_SET_NAME: 列的字符集

COLLATION_NAME: 列的校对规则

COLUMN_TYPE: 列的实际数据类型

COLUMN_KEY: 列是否为主键

EXTRA: 列的额外信息（如 AUTO_INCREMENT）

PRIVILEGES: 列的权限信息

COLUMN_COMMENT: 列的注释

user_privileges 表

user_privileges 表包含了用户在全局范围内的权限信息。通过查询 user_privileges 表，可以获取用户的权限级别和权限范围。

字段说明：GRANTEE: 用户名

TABLE_CATALOG: 权限所属的目录（通常是 NULL）

PRIVILEGE_TYPE: 权限类型

IS_GRANTABLE: 是否可以授予其他用户

table_privileges 表

table_privileges 表包含了用户在特定表上的权限信息。通过查询 table_privileges 表，可以获取用户在某个表上的权限级别和权限范围。

字段说明：GRANTEE: 用户名

TABLE_CATALOG: 权限所属的目录（通常是 NULL）

TABLE_SCHEMA: 权限所属的数据库名称

TABLE_NAME: 权限所属的表名称

PRIVILEGE_TYPE: 权限类型

IS_GRANTABLE: 是否可以授予其他用户

statistics 表

statistics 表包含了表的索引信息。通过查询 statistics 表，可以获取表的索引名称、索引类型、唯一性等信息。

字段说明：TABLE_CATALOG: 索引所属的目录（通常是 NULL）

TABLE_SCHEMA: 索引所属的数据库名称

TABLE_NAME: 索引所属的表名称

NON_UNIQUE: 索引是否唯一

INDEX_SCHEMA: 索引所属的数据库名称

INDEX_NAME: 索引名称

SEQ_IN_INDEX: 索引中的列序号

COLUMN_NAME: 索引中的列名称

COLLATION: 列的排序规则

CARDINALITY: 索引的基数

SUB_PART: 列的前缀长度

PACKED: 是否压缩

NULLABLE: 列是否允许为空

INDEX_TYPE: 索引类型

COMMENT: 索引的注释

events 表

events 表包含了数据库中所有事件的元数据信息。通过查询 events 表，可以获取事件的名称、调度器名称、触发条件、执行频率等信息。

字段说明：EVENT_CATALOG: 事件所属的目录（通常是 NULL）

EVENT_SCHEMA: 事件所属的数据库名称

EVENT_NAME: 事件的名称

DEFINER: 事件的创建者

TIME_ZONE: 事件的时间区域

EVENT_BODY: 事件的主体

EVENT_DEFINITION: 事件的定义

EVENT_TYPE: 事件的类型

EXECUTE_AT: 事件的执行时间

INTERVAL_VALUE: 事件的间隔值

INTERVAL_FIELD: 事件的间隔单位

SQL_MODE: SQL 模式

STARTS: 事件的开始时间

ENDS: 事件的结束时间

STATUS: 事件的状态

ON_COMPLETION: 事件完成后的处理方式

CREATED: 事件的创建时间

LAST_ALTERED: 事件的最后修改时间

LAST_EXECUTED: 事件的最后执行时间

EVENT_COMMENT: 事件的注释

triggers 表

triggers 表包含了数据库中所有触发器的元数据信息。通过查询 triggers 表，可以获取触发器的名称、触发时机、触发事件、触发条件等信息。

字段说明：TRIGGER_CATALOG: 触发器所属的目录（通常是 NULL）

TRIGGER_SCHEMA: 触发器所属的数据库名称

TRIGGER_NAME: 触发器的名称

EVENT_MANIPULATION: 触发事件（如 INSERT、UPDATE、DELETE）

EVENT_OBJECT_TABLE: 触发事件的对象表

ACTION_ORDER: 动作的顺序

ACTION_CONDITION: 动作的条件

ACTION_STATEMENT: 动作的 SQL 语句

ACTION_ORIENTATION: 动作的方向

ACTION_TIMING: 动作的时机

ACTION_REFERENCE_OLD_TABLE: 动作引用的旧表

ACTION_REFERENCE_NEW_TABLE: 动作引用的新表

ACTION_REFERENCE_OLD_ROW: 动作引用的旧行

ACTION_REFERENCE_NEW_ROW: 动作引用的新行

CREATED: 触发器的创建时间

SQL_MODE: SQL 模式

DEFINER: 触发器的创建者

CHARACTER_SET_CLIENT: 客户端的字符集

COLLATION_CONNECTION: 客户端的校对规则

DATABASE_COLLATION: 数据库的校对规则

views 表

views 表包含了数据库中所有视图的元数据信息。通过查询 views 表，可以获取视图的名称、定义、创建时间等信息。

字段说明：TABLE_CATALOG: 视图所属的目录（通常是 NULL）

TABLE_SCHEMA: 视图所属的数据库名称

TABLE_NAME: 视图的名称

VIEW_DEFINITION: 视图的定义

CHECK_OPTION: 是否启用检查选项

IS_UPDATABLE: 视图是否可更新

DEFINER: 视图的创建者

SECURITY_TYPE: 安全类型

CHARACTER_SET_CLIENT: 客户端的字符集

COLLATION_CONNECTION: 客户端的校对规则

CREATED: 视图的创建时间

LAST_ALTERED: 视图的最后修改时间

character_sets 表

character_sets 表包含了数据库中所有字符集的元数据信息。通过查询 character_sets 表，可以获取字符集的名称、编码、最大长度等信息。

字段说明：CHARACTER_SET_NAME: 字符集的名称

DEFAULT_COLLATE_NAME: 默认的校对规则

DESCRIPTION: 字符集的描述

MAXLEN: 字符集的最大长度

collations 表

collations 表包含了数据库中所有校对规则的元数据信息。通过查询 collations 表，可以获取校对规则的名称、字符集、排序规则等信息。

字段说明：COLLATION_NAME: 校对规则的名称

CHARACTER_SET_NAME: 校对规则所属的字符集

ID: 校对规则的 ID

IS_DEFAULT: 是否为默认校对规则

IS_COMPILED: 是否已编译

SORTLEN: 排序长度

routines 表

routines 表包含了数据库中所有存储过程和函数的元数据信息。通过查询 routines 表，可以获取存储过程和函数的名称、定义、参数列表等信息。

字段说明：SPECIFIC_NAME: 存储过程或函数的名称

ROUTINE_CATALOG: 存储过程或函数所属的目录（通常是 NULL）

ROUTINE_SCHEMA: 存储过程或函数所属的数据库名称

ROUTINE_NAME: 存储过程或函数的名称

ROUTINE_TYPE: 存储过程或函数的类型

DATA_TYPE: 返回值的数据类型

CHARACTER_MAXIMUM_LENGTH: 字符列的最大长度

CHARACTER_OCTET_LENGTH: 字符列的最大字节数

NUMERIC_PRECISION: 数值列的精度

NUMERIC_SCALE: 数值列的小数位数

DATETIME_PRECISION: 日期时间列的精度

CHARACTER_SET_NAME: 字符集

COLLATION_NAME: 校对规则

DTD_IDENTIFIER: 数据类型的定义

ROUTINE_BODY: 存储过程或函数的主体

ROUTINE_DEFINITION: 存储过程或函数的定义

EXTERNAL_NAME: 外部名称

EXTERNAL_LANGUAGE: 外部语言

PARAMETER_STYLE: 参数风格

IS_DETERMINISTIC: 是否确定

SQL_DATA_ACCESS: SQL 数据访问

SQL_PATH: SQL 路径

SECURITY_TYPE: 安全类型

CREATED: 存储过程或函数的创建时间

LAST_ALTERED: 存储过程或函数的最后修改时间

SQL_MODE: SQL 模式

DEFINER: 存储过程或函数的创建者

CHARACTER_SET_CLIENT: 客户端的字符集

COLLATION_CONNECTION: 客户端的校对规则

DATABASE_COLLATION: 数据库的校对规则

三、information_schema 的实际应用

通过查询 information_schema，管理员可以轻松获取数据库的当前状态，包括表的数量、索引的数量、存储引擎的使用情况等。这些信息可以帮助管理员进行备份、恢复和迁移等操作。

开发者可以通过查询 information_schema 自动生成代码，如生成数据库脚本、生成报表等。例如，通过查询 tables 表和 columns 表，可以生成创建表的 SQL 脚本。

通过查询 statistics 表，可以获取表的索引信息，帮助优化查询性能。例如，通过分析索引的基数和唯一性，可以决定是否需要添加新的索引或调整现有的索引。

通过查询 user_privileges 表和 table_privileges 表，可以检查用户的权限设置，确保数据库的安全性。例如，通过查询 user_privileges 表，可以检查是否存在不必要的全局权限。

在某些情况下，程序可能需要根据运行时的数据库结构动态生成 SQL 查询。通过查询 information_schema，程序可以获取到所需的元数据信息，从而实现自动化任务。

综上所述，information_schema 是 MySQL 内置的一个非常有用的虚拟数据库，它包含了关于数据库元数据的详细信息。通过查询 information_schema 中的各种表，用户可以获取到数据库的结构、权限、统计信息、事件调度器、触发器、视图、字符集和校对规则、存储过程和函数等信息。information_schema 在数据库维护、开发辅助、性能优化、安全管理、自动化任务等方面都有着广泛的应用。掌握 information_schema 的使用方法，不仅可以提高工作效率，还可以更好地管理和维护数据库。未来，随着 MySQL 的不断发展，information_schema 将会提供更多的功能和更详细的元数据信息，为用户提供更加全面的支持。

以上就是php小编整理的全部内容，希望对您有所帮助，更多相关资料请查看php教程栏目。