MySQL5 new feature (data dictionary) _ MySQL

The new feature of MySQL5 (data dictionary) has become more and more popular because of the use of metadata (metadata is the data about how to database, mySQL5 also designs a dedicated database in the product. the fixed name of this database is information_schema, which emerged as a central data dictionary role, it contains all the object information of the database and other database-related projects (such as security). The Object Information is specified in the MySQL server product. Database administrators and related developers can use the information_schema data dictionary database to obtain metadata related to one or more data libraries on the MySQL server.

For example, if the database administrator wants to know the overview of the storage space in a MySQL database instance, you can perform the following query on the data dictionary:
Mysql> SELECT a. schema_name db_name,

-> IFNULL (ROUND (SUM (B. data_length) + SUM (B. index_length)/1024/1024, 2), 0.00)

-> Total_size_mb,

-> IFNULL (ROUND (SUM (B. data_length) + SUM (B. index_length ))-

-> SUM (B. data_free)/1024/1024, 2), 0.00) data_used_mb,

-> IFNULL (ROUND (SUM (data_free)/1024/1024, 2), 0.00) data_free_mb,

-> IFNULL (ROUND (SUM (B. data_length) + SUM (B. index_length)-SUM (B. data_free ))/

-> (SUM (B. data_length) + SUM (B. index_length) * 100), 2), 0) pct_used,

-> COUNT (table_name) tables

-> FROM information_schema.schemata

-> Left join information_schema.tables B ON a. schema_name = B. table_schema

-> WHERE a. schema_name! = 'Information _ scheme'

-> Group by a. schema_name

-> Order by 1;

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

| Db_name | total_size_mb | data_used_mb | data_free_mb | pct_used | tables |

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

| Gim | 432.67 | 432.67 | 0.00 | 100.00 | 16 |

| Gim2 | 8.64 | 8.64 | 0.00 | 100.00 | 6 |

| Mysql | 0.33 | 0.33 | 0.00 | 99.69 | 18 |

| Test | 0.00 | 0.00 | 0.00 | 0.00 | 0 |

| Tpcc | 126.09 | 126.09 | 0.00 | 100.00 | 9 |

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

Note that in the above example, you should set it to not query the dictionary database itself, and use the where condition statement. (51CTO. COM tutorial)