O'Reilly logo

Expert PHP and MySQL® by Patrick Galbraith, Ronald Bradford, Andrew Curioso

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required