MySQL Study之--MySQL schema_information資料庫，mysql資料庫schema

MySQL Study之--MySQL schema_information資料庫，mysql資料庫schema

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的資料庫基本資料。並隨時改變。用於查看資訊以及系統決策時作為重要的資訊提供者。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。