Working with Metadata - MySQL - page 336

Metadata is closely tied to the implementation of the database system, so it tends to be
database system−dependent. This means that if an application uses techniques shown
in this chapter, it might need some modification if you port it to other database systems.
For example, lists of tables and databases in MySQL are available by executing SHOW
statements. However, SHOW is a MySQL-specific extension to SQL, so even for APIs like
Perl or Ruby DBI, PDO, DB API, and JDBC that give you a database-independent way
of executing statements, the SQL itself is MySQL-specific and must be changed to work
with other database systems.

A more portable source of metadata is INFORMATION_SCHEMA , a database that contains
information about databases, tables, columns, character sets, and so forth. INFORMA
TION_SCHEMA has some advantages over SHOW :

• Other database systems support INFORMATION_SCHEMA , so applications that use it
are likely to be more portable than those that use SHOW statements.
• INFORMATION_SCHEMA is used with standard SELECT syntax, so it's more similar to
other data-retrieval operations than SHOW statements.

Because of those advantages, recipes in this chapter use INFORMATION_SCHEMA rather
than SHOW in most cases.

A disadvantage of INFORMATION_SCHEMA is that statements to access it are more verbose
than the corresponding SHOW statements. That doesn't matter so much when you're
writing programs, but for interactive use, SHOW statements can be more attractive because
they require less typing. The following table lists SHOW statements that provide infor‐
mation similar to the contents of certain INFORMATION_SCHEMA tables:

INFORMATION_SCHEMA table SHOW statement

SCHEMATA SHOW DATABASES

TABLES SHOW TABLES

COLUMNS SHOW COLUMNS

The results retrieved from INFORMATION_SCHEMA or SHOW depend on
your privileges. You'll see information only for those databases or
tables for which you have some privileges. Thus, an existence test for
an object returns false if it exists but you have no privileges for ac‐
cessing it.

Scripts that create tables used in this chapter are located in the tables directory of the
recipes distribution. Scripts containing code for the examples are located in the meta
data directory. (Some of them use utility functions located in the lib directory.) The
distribution often provides implementations in languages other than those shown.