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'。
|