Introduction

Most of the queries 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 query 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:

Some APIs try to provide a database-independent interface for types of metadata that tend to be available across a variety of database engines (such as the names of the columns in a result set). But in general, metadata information is closely tied to the structure of the database system, so it tends to be somewhat database-dependent. This means that if you port an application that uses recipes in this chapter to other databases, it may need some modification. 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 DBI, DB-API, or JDBC that gives you a database-independent way of issuing queries, the SQL itself is database-specific and will need to be changed to work with other engines.

The scripts containing the code for the examples shown here are 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, look in the tables directory.

In several cases, recipes developed here construct queries using a database, table, or column name that is stored in a variable. For simplicity, generally such names are inserted as is into the query string. For example:

$query = "SHOW COLUMNS FROM $tbl_name";

This works properly in the majority of cases, but there are some possible complications you should know about, and may wish to take into account when adapting these recipes for your own use. As of MySQL 3.23.6, names are allowed to contain almost any character, such as spaces. If you anticipate a need to deal with such names, surround the name with backticks:

$query = "SHOW COLUMNS FROM `$tbl_name`";

If the server is running in ANSI mode, name quoting should be done with double quotes instead:

$query = "SHOW COLUMNS FROM "$tbl_name"";

To deal with these issues on a general basis, you can query the server to see if it is Version 3.23.6 or later (see Recipe 9.14), and you can also use SHOW VARIABLES to see if it is running in ANSI mode. The recipes here do not perform all these checks, because doing so would obscure their main point.

Категории