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 table_schema,table_name,table_type,engine,table_rows from tables where table_schema='prod';

+--------------+------------+------------+--------+------------+| table_schema | table_name | table_type | engine | table_rows |+--------------+------------+------------+--------+------------+| prod         | t1         | BASE TABLE | InnoDB |          2 || prod         | t2         | BASE TABLE | InnoDB |          2 |+--------------+------------+------------+--------+------------+2 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的数据库基本信息。并随时改变。用于查看信息以及系统决策时作为重要的信息提供者。