Database Reference
In-Depth Information
| May 12, 2014 | barb@saturn | 271 |
…
The aliases make the column names more concise, easier to read, and more meaningful.
Aliases are subject to a few restrictions. For example, they must be quoted if they are
SQL keywords, entirely numeric, or contain spaces or other special characters (an alias
can consist of several words if you want to use a descriptive phrase). The following
statement retrieves the same data values as the preceding one but uses phrases to name
the output columns:
mysql>
SELECT
->
DATE_FORMAT(t,'%M %e, %Y') AS 'Date of message',
->
CONCAT(srcuser,'@',srchost) AS 'Message sender',
->
size AS 'Number of bytes' FROM mail;
+-----------------+----------------+-----------------+
| Date of message | Message sender | Number of bytes |
+-----------------+----------------+-----------------+
| May 11, 2014 | barb@saturn | 58274 |
| May 12, 2014 | tricia@mars | 194925 |
| May 12, 2014 | phil@mars | 1048 |
| May 12, 2014 | barb@saturn | 271 |
…
If MySQL complains about a single-word alias, the word probably is reserved. Quoting
the alias should make it legal:
mysql>
SELECT 1 AS INTEGER;
You have an error in your SQL syntax near 'INTEGER'
mysql>
SELECT 1 AS 'INTEGER';
+---------+
| INTEGER |
+---------+
| 1 |
+---------+
Column aliases also are useful for programming purposes. If you write a program that
fetches rows into an array and accesses them by numeric column indexes, the presence
or absence of column aliases makes no difference because aliases don't change the po‐
sitions of columns within the result set. However, aliases make a big difference if you
access output columns by name because aliases change those names. Exploit this fact
to give your program easier names to work with. For example, if your query displays
reformatted message time values from the
mail
table using the expression
DATE_FOR
MAT(t,'%M %e, %Y')
, that expression is also the name you must use when referring to
the output column. In a Perl hashref, for example, you'd access it as
$ref-
>{"DATE_FORMAT(t,'%M %e, %Y')"}
. That's inconvenient. Use
AS
date_sent
to give the
column an alias and you can refer to it more easily as
$ref->{date_sent}
. Here's an
example that shows how a Perl DBI script might process such values. It retrieves rows
into a hash and refers to column values by name:
