3.3 Avoiding Output Column Order Problems When Writing Programs
3.3.1 Problem
You're issuing a
SELECT * query from within a
program, and the columns don't come back in the
order you expect.
3.3.2 Solution
When you use * to select columns, all bets are
off; you can't assume anything about the order in
which they'll be returned. Either name the columns
explicitly in the order you want, or retrieve them into a data
structure that makes their order irrelevant.
3.3.3 Discussion
The examples in the previous section illustrate the differences
between using * versus a list of names to specify
output columns when issuing SELECT statements from
within the mysql program. The difference between
approaches also may be significant when issuing queries through an
API from within your own programs, depending on how you fetch result
set rows. If you select output columns using *,
the server returns them using the order in which they are listed in
the table definition—an order that may change if the table
structure is modified. If you fetch rows into an array, this
non-determinacy of output column order makes it impossible to know
which column each array element corresponds to. By naming output
columns explicitly, you can fetch rows into an array with confidence
that the columns will appear in the array in the same order that you
named them in the query.
On the other hand, your API may allow you to fetch rows into a
structure containing elements that are accessed by name. (For
example, in Perl you can use a hash; in PHP you can use an
associative array or an object.) If you do this, you can issue a
SELECT * query and then access
structure members by referring to the column names in any order you
want. In this case, there is effectively no difference between
selecting columns with * or by naming them
explicitly: If you can access values by name within your program,
their order within result set rows is irrelevant. This fact makes it
tempting to take the easy way out by using SELECT
* for all your queries, even if
you're not actually going to use every column.
Nevertheless, it's more efficient to name
specifically only the columns you want so that the server
doesn't send you information you're
just going to ignore. (An example that explains in more detail why
you may want to avoid retrieving certain columns is given in Recipe 9.9, in Recipe 9.9.10.")
|
|
|
