MySQL information_schema 数据库与实例概述

从 MySQL 5 开始，当您执行show databases命令时，information_schema 将列在数据库列表中。information_schema是一个虚拟的 MySQL 数据库，存储所有 MySQL 数据库的元数据信息。

通常，您将执行 DESCRIBE 或 SHOW 命令来获取元数据信息。但是，使用 information_schema 视图，您可以执行标准的 select SQL 命令来获取元数据信息。在本文中，让我们了解几个有关如何使用 information_schema 数据库的实际示例。

1.获取information_schema的基本信息

show databases命令将在数据库列表中显示information_schema。information_schema 不是物理数据库。当您对 information_schema 表执行select时，它会从真实数据库和其他系统变量中提取数据。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcrm           |
+--------------------+
4 rows in set (0.00 sec)

以下是 information_schema 数据库中可用的表（视图）。

mysql> use information_schema;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

2.查询显示超过1000行的表

使用上面提到的 information_schema 表，您可以构建自己的查询来获取元数据信息。此示例显示具有超过 1000 行的 MySQL 表。

mysql> select concat(table_schema,'.',table_name) as table_name,table_rows
    -> from information_schema.tables where table_rows > 1000
    -> order by table_rows desc;

+----------------------------------+------------+
| table_name                       | table_rows |
+----------------------------------+------------+
| bugs.series_data                 |      52778 |
| bugs.bugs_activity               |      26436 |
| bugs.longdescs                   |      21473 |
| bugs.email_setting               |       5370 |
| bugs.attachments                 |       4714 |
| bugs.attach_data                 |       4651 |
| bugs.cc                          |       4031 |
| bugs.bugs                        |       2190 |
| bugs.namedqueries_link_in_footer |       1228 |
+----------------------------------+------------+
9 rows in set (0.04 sec)

3.查询列出所有没有主键的表

这个例子给出了所有没有主键的表的列表。

SELECT CONCAT(t.table_name,".",t.table_schema) as table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE';

4.使用information_schema实现表的历史数据

Putting MySQL information_schema to Use文章使用信息模式实现了一个历史数据库。本文的前半部分描述了历史数据库的需求，以及实现它的通用设计。后半部分描述了代码生成器的逐步构建，该代码生成器创建用于构建和加载历史数据库的 SQL。代码生成器由信息模式驱动，并详细讨论了信息模式的一些特征。

5.查询列出数据库中最大的前5张表

此示例给出了数据库中占用空间最大的前 5 个表及其大小（以 MB 为单位）。

mysql> SELECT concat(table_schema,'.',table_name) table_name,
    -> concat(round(data_length/(1024*1024),2),'M') data_length
    -> FROM information_schema.TABLES
    -> ORDER BY data_length DESC LIMIT 5;

+--------------------+-------------+
| table_name         | data_length |
+--------------------+-------------+
| bugs.attach_data   | 706.89M     |
| bugs.longdescs     | 3.45M       |
| bugs.bugs_activity | 1.45M       |
| bugs.series_data   | 0.75M       |
| bugs.attachments   | 0.51M       |
+--------------------+-------------+
5 rows in set (0.05 sec)