36.笔记 MySQL学习——借助INFORMATION_SCHEMA获取元数据

36.笔记 MySQL学习——借助INFORMATION_SCHEMA获取元数据

通过SELECT语句可以访问INFORMATION_SCHEMA库。SHOW语句的输出里，列的个数是固定的，无法把输出内容捕获到表里去。

           利用INFORMATION_SCHEMA可以选取特定的输出列。

           INFORMATION_SCHEMA可以理解成一个虚拟的库，表是各种不同数据库元数据的视图。

查看如下：

mysql> show tables in information_schema;

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

| 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_FT_CONFIG                      |

| INNODB_SYS_VIRTUAL                    |

| INNODB_CMP                            |

| INNODB_FT_BEING_DELETED               |

| INNODB_CMP_RESET                      |

| INNODB_CMP_PER_INDEX                  |

| INNODB_CMPMEM_RESET                   |

| INNODB_FT_DELETED                     |

| INNODB_BUFFER_PAGE_LRU                |

| INNODB_LOCK_WAITS                     |

| INNODB_TEMP_TABLE_INFO                |

| INNODB_SYS_INDEXES                    |

| INNODB_SYS_TABLES                     |

| INNODB_SYS_FIELDS                     |

| INNODB_CMP_PER_INDEX_RESET            |

| INNODB_BUFFER_PAGE                    |

| INNODB_FT_DEFAULT_STOPWORD            |

| INNODB_FT_INDEX_TABLE                 |

| INNODB_FT_INDEX_CACHE                 |

| INNODB_SYS_TABLESPACES                |

| INNODB_METRICS                        |

| INNODB_SYS_FOREIGN_COLS               |

| INNODB_CMPMEM                         |

| INNODB_BUFFER_POOL_STATS              |

| INNODB_SYS_COLUMNS                    |

| INNODB_SYS_FOREIGN                    |

| INNODB_SYS_TABLESTATS                 |

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

61 rows in set (0.00 sec)

查看表里包含哪些列，如下：

mysql> desc information_schema.character_sets;

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

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

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

| CHARACTER_SET_NAME   | varchar(32) | NO   |    |         |       |

| DEFAULT_COLLATE_NAME | varchar(32) |NO   |    |         |       |

| DESCRIPTION          | varchar(60) | NO   |    |         |       |

| MAXLEN               | bigint(3)   | NO  |     | 0       |      |

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

4 rows in set (0.00 sec)

mysql> select count(*) frominformation_schema.tables where table_schema = 'sampdb' and table_name ='member';

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

| count(*) |

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

|       1 |

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

1 row in set (0.00 sec)

查看表使用的存储引擎是哪一种

mysql> select engine frominformation_schema.tables where table_schema = 'sampdb' and table_name ='student';

+--------+

| engine |

+--------+

| InnoDB |

+--------+

1 row in set (0.00 sec)