mysql数据库提供者_MySQL Study之--MySQL schema_information数据库

MySQL Study之--MySQL schema_information数据库

information_schema数据库是在mysql的版本5.0之后产生的，一个虚拟数据库，物理上并不存在。

information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限(更加细化的访问方式)。

案例：

mysql> show databases;+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| prod               |

| test               |

+--------------------+

5 rows in set (0.00 sec)

访问information_schema：mysql> use information_schema;Database changed

mysql> show tables;+---------------------------------------+

| Tables_in_information_schema          |

+---------------------------------------+

| CHARACTER_SETS                        |

| COLLATIONS                            |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS                               |

| COLUMN_PRIVILEGES                     |

| ENGINES                               |

| EVENTS                                |

| FILES                                 |

| GLOBAL_STATUS                         |

| GLOBAL_VARIABLES                      |

| KEY_COLUMN_USAGE                      |

| OPTIMIZER_TRACE                       |

| PARAMETERS                            |

| PARTITIONS                            |

| PLUGINS                               |

| PROCESSLIST                           |

| PROFILING                             |

| REFERENTIAL_CONSTRAINTS               |

| ROUTINES                              |

| SCHEMATA                              |

| SCHEMA_PRIVILEGES                     |

| SESSION_STATUS                        |

| SESSION_VARIABLES                     |

| STATISTICS                            |

| TABLES                                |

| TABLESPACES                           |

| TABLE_CONSTRAINTS                     |

| TABLE_PRIVILEGES                      |

| TRIGGERS                              |

| USER_PRIVILEGES                       |

| VIEWS                                 |

| INNODB_LOCKS                          |

| INNODB_TRX                            |

| INNODB_SYS_DATAFILES                  |

| INNODB_LOCK_WAITS                     |

| INNODB_SYS_TABLESTATS                 |

| INNODB_CMP                            |

| INNODB_METRICS                        |

| INNODB_CMP_RESET                      |

| INNODB_CMP_PER_INDEX                  |

| INNODB_CMPMEM_RESET                   |

| INNODB_FT_DELETED                     |

| INNODB_BUFFER_PAGE_LRU                |

| INNODB_SYS_FOREIGN                    |

| INNODB_SYS_COLUMNS                    |

| INNODB_SYS_INDEXES                    |

| INNODB_FT_DEFAULT_STOPWORD            |

| INNODB_SYS_FIELDS                     |

| INNODB_CMP_PER_INDEX_RESET            |

| INNODB_BUFFER_PAGE                    |

| INNODB_CMPMEM                         |

| INNODB_FT_INDEX_TABLE                 |

| INNODB_FT_BEING_DELETED               |

| INNODB_SYS_TABLESPACES                |

| INNODB_FT_INDEX_CACHE                 |

| INNODB_SYS_FOREIGN_COLS               |

| INNODB_SYS_TABLES                     |

| INNODB_BUFFER_POOL_STATS              |

| INNODB_FT_CONFIG                      |

+---------------------------------------+

59 rows in set (0.00 sec)

mysql> desc SCHEMATA;+----------------------------+--------------+------+-----+---------+-------+

| Field                      | Type         | Null | Key | Default | Extra |

+----------------------------+--------------+------+-----+---------+-------+

| CATALOG_NAME               | varchar(512) | NO   |     |         |       |

| SCHEMA_NAME                | varchar(64)  | NO   |     |         |       |

| DEFAULT_CHARACTER_SET_NAME | varchar(32)  | NO   |     |         |       |

| DEFAULT_COLLATION_NAME     | varchar(32)  | NO   |     |         |       |

| SQL_PATH                   | varchar(512) | YES  |     | NULL    |       |

+----------------------------+--------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME  from SCHEMATA;+--------------------+----------------------------+

| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME |

+--------------------+----------------------------+

| information_schema | utf8                       |

| mysql              | latin1                     |

| performance_schema | utf8                       |

| prod               | latin1                     |

| test               | latin1                     |

+--------------------+----------------------------+

5 rows in set (0.00 sec)

mysql> desc tables;+-----------------+---------------------+------+-----+---------+-------+

| Field           | Type                | Null | Key | Default | Extra |

+-----------------+---------------------+------+-----+---------+-------+

| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |

| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |

| TABLE_NAME      | varchar(64)         | NO   |     |         |       |

| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |

| ENGINE          | varchar(64)         | YES  |     | NULL    |       |

| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |

| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |

| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |

| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |

| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |

| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |

| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |

| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |

| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |

| CREATE_TIME     | datetime            | YES  |     | NULL    |       |

| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |

| CHECK_TIME      | datetime            | YES  |     | NULL    |       |

| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |

| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |

| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |

| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |

+-----------------+---------------------+------+-----+---------+-------+

21 rows in set (0.00 sec)

mysql> select * from prod.t1;+------+-------+

| id   | name  |

+------+-------+

|   10 | tom   |

|   20 | jerry |

|   30 | rose  |

|   40 | ellen |

+------+-------+

4 rows in set (0.00 sec)

mysql> explain select * from prod.t1;+----+-------------+-------+------+---------------+------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

其中重要的表有：

SCHEMATA表：

SCHEMATA表提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表：

TABLES表提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间，等等信息。是show tables from schemaname的结果取之此表。

COLUMNS表：

COLUMNS表提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

STATISTICS表：

STATISTICS表提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES表：

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

SCHEMA_PRIVILEGES表：

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

TABLE_PRIVILEGES表：

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

COLUMN_PRIVILEGES表；

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

CHARACTER_SETS表：

CHARACTER_SETS(字符集)表提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表:

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

COLLATION_CHARACTER_SET_APPLICABILITY表：

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

TABLE_CONSTRAINTS表:

TABLE_CONSTRAINTS表描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：

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

ROUTINES表：

ROUTINES表提供了关于存储子程序(存储程序和函数)的信息。此时，ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：

VIEWS表给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

TRIGGERS表；

TRIGGERS表提供了关于触发程序的信息。必须有super权限才能查看该表。

另外，在未来的版本中会有新的表添加。

【注】information_schema是一个由数据库的元数据组成的数据库。里面存储的是mysql的数据库基本信息。并随时改变。用于查看信息以及系统决策时作为重要的信息提供者。

原文：http://tiany.blog.51cto.com/513694/1677634