information_schema 信息数据库

INFORMATION_SCHEMA 简介

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

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

  • MySQL:MySQL 的 INFORMATION_SCHEMA 包含多个只读视图,用于获取关于数据库对象和服务器实例的元数据。这些视图包括 TABLESCOLUMNSSTATISTICSSCHEMATAUSER_PRIVILEGES 等。

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

  • SQL Server:SQL Server 的 INFORMATION_SCHEMA 视图包含有关数据库元数据的信息,如 TABLESCOLUMNSROUTINESVIEWSTABLE_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:表的存储引擎(例如 InnoDBMyISAM)。
  • VERSION:表的 .frm 文件的版本号。
  • ROW_FORMAT:行存储格式(例如 FixedDynamicCompressed)。
  • 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: 触发器所响应的事件类型,例如 INSERTUPDATEDELETE
  • 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’。

information_schema 信息数据库
https://flepeng.github.io/041-SQL-通用-information-schema-信息数据库/
作者
Lepeng
发布于
2024年3月8日
许可协议