扩展[教材]第23章：INFORMATION_SCHEMA信息数据库

第23章 INFORMATI ON_SCHEMA信息数据库

第23章 I NFORMATION_SCHEMA信息数据库

目录

23. 1. I NFORMATION_S CHEMA表

23. 1. 1. INFORMAT ION_SCHEMA S CHEMATA表

23. 1.2. INFORMAT ION_SCHEMA TABLES表

23. 1.3. INFORMAT ION_SCHEMA COLUMNS表

23. 1.4. INFORMAT ION_SCHEMA STAT IST ICS表

23. 1.5. INFORMAT ION_SCHEMA USER_PRIV ILEGES表

23. 1.6. INFORMAT ION_SCHEMA SCHEMA_PRIVI LEGES表

23. 1.7. INFORMAT ION_SCHEMA TABLE_PRIVILEGES表

23. 1.8. INFORMAT ION_SCHEMA COLUMN_PRIVI LEGES表

23. 1.9. INFORMAT ION_SCHEMA CHARACTER_SETS表

23. 1. 10. INFORMATION_SCHEMA COLLAT IONS表

23. 1. 11. I NFORMATION_SCHEMA

COLLATI ON_CHARACTER_SET_APPL ICABIL ITY表

23. 1. 12. I NFORMATION_SCHEMA TABLE_CONSTRAI NTS表

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.其他I NFORMATION_SCHEMA表

23.2. S HOW语句的扩展

I NFORMATION_SCHEMA提供了访问数据库元数据的方式。

元数据是关于数据的数据如数据库名或表名列的数据类型或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和

“系统目录” 。

例如mysql> SELECT table_name, table_type, engine-> FROMinformation_schema. tables-> WHERE table_schema = 'db5'->

ORDER BY table_name DESC; table_name 表名 table_type 表类型 引擎v56 VIEW 视图 NULLv3 VIEW 视图

NULLv2 VIEW 视图

NULLv VIEW 视图

NULLtables BASE TABLE 基本表 MyISAMt7 BASE TABLE 基本表 MyISAMt3 BASE TABLE 基本表 MyISAMt2 BASE TABLE 基本表 MyISAMt BASE TABLE 基本表 MyISAMpk BASE TABLE 基本表 InnoDBloop BASE TABLE 基本表 MyISAMkurs BASE TABLE 基本表 MyISAMk BASE TABLE 基本表 MyISAMinto BASE TABLE 基本表 MyISAMgoto BASE TABLE 基本表 MyISAMfk2 BASE TABLE 基本表 InnoDBfk BASE TABLE 基本表 InnoDB

集合中含17行0.01秒。解释该语句请求按逆向字母顺序列出

数据库db 5中的所有表但仅显示三种信息表名表类型 以及表引擎。

INFORMATION_SCHEMA是信息数据库其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在I NF ORMAT I ON_SC HEMA中有数个只读表。它们实际上是视图而不是基本表因此你将无法看到与之相关的任何文件。

每位MySQL用户均有权访问这些表但仅限于表中的特定行在这类行中含有用户具有恰当访问权限的对象。

SELECT的优点

SELECT . . . FROM INFORMATI ON_SCHEMA语句的目的在于提供一种更为一致的方式 以访问MyS QL所支持的各种SHOW语句SHOWDATABASES、SHOW TABLES等等提供的信息。与SHOW相比使用SELECT有多项优点“

· 符合Codd规则。也就是说所有访问均是在表上进行的。· 不需要了解新语句的语法。由于他们已知道SELECT的工作方式仅需了解对象名即可。

· 实现人无需操心增加关键词方面的事宜。

· 有数百万种可能的输出变化而不是一种。这样就为对元数据有不同需求的应用程序提供了更高的灵活性。

· 由于其他DBMS也采用了这类方式移植更为容易。然而由于SHOW在My SQL的雇员和用户中十分流行如果SHOW消失可能会导致混乱 因此传统的语法方式无法给出消除S H OW的足够理由。事实上在MySQL 5. 1中还对SHOW进行了多项增强。关于这方面的介绍请参见23.2节 “S HOW语句的扩展” 。

标准

在MySQL中 I NFORMATION_SCHEMA表结构的实施遵从“ANS I/ISO

SQL:2003标准第11部分纲要” 。我们的目的在于获得与SQL:2003核心特性F021 “基本信息方案”的近似兼容。

SQL服务器2000 也遵从该标准的用户可能已注意到它们高度的相似性。但是 MySQL略去了与我们的实施方式不相关的众多列并添加了一些MySQL特有的列。其中一种列就是

I NFO RMAT I ON_S CHEMA.TAB LES表中的引擎列。

尽管其他DBMS使用了不同的名称如sy scat或系统但标准名称是I NFORMATION_SCHEMA。

事实上尽管不需要生成名为INFORMAT I ON_SCHEMA的文件我们仍提供了名为INFORMATION_SCHEMA的新数据库。可以使用USE语句将I NFORMATION_SCHEMA选择为默认数据库但访问该数据库中所含表的唯一方式是使用SELECT语句。不能在其中插入内容不能更新它们也不能删除其中的内容。

权限

当前权限SHOW要求和SELCET权限要求不存在差别。在任何一种情况下要想查看关于它的信息需要对某类对象拥有特定权限。

23. 1. I NFORMATION_SCHEMA表

23. 1. 1. INFORMAT ION_SCHEMA S CHEMATA表

23. 1.2. INFORMAT ION_SCHEMA TABLES表

23. 1.3. INFORMAT ION_SCHEMA COLUMNS表

23. 1.4. INFORMAT ION_SCHEMA STAT IST ICS表

23. 1.5. INFORMAT ION_SCHEMA USER_PRIV ILEGES表

23. 1.6. INFORMAT ION_SCHEMA SCHEMA_PRIVI LEGES表

23. 1.7. INFORMAT ION_SCHEMA TABLE_PRIVILEGES表

23. 1.8. INFORMAT ION_SCHEMA COLUMN_PRIVI LEGES表

23. 1.9. INFORMAT ION_SCHEMA CHARACTER_SETS表

23. 1. 10. INFORMATION_SCHEMA COLLAT IONS表

23. 1. 11. I NFORMATION_SCHEMA

COLLATI ON_CHARACTER_SET_APPL ICABIL ITY表

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.其他I NFORMATION_SCHEMA表

下述章节说明

在下面的章节中我们选择了INFORMAT I ON_SCHEMA中的表和列。对于每一列有三类信息

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

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

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

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

字符列例如TABLE S.TABLE_NAME定义通常是VARCHAR(N) CHARACTERSET utf8其中 N至少为64。

在每一部分中指明了等效于从I NFO RMA T I ON_S CH EMA中检索信息的S ELECT语句的SHOW语句或者不存在这类语句。

注释 目前有一些丢失的列和一些混乱的列。我们正在着手解决该

问题并随着变化情况更新文档。

23. 1. 1. INFORMAT ION_SCHEMA S CHEMATA表

该方案是数据库 因此S CH EMA TA表提供了关于数据库的信息。标准名称

SHOW名称

注释

CATALOG_NAME

-

NULL

S CHEMA_NAME

Database

DEFAULT_CHARACTER_S ET_NAME

DEFAULT_COLLAT ION_NAME

S QL_PATH

NULL

注释 S QL_PATH列的之总为NULL。

下述语句是等效的

SELECT SCHEMA_NAME AS `DatabaseFROM

INFORMATION_SCHEMA.SCHEMATA[WHERE SCHEMA_NAME LIKE 'wi ld' ]SHOW DATABASES[LI KE 'wi ld' ]23. 1.2. INFORMATI ON_SCHEMA TABLES表

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

标准名称

SHOW名称

注释

TABLE_CATALOG

NULL

TABLE_S CHEMA

Tab l e_. . .

TABLE_NAME

Tab l e_. . .

TABLE_TYPE

E NG I NE

Engine

MySQL扩展

VERS I ON

Version

MySQL扩展

R OW_FORMAT

Row_fo rmat

MySQL扩展

TABL E_R OWS

Rows

MySQL扩展

AVG_ROW_LENGTH

Avg_row_lengthMySQL扩展

DATA_LENGTH

Data_length

MySQL扩展

MAX_DAT A_L ENG TH

Max_data_lengthMySQL扩展

I NDE X_L ENG TH

Index_length

MySQL扩展

DATA_FREE

Data_free

MySQL扩展

A UTO_I N CRE MENT

Auto_increment

MySQL扩展

C REATE_T I ME

Create_time

MySQL扩展

UPDATE_T IME

Update_time

MySQL扩展

CHECK_T IME

Check_time

MySQL扩展

TABLE_C OLLAT I ON

Collation

MySQL扩展

CHEC KSUM

Checksum

MySQL扩展

C REATE_OPT I ONS

Create_optionsMySQL扩展

T ABL E_C OMM ENT

Comment

MySQL扩展

注释