MySql SELECT AS - Append all field names

Question

I'd like to do something like this:

SELECT table.id AS prefix_id, table.name AS prefix_name

... and have the prefix added dynamically to all field names rather than selecting them all manually (ie SELECT table.* AS prefix_* or something)

Hopefully I've described that accurately. Any thoughts?

Edit

To be clear, the reason I'm asking is to make sure that my query result contains every column from each table I call even if there are duplicate field names. For example, I might have a table with lots of fields which means I don't want to alias all the fields manually. Further if 3 tables have a field called name my result won't contain three name results; it will have one. I want to avoid ambiguity with my column names.

Answer 1

When you reference the results, you can reference each column by the tablename.column . Therefore you have a default prefix that is appended to each column name.

SELECT table.id,table.name,other.name FROM table,other Where other.name = table.name;

Answer 2

To avoid ambiguity in columns with multiple tables it appears you must manually specify each column name that exists with the same name in more than one table.

http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

Answer 3

I can imagine a situation where this could be useful if you have multiple data models in a list that you need to populate from a single SQL query.

It should be possible to use DESCRIBE or SHOW COLUMNS on those tables before the SELECT query. That way, you know all of the field names for each table. This doesn't help with any potential field name conflicts though.

I am not going to comment on if this is a good idea or not. Each developer must decide where to draw the line between flexibility, laziness and good practice.