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)  

 

 

以下是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)  

 

 

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)  

 

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';  

 

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)