20.1. Overview of Metadata Access Methods

MySQL produces metadata for several aspects of database structure. To name a few, you can obtain names of databases and tables, information about columns and indexes in tables, or stored routine definitions.

One method by which MySQL makes metadata available is through a family of SHOW statements, each of which displays one kind of information. For example, SHOW DATABASES and SHOW TABLES return lists of database and table names, and SHOW COLUMNS produces information about definitions of columns in a table.

A client program, mysqlshow, acts as a command-line front end to a few of the SHOW statements. When invoked, it examines its arguments to determine what information to display, issues the appropriate SHOW statement, and displays the results that the statement returns.

SHOW and mysqlshow have been available since very early releases of MySQL. As of MySQL 5, metadata access is enhanced through two additions:

• The INFORMATION_SCHEMA database is implemented. This provides better compliance with standard SQL because INFORMATION_SCHEMA is standard, not a MySQL-specific extension like SHOW.

• SHOW statement syntax is extended to support a WHERE clause for describing which rows to display. (Some SHOW statements support a LIKE clause for applying a pattern match to the rows of the result, but WHERE is more flexible.)

The following sections provide more detail about each of the metadata access methods. Chapter 31, "The INFORMATION_SCHEMA Database," provides a comparative breakdown of the advantages and disadvantages of INFORMATION_SCHEMA versus SHOW.