Avoiding Output Column Order Problems When Writing Programs
Problem
You’re issuing a
SELECT
*
query from within a
program, and the columns don’t come back in the
order you expect.
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.
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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.