Expert PHP and MySQL®

Chapter 13. Understanding the INFORMATION_SCHEMA

WHAT'S IN THIS CHAPTER?

• What tables exist in the INFORMATION_SCHEMA

• How to retrieve information on your schemas and tables

• How to list core MySQL metadata used for database objects

• How to retrieve instrumentation on a running MySQL instance

• What extensions to INFORMATION_SCHEMA exist

The ANSI Standard SQL (e.g, SQL:2003 and SQL:2008) defines the support for a level of database metadata using the concept of INFORMATION_SCHEMA. MySQL starting with version 5.0 has implemented the INFORMATION_SCHEMA, often referred to as I_S, to provide a level of SQL access to information previously found in the popular but not SQL standard SHOW commands. The metadata is a form of data dictionary that provides the user access to various information including database objects such as TABLES, COLUMNS, and VIEWS; schema metadata such as CHARACTER_SETS; and internal MySQL operations including GLOBAL_STATUS, GLOBAL_VARIABLES, and PROCESSLIST.

This chapter steps through the INFORMATION_SCHEMA tables from versions 5.0, 5.1, and the 5.4 alpha release. It also gives an example of the INFORMATION_SCHEMA extensions possible using the InnoDB Plugin 1.0.4 and greater, which is available separately and now included in MySQL starting with version 5.1.38.

USING THE INFORMATION_SCHEMA

The SHOW command can be used to retrieve information from the INFORMATION_SCHEMA tables. As you can see, this 5.1 schema contains a number of tables, which are discussed in detail in this chapter: ...