Section 9.0. Introduction

9.0. Introduction

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

Information about statement results

For statements that delete or update rows, you can determine how many rows were changed. For a SELECT statement, you can find out the number of columns in the result set, as well as information about each column in the result set, such as the column name and its display width. Such information often is essential for processing the results. For example, if you're formatting a tabular display, you can determine how wide to make each column and whether to justify values to the left or right.

Information about tables and databases

Information pertaining to the structure of tables and databases is useful for applications that need to enumerate a list of tables in a database or databases hosted on a server (for example, to present a display allowing the user to select one of the available choices). You can also use this information to determine whether tables or databases exist. Another use for table metadata is to determine the legal values for ENUM or SET columns.

Information about the MySQL server

Some APIs provide information about the database server or about the status of your current connection with the server. Knowing the server version can be useful for determining whether it supports a given feature, which helps you build adaptive applications. Information about the connection includes such values as the current user and the default database.

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:

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

INFORMATION_SCHEMA table	SHOW statement
SCHEMATA	SHOW DATABASES
TABLES	SHOW TABLES
COLUMNS	SHOW COLUMNS