Recipe 3.3. Giving Better Names to Query Result Columns
ProblemYou don't like the names of the columns in a query result. SolutionUse column aliases to supply names of your own choosing. DiscussionWhen you retrieve a result set, MySQL gives every output column a name. (That's how the mysql program gets the names that you see displayed as the initial row of column headers in result set output.) By default, MySQL assigns the column names specified in the CREATE TABLE or ALTER TABLE statement to output columns, but if these defaults are not suitable, you can use column aliases to specify your own names. This section explains aliases and shows how to use them to assign column names in statements. If you're writing a program that needs to retrieve information about column names (that is, column metadata), see Section 9.2. If an output column in a result set comes directly from a table, MySQL uses the table column name for the output column name. For example, the following statement selects three table columns, the names of which become the corresponding output column names: mysql> SELECT t, srcuser, size FROM mail; +---------------------+---------+---------+ | t | srcuser | size | +---------------------+---------+---------+ | 2006-05-11 10:15:08 | barb | 58274 | | 2006-05-12 12:48:13 | tricia | 194925 | | 2006-05-12 15:02:49 | phil | 1048 | | 2006-05-13 13:59:18 | barb | 271 | ... If you generate a column by evaluating an expression, the expression itself is the column name. This can produce rather long and unwieldy names in result sets, as illustrated by the following statement that uses an expression to reformat the t column of the mail table: mysql> SELECT -> CONCAT(MONTHNAME(t),' ',DAYOFMONTH(t),', ',YEAR(t)), -> srcuser, size FROM mail; +-----------------------------------------------------+---------+---------+ | CONCAT(MONTHNAME(t),' ',DAYOFMONTH(t),', ',YEAR(t)) | srcuser | size | +-----------------------------------------------------+---------+---------+ | May 11, 2006 | barb | 58274 | | May 12, 2006 | tricia | 194925 | | May 12, 2006 | phil | 1048 | | May 13, 2006 | barb | 271 | ... The query in the preceding example is specifically contrived to illustrate how awful-looking column names can be. The reason it's contrived is that you probably wouldn't really write the query that way; the same result can be produced more easily using the DATE_FORMAT( ) function. But even if you use DATE_FORMAT( ), the column header is still ugly: mysql> SELECT -> DATE_FORMAT(t,'%M %e, %Y'), -> srcuser, size FROM mail; +----------------------------+---------+---------+ | DATE_FORMAT(t,'%M %e, %Y') | srcuser | size | +----------------------------+---------+---------+ | May 11, 2006 | barb | 58274 | | May 12, 2006 | tricia | 194925 | | May 12, 2006 | phil | 1048 | | May 13, 2006 | barb | 271 | ... To give an output column a name of your own choosing, use an AS name clause to specify a column alias (the keyword AS is optional). The following statement retrieves the same result as the previous one, but renames the first column to date_sent: mysql> SELECT -> DATE_FORMAT(t,'%M %e, %Y') AS date_sent, -> srcuser, size FROM mail; +--------------+---------+---------+ | date_sent | srcuser | size | +--------------+---------+---------+ | May 11, 2006 | barb | 58274 | | May 12, 2006 | tricia | 194925 | | May 12, 2006 | phil | 1048 | | May 13, 2006 | barb | 271 | ... The alias makes the column name more concise, easier to read, and more meaningful. If you want to use a descriptive phrase, an alias can consist of several words. Aliases can be fairly arbitrary, although they are subject to a few restrictions; for example, they must be quoted if they are SQL keywords, contain spaces or other special characters, or are entirely numeric. 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', -> srcuser AS 'Message sender', size AS 'Number of bytes' FROM mail; +-----------------+----------------+-----------------+ | Date of message | Message sender | Number of bytes | +-----------------+----------------+-----------------+ | May 11, 2006 | barb | 58274 | | May 12, 2006 | tricia | 194925 | | May 12, 2006 | phil | 1048 | | May 13, 2006 | barb | 271 | ... You can apply an alias to any output column, not just those that come from tables: mysql> SELECT '1+1+1' AS 'The expression', 1+1+1 AS 'The result'; +----------------+------------+ | The expression | The result | +----------------+------------+ | 1+1+1 | 3 | +----------------+------------+ Here, the value of the first column is '1+1+1' (quoted so that it is treated as a string), and the value of the second column is 1+1+1 (without quotes so that MySQL treats it as an expression and evaluates it). The aliases are descriptive phrases that help to clarify the relationship between the two column values. If you use a single-word alias, and MySQL complains about it, 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' at line 1 mysql> SELECT 1 AS 'INTEGER'; +---------+ | INTEGER | +---------+ | 1 | +---------+ |
