23.1. INFORMATION_SCHEMA表 - MySQL 中文手册

返回介绍

23.1. INFORMATION_SCHEMA表

发布于 2019-06-13 字数 25468 浏览 998 评论 0

23.1.1. INFORMATION_SCHEMA SCHEMATA表
23.1.2. INFORMATION_SCHEMA TABLES表
23.1.3. INFORMATION_SCHEMA COLUMNS表
23.1.4. INFORMATION_SCHEMA STATISTICS表
23.1.5. INFORMATION_SCHEMA USER_PRIVILEGES表
23.1.6. INFORMATION_SCHEMA SCHEMA_PRIVILEGES表
23.1.7. INFORMATION_SCHEMA TABLE_PRIVILEGES表
23.1.8. INFORMATION_SCHEMA COLUMN_PRIVILEGES表
23.1.9. INFORMATION_SCHEMA CHARACTER_SETS表
23.1.10. INFORMATION_SCHEMA COLLATIONS表
23.1.11. INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY表
23.1.12. INFORMATION_SCHEMA TABLE_CONSTRAINTS表
23.1.13. INFORMATION_SCHEMA KEY_COLUMN_USAGE表
23.1.14. INFORMATION_SCHEMA ROUTINES表
23.1.15. INFORMATION_SCHEMA VIEWS表
23.1.16. INFORMATION_SCHEMA TRIGGERS表
23.1.17. 其他INFORMATION_SCHEMA表

下述章节说明

在下面的章节中,我们选择了INFORMATION_SCHEMA中的表和列。对于每一列,有三类信息:

·“标准名称”:指明了列的标准SQL名称。

·“SHOW名称”:指明了最近SHOW语句中的等效字段名,如果有的话。

·“注释”给出了适用的附加信息。

为了避免使用标准或DB2、SQL服务器或Oracle中保留的名称,我们更改了标注为“SQL扩展”的列名。(例如,在TABLES表中,我们将COLLATION改为TABLE_COLLATION)。请参见本文末尾处给出的保留字列表。http://www.dbazine.com/gulutzan5.shtml。

字符列(例如TABLES.TABLE_NAME)定义通常是VARCHAR(N) CHARACTER SET utf8,其中,N至少为64。

在每一部分中,指明了等效于从INFORMATION_SCHEMA中检索信息的SELECT语句的SHOW语句,或者不存在这类语句。

注释:目前,有一些丢失的列和一些混乱的列。我们正在着手解决该问题,并随着变化情况更新文档。

23.1.1. INFORMATION_SCHEMA SCHEMATA表

该方案是数据库,因此SCHEMATA表提供了关于数据库的信息。

标准名称

SHOW名称

注释

CATALOG_NAME

NULL

SCHEMA_NAME

Database

DEFAULT_CHARACTER_SET_NAME

DEFAULT_COLLATION_NAME

SQL_PATH

NULL

注释:SQL_PATH列的之总为NULL。

下述语句是等效的:

SELECT SCHEMA_NAME AS `Database
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE 'wild']
 
SHOW DATABASES
[LIKE 'wild']

23.1.2. INFORMATION_SCHEMA TABLES表

TABLES表给出了关于数据库中的表的信息。

标准名称

SHOW名称

注释

TABLE_CATALOG

NULL

TABLE_SCHEMA

Table_…

TABLE_NAME

Table_…

TABLE_TYPE

ENGINE

Engine

MySQL扩展

VERSION

Version

MySQL扩展

ROW_FORMAT

Row_format

MySQL扩展

TABLE_ROWS

Rows

MySQL扩展

AVG_ROW_LENGTH

Avg_row_length

MySQL扩展

DATA_LENGTH

Data_length

MySQL扩展

MAX_DATA_LENGTH

Max_data_length

MySQL扩展

INDEX_LENGTH

Index_length

MySQL扩展

DATA_FREE

Data_free

MySQL扩展

AUTO_INCREMENT

Auto_increment

MySQL扩展

CREATE_TIME

Create_time

MySQL扩展

UPDATE_TIME

Update_time

MySQL扩展

CHECK_TIME

Check_time

MySQL扩展

TABLE_COLLATION

Collation

MySQL扩展

CHECKSUM

Checksum

MySQL扩展

CREATE_OPTIONS

Create_options

MySQL扩展

TABLE_COMMENT

Comment

MySQL扩展

注释:

·TABLE_SCHEMA和TABLE_NAME是SHOW显示中的单个字段,例如Table_in_db1。

·TABLE_TYPE(表类型)应是BASE TABLE(基本表)或VIEW(视图)。如果表是临时性的,TABLE_TYPE = TEMPORARY。(没有临时视图,因此,因此不存在歧义)。

·如果表位于INFORMATION_SCHEMA数据库中,TABLE_ROWS列为NULL。对于InnoDB表,在SQL优化中,行计数仅是大概估计值。

·没有关于表默认字符集的任何信息。TABLE_COLLATION处于关闭状态,原因在于校对名称以字符集名称开头。

下述语句是等效的:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  [WHERE table_schema = 'db_name']
  [WHERE|AND table_name LIKE 'wild']
 
SHOW TABLES
  [FROM db_name]
  [LIKE 'wild']

23.1.3. INFORMATION_SCHEMA COLUMNS表

COLUMNS表给出了表中的列信息。

标准名称

SHOW名称

注释

TABLE_CATALOG

NULL

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

Field

ORDINAL_POSITION

参见注释

COLUMN_DEFAULT

Default

IS_NULLABLE

Null

DATA_TYPE

Type

CHARACTER_MAXIMUM_LENGTH

Type

CHARACTER_OCTET_LENGTH

NUMERIC_PRECISION

Type

NUMERIC_SCALE

Type

CHARACTER_SET_NAME

COLLATION_NAME

Collation

COLUMN_TYPE

Type

MySQL扩展

COLUMN_KEY

Key

MySQL扩展

EXTRA

Extra

MySQL扩展

COLUMN_COMMENT

Comment

MySQL扩展

注释:

·在SHOW中,类型显示包括来自数个不同COLUMNS列的值。

·ORDINAL_POSITION有必要,这是因为,你可能会在某一天需要ORDER BY ORDINAL_POSITION(按ORDINAL_POSITION排序)。不同于SHOW,SELECT没有自动排序功能。

·CHARACTER_OCTET_LENGTH应与CHARACTER_MAXIMUM_LENGTH相同,但多字节字符集除外。

·CHARACTER_SET_NAME可由Collation(校对)导出。例如,如果给出了“SHOW FULL COLUMNS FROM t”,在Collation(校对)列中将见到latin1_swedish_ci的值,字符集由第1个下划线前的名称指明。latin1.

下述语句是等效的:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']
 
SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE wild]

23.1.4. INFORMATION_SCHEMA STATISTICS表

STATISTICS表给出了关于表索引的信息。

标准名称

SHOW名称

注释

TABLE_CATALOG

NULL

TABLE_SCHEMA

=数据库

TABLE_NAME

Table

NON_UNIQUE

Non_unique

INDEX_SCHEMA

=数据库

INDEX_NAME

Key_name

SEQ_IN_INDEX

Seq_in_index

COLUMN_NAME

Column_name

COLLATION

Collation

CARDINALITY

Cardinality

SUB_PART

Sub_part

MySQL扩展

PACKED

Packed

MySQL扩展

NULLABLE

Null

MySQL扩展

INDEX_TYPE

Index_type

MySQL扩展

COMMENT

Comment

MySQL扩展

注释:

·没有关于这些索引的标准表。上面的列表与SQL服务器2000中sp_statistics返回的值类似。不同之处在于用CATALOG替换了QUALIFIER,并用SCHEMA替换了OWNER。

显而易见,前述表和SHOW INDEX的输出均是由相同的父对象导出的。因此,相关性已关闭。

下述语句是等效的:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
 
SHOW INDEX
  FROM tbl_name
  [FROM db_name]

23.1.5. INFORMATION_SCHEMA USER_PRIVILEGES表

USER_PRIVILEGES(用户权限)表给出了关于全程权限的信息。该信息源自mysql.user授权表。

标准名称

SHOW名称

注释

GRANTEE

例如“user'@'host”

TABLE_CATALOG

NULL

PRIVILEGE_TYPE

IS_GRANTABLE

注释:

·这是一个非标准表。其值来自mysql.user表。

23.1.6. INFORMATION_SCHEMA SCHEMA_PRIVILEGES表

SCHEMA_PRIVILEGES(方案权限)表给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。

标准名称

SHOW名称

注释

GRANTEE

例如“user'@'host”

TABLE_CATALOG

NULL

TABLE_SCHEMA

PRIVILEGE_TYPE

IS_GRANTABLE

注释:

·这是一个非标准表。其值来自mysql.db表。

23.1.7. INFORMATION_SCHEMA TABLE_PRIVILEGES表

TABLE_PRIVILEGES(表权限)表给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。

标准名称

SHOW名称

注释

GRANTEE

例如“user'@'host”

TABLE_CATALOG

NULL

TABLE_SCHEMA

TABLE_NAME

PRIVILEGE_TYPE

IS_GRANTABLE

下述语句不等效:

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
 
SHOW GRANTS ...

PRIVILEGE_TYPE可以包含这些值之一(仅能一个):SELECT、INSERT、UPDATE、REFERENCES、ALTER、INDEX、DROP、CREATE VIEW。

23.1.8. INFORMATION_SCHEMA COLUMN_PRIVILEGES表

COLUMN_PRIVILEGES(列权限)表给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。

标准名称

SHOW名称

注释

GRANTEE

例如“user'@'host”

TABLE_CATALOG

NULL

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

PRIVILEGE_TYPE

IS_GRANTABLE

注释:

·在SHOW FULL COLUMNS(显示完整列)的输出中,权限值位于一个字段并采用小写形式,例如select、insert、update、references。在COLUMN_PRIVILEGES中,每种权限占一行,并为大写形式。

·PRIVILEGE_TYPE可以包含这些值之一(仅能一个):SELECT,INSERT,UPDATE,REFERENCES.

·如果用户有GRANT OPTION权限,那么IS_GRANTABLE应为YES。否则,IS_GRANTABLE应为NO。在输出中,不会将GRANT OPTION作为单独权限列出。

下述语句不等效:

SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
 
SHOW GRANTS ...

23.1.9. INFORMATION_SCHEMA CHARACTER_SETS表

CHARACTER_SETS(字符集)表提供了关于可用字符集的信息。

标准名称

SHOW名称

注释

CHARACTER_SET_NAME

Charset

DEFAULT_COLLATE_NAME

Default collation

DESCRIPION

Description

MySQL扩展

MAXLEN

Maxlen

MySQL扩展

注释:

·我们增加了两个非标准列,分别对应于SHOW CHARACTER SET输出的Description(描述)和Maxlen(最大长度)列。

下述语句是等效的:

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
  [WHERE name LIKE 'wild']
 
SHOW CHARACTER SET
  [LIKE 'wild']

23.1.10. INFORMATION_SCHEMA COLLATIONS表

COLLATIONS表提供了关于各字符集的对照信息。

标准名称

SHOW名称

注释

COLLATION_NAME

Collation

注释:

·我们增加了5个非标准列,分别对应于SHOW COLLATION输出的Charset、Id、Default、Compiled和Sortlen列。

下述语句是等效的:

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE collation_name LIKE 'wild']
 
SHOW COLLATION
  [LIKE 'wild']

23.1.11. INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY表

COLLATION_CHARACTER_SET_APPLICABILITY表指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

标准名称

SHOW名称

注释

COLLATION_NAME

Collation

CHARACTER_SET_NAME

Charset

23.1.12. INFORMATION_SCHEMA TABLE_CONSTRAINTS表

TABLE_CONSTRAINTS表描述了存在约束的表。

标准名称

SHOW名称

注释

CONSTRAINT_CATALOG

NULL

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

TABLE_SCHEMA

TABLE_NAME

CONSTRAINT_TYPE

注释:

·CONSTRAINT_TYPE的值可以是UNIQUE(唯一)、PRIMARY KEY(主键)或FOREIGN KEY(外键)。

·当Non_unique字段为0时,UNIQUE和PRIMARY KEY信息与SHOW INDEX输出的Key_name字段中给出的信息基本相同。

·CONSTRAINT_TYPE列可包含下述值之一:UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK。这是一个CHAR(非ENUM)列。在我们支持CHECK前,CHECK值不可用。

23.1.13. INFORMATION_SCHEMA KEY_COLUMN_USAGE表

KEY_COLUMN_USAGE表描述了具有约束的键列。

标准名称

SHOW名称

注释

CONSTRAINT_CATALOG

NULL

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

ORDINAL_POSITION

POSITION_IN_UNIQUE_CONSTRAINT

REFERENCED_TABLE_SCHEMA

REFERENCED_TABLE_NAME

REFERENCED_COLUMN_NAME

注释:

·如果约束为外键,这就是外键列,而不是外键引用的列。

·ORDINAL_POSITION的值是列在约束中的位置,而不是列在表中的位置。列位置采用从1开始的数值编号。

·对于“唯一”和“主键”约束,POSITION_IN_UNIQUE_CONSTRAINT的值为NULL。对于“外键”约束,它是所引用表内键中的顺序位置。

例如,假定有两个具有下述定义的表t1和t3:

CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
) ENGINE=InnoDB;
 
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;

对于这两个表,KEY_COLUMN_USAGE表有两行:

o一行含有CONSTRAINT_NAME='PRIMARY',TABLE_NAME='t1',COLUMN_NAME='s3',ORDINAL_POSITION=1,POSITION_IN_UNIQUE_CONSTRAINT=NULL。

o另一行含有CONSTRAINT_NAME='CO',TABLE_NAME='t3',COLUMN_NAME='s2',ORDINAL_POSITION=1,POSITION_IN_UNIQUE_CONSTRAINT=1。

23.1.14. INFORMATION_SCHEMA ROUTINES表

ROUTINES表提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES表不包含自定义函数(UDF)。

名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列,如果有的话。

标准名称

mysql.proc

注释

SPECIFIC_NAME

specific_name

ROUTINE_CATALOG

NULL

ROUTINE_SCHEMA

db

ROUTINE_NAME

name

ROUTINE_TYPE

type

{PROCEDURE|FUNCTION}

DTD_IDENTIFIER

(数据类型描述符)

ROUTINE_BODY

SQL

ROUTINE_DEFINITION

body

EXTERNAL_NAME

NULL

EXTERNAL_LANGUAGE

language

NULL

PARAMETER_STYLE

SQL

IS_DETERMINISTIC

is_deterministic

SQL_DATA_ACCESS

sql_data_access

SQL_PATH

NULL

SECURITY_TYPE

security_type

CREATED

created

LAST_ALTERED

modified

SQL_MODE

sql_mode

MySQL扩展

ROUTINE_COMMENT

comment

MySQL扩展

DEFINER

definer

MySQL扩展

注释:

·MySQL计算EXTERNAL_LANGUAGE,因此:

o如果mysql.proc.language='SQL',那么EXTERNAL_LANGUAGE为NULL。

o否则,EXTERNAL_LANGUAGE为mysql.proc.language中的值。然而,由于尚没有外部语言,因此该值总为NULL。

23.1.15. INFORMATION_SCHEMA VIEWS表

VIEWS表给出了关于数据库中的视图的信息。

标准名称

SHOW名称

注释

TABLE_CATALOG

NULL

TABLE_SCHEMA

TABLE_NAME

VIEW_DEFINITION

CHECK_OPTION

IS_UPDATABLE

DEFINER

SECURITY_TYPE

注释:

·有一种新的权限SHOW VIEW,如果没有它,将无法看到VIEWS表。

·VIEW_DEFINITION列含有你在SHOW CREATE VIEW所生成的Create Table字段中见到的大多数信息。跳过SELECT前的单词,并跳过具有CHECK OPTION(检查选项)的单词。例如,如果初始语句是:

·CREATE VIEW v AS
·  SELECT s2,s1 FROM t
·  WHERE s1 > 5
·  ORDER BY s1
·  WITH CHECK OPTION;

那么视图定义为:

SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1

·CHECK_OPTION列的值总为NONE。

·如果视图是可更新的,IS_UPDATABLE列的值为YES,如果视图是不可更新的,IS_UPDATABLE列的值为NO。

·DEFINER列指明了定义视图的人。SECURITY_TYPE的值为DEFINER或INVOKER。

23.1.16. INFORMATION_SCHEMA TRIGGERS表

TRIGGERS表提供了关于触发程序的信息。

必须有SUPER权限才能查看该表。

标准名称

SHOW名称

注释

TRIGGER_CATALOG

NULL

TRIGGER_SCHEMA

TRIGGER_NAME

Trigger

EVENT_MANIPULATION

Event

EVENT_OBJECT_CATALOG

NULL

EVENT_OBJECT_SCHEMA

EVENT_OBJECT_TABLE

Table

ACTION_ORDER

0

ACTION_CONDITION

NULL

ACTION_STATEMENT

Statement

ACTION_ORIENTATION

ROW

ACTION_TIMING

Timing

ACTION_REFERENCE_OLD_TABLE

NULL

ACTION_REFERENCE_NEW_TABLE

NULL

ACTION_REFERENCE_OLD_ROW

OLD

ACTION_REFERENCE_NEW_ROW

NEW

CREATED

NULL (0)

SQL_MODE

注释:

·TRIGGER_SCHEMA和TRIGGER_NAME列中分别含有相应数据库的名称以及触发程序的名称,在该数据库中,含有该触发程序。

·EVENT_MANIPULATION列含有下述值之一:INSERT、DELETE、或UPDATE。

·正如第21章:触发程序中指出的那样,每个触发程序均与一个表准确相关。EVENT_OBJECT_SCHEMA和EVENT_OBJECT_TABLE列包含相应的数据库和表名,在该数据库中,含有该表。

·ACTION_ORDER语句含有触发程序动作(在相同表上所有类似触发程序列表中)的顺序位置。目前该值总为0,这是因为在相同表上具有相同EVENT_MANIPULATION和ACTION_TIMING的触发程序不能超过1个。

·ACTION_STATEMENT列含有激活了触发程序时将要执行的语句。这与SHOW TRIGGERS输出的Statement(语句)列中显示的文本相同。注意,该文本采用了UTF-8编码方式。

·ACTION_ORIENTATION列总含有值“ROW”。

·ACTION_TIMING列含有下述两种值之一:“BEFORE”或“AFTER”。

·列ACTION_REFERENCE_OLD_ROW和ACTION_REFERENCE_NEW_ROW分别含有旧的和新的列标识符。这意味着ACTION_REFERENCE_OLD_ROW总含有值“OLD”,ACTION_REFERENCE_NEW_ROW总含有值“NEW”。

·SQL_MODE列显示了创建触发程序时有效的服务器SQL模式(无论当前的服务器SQL模式为何,只要激活了触发程序,它将保持有效)。该列的可能取值范围与sql_mode系统变量的取值范围相同。请参见5.3.2节,“SQL服务器模式”。

·在下述列中,目前总含有NULL:TRIGGER_CATALOG,EVENT_OBJECT_CATALOG,ACTION_CONDITION,ACTION_REFERENCE_OLD_TABLE,ACTION_REFERENCE_NEW_TABLE和CREATED。

例如,使用21.3节,“使用触发程序”中定义的触发程序ins_sum。

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
 TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
 TRIGGER_NAME: ins_sum
  EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
 EVENT_OBJECT_SCHEMA: test
  EVENT_OBJECT_TABLE: account
 ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT:  SET @sum = @sum + NEW.amount
  ACTION_ORIENTATION: ROW
 ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
1 row in set (1.54 sec)

另请参见13.5.4.20节,“SHOW TRIGGERS语法”。

23.1.17. 其他INFORMATION_SCHEMA表

我们打算实施附加的INFORMATION_SCHEMA表。尤其是,我们确认了对INFORMATION_SCHEMA.PARAMETERS和INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS的需求。

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

目前还没有任何评论,快来抢沙发吧!