information_schema mysql

从MySQL 5开始, 你可以看到多了一个系统数据库information_schema . information_schema 存贮了其他所有数据库的信息。让我们来看看几个使用这个数据库的例子：

< !--more-->

1. 取得关于 information_schema的基本信息

information_schema是一个虚拟数据库，并不物理存在，在select的时候，从其他数据库获取相应的信息。

Java代码 

1. mysql> show databases; 
2. +--------------------+ 
3. | Database           | 
4. +--------------------+ 
5. | information_schema | 
6. | bugs               | 
7. | mysql              | 
8. | sugarcrm           | 
9. +--------------------+ 
10. 4 rows in set (0.00 sec) 

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

以下是information_schema数据库中的表.

Java代码 

1. mysql> use information_schema; 
2. mysql> show tables; 
3. +---------------------------------------+ 
4. | Tables_in_information_schema          | 
5. +---------------------------------------+ 
6. | CHARACTER_SETS                        | 
7. | COLLATIONS                            | 
8. | COLLATION_CHARACTER_SET_APPLICABILITY | 
9. | COLUMNS                               | 
10. | COLUMN_PRIVILEGES                     | 
11. | KEY_COLUMN_USAGE                      | 
12. | PROFILING                             | 
13. | ROUTINES                              | 
14. | SCHEMATA                              | 
15. | SCHEMA_PRIVILEGES                     | 
16. | STATISTICS                            | 
17. | TABLES                                | 
18. | TABLE_CONSTRAINTS                     | 
19. | TABLE_PRIVILEGES                      | 
20. | TRIGGERS                              | 
21. | USER_PRIVILEGES                       | 
22. | VIEWS                                 | 
23. +---------------------------------------+ 
24. 17 rows in set (0.00 sec) 

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行的表

Java代码 

1. 以下的语句可以查出超过1000行数据的表 
2.  
3. mysql> select concat(table_schema,'.',table_name) as table_name,table_rows 
4.     -> from information_schema.tables where table_rows > 1000 
5.     -> order by table_rows desc; 
6.  
7. +----------------------------------+------------+ 
8. | table_name                       | table_rows | 
9. +----------------------------------+------------+ 
10. | bugs.series_data                 |      52778 | 
11. | bugs.bugs_activity               |      26436 | 
12. | bugs.longdescs                   |      21473 | 
13. | bugs.email_setting               |       5370 | 
14. | bugs.attachments                 |       4714 | 
15. | bugs.attach_data                 |       4651 | 
16. | bugs.cc                          |       4031 | 
17. | bugs.bugs                        |       2190 | 
18. | bugs.namedqueries_link_in_footer |       1228 | 
19. +----------------------------------+------------+ 
20. 9 rows in set (0.04 sec) 

以下的语句可以查出超过1000行数据的表

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. 查询所有没有主键的表

Java代码 

1. This example gives a list of all the tables without primary key. 
2.  
3. SELECT CONCAT(t.table_name,".",t.table_schema) as table_name 
4. FROM information_schema.TABLES t 
5. LEFT JOIN information_schema.TABLE_CONSTRAINTS tc 
6. ON t.table_schema = tc.table_schema 
7. AND t.table_name = tc.table_name 
8. AND tc.constraint_type = 'PRIMARY KEY' 
9. WHERE tc.constraint_name IS NULL 
10. AND t.table_type = 'BASE TABLE'; 

This example gives a list of all the tables without primary key.

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 the MySQL information_schema to Use article implements a history database using the information schema. The first half of this article describes the requirements for the history database, and a generic design to implement it. The second half describes the stepwise construction of code-generator that creates the SQL to construct and load the history database. The code-generator is driven by the information schema and some features of the information schema are discussed in detail.

5. 查询5个最大表

Java代码 

1. mysql> SELECT concat(table_schema,'.',table_name) table_name, 
2.     -> concat(round(data_length/(1024*1024),2),'M') data_length 
3.     -> FROM information_schema.TABLES 
4.     -> ORDER BY data_length DESC LIMIT 5; 
5.  
6. +--------------------+-------------+ 
7. | table_name         | data_length | 
8. +--------------------+-------------+ 
9. | bugs.attach_data   | 706.89M     | 
10. | bugs.longdescs     | 3.45M       | 
11. | bugs.bugs_activity | 1.45M       | 
12. | bugs.series_data   | 0.75M       | 
13. | bugs.attachments   | 0.51M       | 
14. +--------------------+-------------+ 
15. 5 rows in set (0.05 sec)