9.0. IntroductionMost of the SQL statements used so far have been written to work with the data stored in the database. That is, after all, what the database is designed to hold. But sometimes you need more than just data values. You need information that characterizes or describes those valuesthat is, the statement metadata. Metadata information is used most often in relation to processing result sets, but also is available for other aspects of your interaction with MySQL. This chapter describes how to obtain and use the following types of metadata:
In general, metadata information is closely tied to the implementation of the database system, so it tends to be somewhat database-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 issuing SHOW statements. However, SHOW is a MySQL-specific extension to SQL, so even if you're using an API like Perl or Ruby DBI, PEAR DB, DB-API, or JDBC that gives you a database-independent way of issuing statements, the SQL itself is database-specific and will need to be changed to work with other engines. A more portable source of metadata is the INFORMATION_SCHEMA database, which is available as of MySQL 5.0. This metadata database contains information about databases, tables, columns, character sets, and so forth. INFORMATION_SCHEMA has some advantages over SHOW:
Because of those advantages, recipes in this chapter use INFORMATION_SCHEMA rather than SHOW when possible. 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. And if you haven't yet upgraded to MySQL 5.0 or higher, SHOW is your only choice. The scripts containing the code for the examples in this chapter are found in the metadata directory of the recipes distribution. (Some of them use utility functions located in the lib directory.) To create any tables that you need for trying the examples, use the scripts in the tables directory. As already indicated, recipes developed in this chapter tend to use INFORMATION_SCHEMA rather than SHOW. If you have a version of MySQL older than 5.0, the recipes distribution from the first edition of MySQL Cookbook might be helpful. That distribution used SHOW statements because INFORMATION_SCHEMA did not exist in MySQL then. Briefly, the SHOW statements that provide information similar to the contents of certain INFORMATION_SCHEMA tables are listed in the following table:
|