MySQL5 new feature (data dictionary) _ MySQL

Source: Internet
Author: User
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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.