MySQL Administrator's Bible- P15

MySQL Administrator’s Bible- P15: Welcome to the MySQL Administrator’s Bible. Like all books in the Bible series, youcan expect to find both hands-on tutorials and real-world practical application information,as well as reference and background information that provide a context forwhat you are learning.
Nội dung trích xuất từ tài liệu:
MySQL Administrator’s Bible- P15 The MySQL Data DictionaryM ySQL stores information about the data in the databases; this is called metadata. Much of this information is stored in the IN THIS CHAPTER INFORMATION_SCHEMA database, following the SQL 2003standard. Examining the Object catalogTables in the INFORMATION_SCHEMA database are read-only, in-memory, System informationand show data from various sources. Managing permissionsSHOW CREATE TABLE will show the tables as TEMPORARY, because Developing custom metadatathey reside in memory and do not persist between mysqld restarts.INFORMATION_SCHEMA tables are called system views and they may be ofdifferent storage engine types. At the time of this writing all the systemviews are either the MEMORY, MyISAM, or Maria storage engine.Regular SQL statements can be used to query them, though they havesome special properties that other views do not have: ■ mysqldump will not export any information (data, schema) from INFORMATION_SCHEMA system views ■ There is no data directory for the INFORMATION_SCHEMA database ■ There is no .frm file associated with the INFORMATION_SCHEMA views. The definitions are hard-coded into the database.The table definitions for the data dictionary are hard-coded into the sourcecode, and loaded when mysqld starts. Unlike other databases, there isno directory in the datadir for the INFORMATION_SCHEMA database.All users have permission to see the INFORMATION_SCHEMA database;however, they can only see the objects they have permission to see. Forexample, table details in the TABLES system view are limited to the tablesthat the user has permission to see. 667Part IV Extending Your Skills Some of the metadata provided by the INFORMATION_SCHEMA database is also provided by various SHOW commands (see Chapter 4). The INFORMATION_SCHEMA database is a more complete data dictionary than using SHOW commands. Also, standard SQL statements can be used to query the system views to retrieve metadata. The INFORMATION_SCHEMA database contains more than 40 system views. They can be infor- mally categorized as: ■ Object catalog (databases, tables, columns, and so on) ■ System information (variables, statistics, available options) ■ Permissions ■ Storage engine-specific metadata Querying metadata is a powerful tool for a database administrator, answering simple questions such as ‘‘how many tables of each storage engine type exist?’’ and ‘‘which tables have columns using the DECIMAL type?’’ and ‘‘how many foreign key constraints exist?’’ and even ‘‘how much space does a certain group of three tables use?’’ Querying metadata provides a way to retrieve information about the system that can be used to track and tune performance. Any tool that per- forms queries can retrieve metadata by querying the INFORMATION_SCHEMA database, exactly the same way it queries any other database. Object Catalog The INFORMATION_SCHEMA database contains system views with metadata about objects such as databases, tables, views, columns, indexes, partitions, stored routines, triggers, and events. SCHEMATA ‘‘Schema’’ is another name for a database, and ‘‘schemata’’ is the plural of schema. The SCHEMATA system view in the INFORMATION_SCHEMA database provides information about all the databases, including the mysql system database and the INFORMATION_SCHEMA database itself. The fields in the SCHEMATA system view are: ■ CATALOG_NAME — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ SCHEMA_NAME — The name of the database, such as sakila. ■ DEFAULT_CHARACTER_SET_NAME — The default character set of the database. If no default character set is assigned by a CREATE DATABASE or ALTER DATABASE command, the default character set for the system is stored. Thus, the DEFAULT_CHARACTER_SET_ NAME field always has a non-NULL value, and defaults to the character set of the system at the time of database creation. 668 The MySQL Data Dictionary 21 ■ DEFAULT_COLLATION_NAME — The default collation of the database. If no default collation is assigne ...