information_schema 信息数据库

INFORMATION_SCHEMA 简介

INFORMATION_SCHEMA 是一种特殊的数据库，它在 SQL 标准中定义并存在于许多类型的数据库中，用于存储数据库的元数据，包括数据库、表、列、约束、权限等的信息。

具有 INFORMATION_SCHEMA 的数据库系统包括但不限于以下：

• MySQL：MySQL 的 INFORMATION_SCHEMA 包含多个只读视图，用于获取关于数据库对象和服务器实例的元数据。这些视图包括 TABLES、COLUMNS、STATISTICS、SCHEMATA、USER_PRIVILEGES 等。

• PostgreSQL：PostgreSQL 的 INFORMATION_SCHEMA 提供与 MySQL 类似的功能，但具体的视图可能会有所不同。

• SQL Server：SQL Server 的 INFORMATION_SCHEMA 视图包含有关数据库元数据的信息，如 TABLES、COLUMNS、ROUTINES、VIEWS、TABLE_CONSTRAINTS 等。

• MariaDB：MariaDB的 INFORMATION_SCHEMA 与 MySQL 的非常相似，提供大量的视图用于查询数据库元数据。

• Oracle：在 Oracle 中，类似于 INFORMATION_SCHEMA 的元数据信息存储在一系列名为 ALL_, USER_ 和 DBA_ 的视图中。

注意：

• 尽管 INFORMATION_SCHEMA 在不同的数据库系统中都存在，但提供的具体视图和详细信息可能会有所不同。
• 尽管 INFORMATION_SCHEMA 对于数据库元数据的访问非常有用，但是在大型数据库中频繁访问 INFORMATION_SCHEMA 可能会影响性能，因为生成这些信息可能需要消耗大量资源。

INFORMATION_SCHEMA 的一些主要特性

• 标准兼容：INFORMATION_SCHEMA 数据库在许多数据库系统中都存在，因为它是 SQL-92 标准的一部分。这意味着不论使用哪种数据库系统，可以用相同的方式查询元数据。

• 只读：INFORMATION_SCHEMA 中的表是只读的，不能进行插入、删除或更新操作。元数据的更改必须通过执行其他操作（例如，创建新的数据库、更改表结构等）来完成，然后这些更改会自动反映在 INFORMATION_SCHEMA 中。

• 动态数据：INFORMATION_SCHEMA 表中的数据是动态生成的。这意味着，每次查询时，MySQL 会返回表中最新的数据。

INFORMATION_SCHEMA 一些常用表**

SCHEMATA：包含所有数据库的列表以及有关这些数据库的信息，如默认字符集、默认排序规则等

• CATALOG_NAME：数据库所属的目录的名称。在 MySQL 中，此值始终为 def。
• SCHEMA_NAME：数据库的名称。
• DEFAULT_CHARACTER_SET_NAME：数据库的默认字符集。
• DEFAULT_COLLATION_NAME：数据库的默认排序规则。
• SQL_PATH：保留给标准 SQL 使用。在 MySQL 中，此值始终为 NULL。

TABLES：包含所有数据库中的表信息，如表名、表类型（如 BASE TABLE, VIEW 等）、存储引擎、创建时间、更新时间等

• TABLE_CATALOG：表所属的目录的名称。在 MySQL 中，此值始终为 def。
• TABLE_SCHEMA：表所在的数据库的名称。
• TABLE_NAME：表的名称。
• TABLE_TYPE：表的类型。常见的值有 BASE TABLE（表示一张普通表）、VIEW（表示一个视图）和 SEQUENCE（表示一个序列）。
• ENGINE：表的存储引擎（例如 InnoDB、MyISAM）。
• VERSION：表的 .frm 文件的版本号。
• ROW_FORMAT：行存储格式（例如 Fixed、Dynamic、Compressed）。
• TABLE_ROWS：表中行数的估计值。对于 InnoDB 表，此为估算值，可能并非精确值。
• AVG_ROW_LENGTH：平均行长度（以字节为单位）。
• DATA_LENGTH：表的数据文件的长度（以字节为单位），表示实际表数据占用的空间。
• MAX_DATA_LENGTH：表的数据文件的最大长度（以字节为单位），这取决于所使用的数据类型。
• INDEX_LENGTH：表的索引文件的长度（以字节为单位），表示索引数据占用的空间。
• DATA_FREE：分配给表但尚未使用的字节数。
• AUTO_INCREMENT：表的下一个 AUTO_INCREMENT 值。
• CREATE_TIME：表创建的时间。
• UPDATE_TIME：表上次更新的时间。
• CHECK_TIME：表上次检查的时间。
• TABLE_COLLATION：表的排序规则。
• CHECKSUM：实时校验和值（如果有）。
• CREATE_OPTIONS：创建表时使用的其他选项。
• TABLE_COMMENT：对表的注释。

COLUMNS：包含所有表的列信息，如列名、数据类型、是否允许为 NULL、默认值、字符集、排序规则等

• TABLE_CATALOG: 包含该列的表所在的目录的名称。在 MySQL 中，这通常为 def。
• TABLE_SCHEMA: 包含该列的表所在的数据库的名称。
• TABLE_NAME: 包含该列的表的名称。
• COLUMN_NAME: 列的名称。
• ORDINAL_POSITION: 列在表中的位置。
• COLUMN_DEFAULT: 列的默认值。
• IS_NULLABLE: 列是否可以包含 NULL。如果可以，该值为 ‘YES’，否则为 ‘NO’。
• DATA_TYPE: 列的数据类型。
• CHARACTER_MAXIMUM_LENGTH: 对于字符类型的列，这是字符的最大长度。
• CHARACTER_OCTET_LENGTH: 对于字符类型的列，这是最大长度（以字节为单位）。
• NUMERIC_PRECISION: 对于数字数据类型，这是最大精度。
• NUMERIC_SCALE: 对于数字数据类型，这是小数点后的位数。
• DATETIME_PRECISION: 对于时间戳和日期类型的列，这是小数点后的秒数。
• COLUMN_TYPE: 列的数据类型，包括长度和其它属性。
• COLUMN_KEY: 表示该列是否被索引，以及如何被索引。如果 COLUMN_KEY 是 ‘PRI’，那么该列是表的主键。’UNI’ 表示该列是唯一索引的一部分。’MUL’ 表示该列是非唯一索引的一部分，或者它是一个包含多个列的索引的一部分。
• EXTRA: 提供列的额外信息。例如，如果列有一个自动增长的属性，那么这将显示为 ‘auto_increment’。

STATISTICS 包含有关所有表的索引信息，如索引名、索引类型（如 BTREE, HASH 等）、索引方法（如 UNIQUE, FULLTEXT 等）、包含的列等

• TABLE_CATALOG: 包含索引的目录的名称。在 MySQL 中，这通常为 def。
• TABLE_SCHEMA: 包含索引的数据库的名称。
• TABLE_NAME: 索引所在的表的名称。
• NON_UNIQUE: 如果索引不能包含重复项，则为 0；如果可以，则为 1。
• INDEX_SCHEMA: 索引的数据库名。
• INDEX_NAME: 索引的名字。
• SEQ_IN_INDEX: 该列在索引中的位置。
• COLUMN_NAME: 该列的名字。
• COLLATION: 列在索引中的排序方式。可能的值是 ‘A’ (升序) 或 NULL (未排序)。
• CARDINALITY: 索引中唯一值的数量。
• SUB_PART: 索引中列的前缀的长度，如果整个列是被索引的则为 NULL。
• PACKED: 指示索引是否被压缩。
• NULLABLE: 列是否可以包含 NULL。

VIEWS ：包含有关所有视图的信息，如视图名、视图定义（即创建视图时使用的 SELECT 语句）、是否可更新等

• TABLE_CATALOG: 包含视图的目录的名称。在 MySQL 中，这通常为 def。
• TABLE_SCHEMA: 包含视图的数据库的名称。
• TABLE_NAME: 视图的名称。
• VIEW_DEFINITION: 视图定义的 SQL 查询。
• CHECK_OPTION: 指定视图是否满足“WITH CHECK OPTION”。这是一个创建视图时可以指定的选项，它决定了是否允许通过视图进行会对视图定义产生冲突的数据修改。
• IS_UPDATABLE: 如果视图的所有组件都可以进行更新操作，则此值为 ‘YES’；否则，此值为 ‘NO’。
• DEFINER: 创建视图的 MySQL 用户的账户名。
• SECURITY_TYPE: 安全类型。可能的值是 ‘DEFINER’ 或 ‘INVOKER’。

ROUTINES：包含有关所有存储过程和函数的信息，如例程名、例程类型（如 PROCEDURE, FUNCTION 等）、数据类型、字符集、排序规则、SQL 数据访问（如 CONTAINS SQL, READS SQL DATA 等）等

• ROUTINE_CATALOG: 这是包含过程的目录的名称。在 MySQL 中，这通常为 def。
• ROUTINE_SCHEMA: 这是包含过程的数据库的名称。
• ROUTINE_NAME: 这是过程的名称。
• ROUTINE_TYPE: 指定这是一个 PROCEDURE（存储过程）还是 FUNCTION（函数）。
• DATA_TYPE: 这是过程返回值的数据类型（只适用于函数）。
• ROUTINE_BODY: 这是过程体的内容。
• ROUTINE_DEFINITION: 这是过程体的定义。
• CREATED: 这是创建过程的日期和时间。
• LAST_ALTERED: 这是上次修改过程的日期和时间。

TRIGGERS：包含有关所有触发器的信息，如触发器名、触发事件（如 INSERT, UPDATE, DELETE 等）、触发时间（如 BEFORE, AFTER 等）、触发语句（即创建触发器时使用的 SQL 语句）等

• TRIGGER_CATALOG: 触发器所在的目录的名称。在 MySQL 中，这一列通常为 def。
• TRIGGER_SCHEMA: 触发器所在的数据库的名称。
• TRIGGER_NAME: 触发器的名称。
• EVENT_MANIPULATION: 触发器所响应的事件类型，例如 INSERT、UPDATE 或 DELETE。
• EVENT_OBJECT_SCHEMA: 触发器关联的表所在的数据库的名称。
• EVENT_OBJECT_TABLE: 触发器关联的表的名称。
• ACTION_ORDER: 如果多个触发器在同一事件中定义，这个列指定了它们的执行顺序。
• ACTION_CONDITION: 触发器的条件表达式。
• ACTION_STATEMENT: 触发器的 SQL 语句。
• ACTION_ORIENTATION: 指定触发器是在行级别（ROW）还是在语句级别（STATEMENT）上执行。

TABLE_CONSTRAINTS：包含有关所有表约束的信息，如约束名、约束类型（如 PRIMARY KEY, UNIQUE, FOREIGN KEY 等）等

• CONSTRAINT_CATALOG: 约束所在的目录的名称。在 MySQL 中，这一列通常为 def。
• CONSTRAINT_SCHEMA: 约束所在的数据库的名称。
• CONSTRAINT_NAME: 约束的名称。
• TABLE_SCHEMA: 包含约束的表所在的数据库的名称。
• TABLE_NAME: 包含约束的表的名称。
• CONSTRAINT_TYPE: 约束的类型。可能的值包括 UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK。

KEY_COLUMN_USAGE：提供了关于键列（主键、外键、唯一键等）的使用信息

• CONSTRAINT_CATALOG: 指定约束所在的目录的名称。在 MySQL 中，这一列通常为 def。
• CONSTRAINT_SCHEMA: 指定约束所在的数据库的名称。
• CONSTRAINT_NAME: 指定约束的名称。
• TABLE_CATALOG: 指定表所在的目录的名称。在 MySQL 中，这一列通常为 def。
• TABLE_SCHEMA: 指定表所在的数据库的名称。
• TABLE_NAME: 指定包含约束的表的名称。
• COLUMN_NAME: 指定约束中包含的列的名称。
• ORDINAL_POSITION: 如果约束涉及多列，则此列显示列在约束中的位置。第一列的位置为1，第二列的位置为2，等等。
• POSITION_IN_UNIQUE_CONSTRAINT: 如果当前约束是一个外键，那么这一列将显示唯一约束中的列位置，否则为NULL。
• REFERENCED_TABLE_SCHEMA: 对于外键，这个列指定了被引用表所在的数据库的名称，对于其他类型的键，这个列为 NULL。
• REFERENCED_TABLE_NAME: 对于外键，这个列指定了被引用表的名称，对于其他类型的键，这个列为 NULL。
• REFERENCED_COLUMN_NAME: 对于外键，这个列指定了被引用表中的列的名称，对于其他类型的键，这个列为 NULL。

USER_PRIVILEGES：每行都对应一种给定用户的权限。这个表只显示有全局权限的用户。如果想看某个具体数据库的权限或某个具体表的权限，需要查看 SCHEMA_PRIVILEGES 表或 TABLE_PRIVILEGES 表。

• GRANTEE：这个列包含了授予权限的用户的信息。这个信息包括用户名和主机名，它们在一个字符串中用 ‘@’ 符号分隔，整个字符串被单引号引起来。例如，一个用户名为 ‘root’，主机名为 ‘localhost’ 的用户将显示为 “‘root‘@’localhost’”。
• TABLE_CATALOG：这个列总是显示为 ‘def’。这是因为 MySQL 不使用目录（catalogs）的概念，所有的数据库都直接在同一个级别下。
• PRIVILEGE_TYPE：这个列显示了授予给用户的权限类型，例如 ‘SELECT’，’INSERT’，’UPDATE’，’DELETE’ 等。
• IS_GRANTABLE：这个列显示了用户是否能将他们的权限授予给其他用户。如果可以，这个列将显示为 ‘YES’，否则显示为 ‘NO’。

SCHEMA_PRIVILEGES：这个表只显示有全局权限的用户。如果想看某个具体数据库的权限或某个具体表的权限，需要查看 SCHEMA_PRIVILEGES 表或 TABLE_PRIVILEGES

• GRANTEE：这是被授予权限的用户的信息。信息格式是用户名和主机名，用 ‘@’ 符号连接，整个字符串用单引号引起来。
• TABLE_CATALOG：这一列总是显示为 ‘def’。这是因为 MySQL 不使用目录（catalogs）的概念，所有的数据库都直接在同一个级别下。
• TABLE_SCHEMA：这是数据库名。它指示了权限被授予哪个数据库中的表。
• TABLE_NAME：这是表的名字。它表示了权限被授予哪个表。
• PRIVILEGE_TYPE：这是权限类型，例如 ‘SELECT’，’INSERT’，’UPDATE’，’DELETE’ 等。
• IS_GRANTABLE：这个列显示了用户是否能将他们的权限授予给其他用户。如果可以，这个列将显示为 ‘YES’，否则显示为 ‘NO’。

TABLE_PRIVILEGES：表级别权限的信息表。这个表的每一行都对应一个用户的表权限。对于每一行，列信息提供了关于那个权限的详细信息。

• GRANTEE：这是被授予权限的用户的信息。信息格式是用户名和主机名，用 ‘@’ 符号连接，整个字符串用单引号引起来。
• TABLE_CATALOG：这一列总是显示为 ‘def’。这是因为 MySQL 不使用目录（catalogs）的概念，所有的数据库都直接在同一个级别下。
• TABLE_SCHEMA：这是数据库名。它指示了权限被授予哪个数据库中的表。
• TABLE_NAME：这是表的名字。它表示了权限被授予哪个表。
• PRIVILEGE_TYPE：这是权限类型，例如 ‘SELECT’，’INSERT’，’UPDATE’，’DELETE’ 等。
• IS_GRANTABLE：这个列显示了用户是否能将他们的权限授予给其他用户。如果可以，这个列将显示为 ‘YES’，否则显示为 ‘NO’。

COLUMN_PRIVILEGES：列级别权限的信息表。这个表的每一行都对应一个用户的列权限。对于每一行，列信息提供了关于那个权限的详细信息。

• GRANTEE：这是被授予权限的用户的信息。信息格式是用户名和主机名，用 ‘@’ 符号连接，整个字符串用单引号引起来。
• TABLE_CATALOG：这一列总是显示为 ‘def’。这是因为 MySQL 不使用目录（catalogs）的概念，所有的数据库都直接在同一个级别下。
• TABLE_SCHEMA：这是数据库名。它指示了权限被授予哪个数据库中的表。
• TABLE_NAME：这是表的名字。它表示了权限被授予哪个表。
• COLUMN_NAME：这是列的名字。它表示了权限被授予哪个列。
• PRIVILEGE_TYPE：这是权限类型，例如 ‘SELECT’，’INSERT’，’UPDATE’，’DELETE’ 等。
• IS_GRANTABLE：这个列显示了用户是否能将他们的权限授予给其他用户。如果可以，这个列将显示为 ‘YES’，否则显示为 ‘NO’。