9.1 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
values�that 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:
Information about the result of queries. When you delete or update a set of rows, you can determine the number
of rows that were changed. For a SELECT query, 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 items as the current user and the
current database.
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.
|
No comments:
Post a Comment