Mysql auto-add prefixes to fields

Question

Example table1:

id | field1 | field2
--------------------

table2:

id | id1 | field1 | field2
--------------------------

Is it possible to do this:

select t1.*, t2.* from table1 t1 inner join table2 t2 on t1.id = t2.id1

and receive this result:

t1_id | t1_fiedl1 | t1_field2 | t2_id | t2_id1 | t2_fiedl1 | t2_field2
----------------------------------------------------------------------

The goal is to make mysql auto add prefixes to resulting fields, to avoid long typings, such as

select t1.id as t1_id, t1.field1 as t1_field1

and so on

Answer 1

The SQL engine will not rewrite your query to "auto-alias" fields for you — aliases must be explicit.

You have two options in client code, however.

First, you could obviously write an abstraction that pieces together the query and provides aliases while doing so.

Second, and easier, is using the information exposed in the underlying MYSQL_FIELD structures associated with each result set. These contain the field and table name (and other information) about each field, allowing you to stitch together t1_field1 programmatically and without knowing field names in advance. How this info is exposed depends on your particular client API.

Answer 2

The goal is to make mysql auto add prefixes to resulting fields, to avoid long typings, such as

select t1.id as t1_id, t1.field1 as t1_field1 and so on

If the field names are same on both tables, you will not get columns from both tables.
You have to write column names with specific alias to achieve that.

For e.g.

SELECT t1.id t1_id, t1.field1 t1_fiedl1, t1.field2 t1_field2 
      ,t2.id t2_id, t2.id1 t2_id1 ,t2.field1 t2_fiedl1, t2.field2 t2_field2
FROM   table1 t1 
INNER  JOIN table2 t2 
         ON t1.id = t2.id1

See this SQLFiddle Demo

If Fields name on both tables are different then you can do that to achieve the desired result.

For e.g.

SELECT     t1.*, t2.*
FROM       table1 t1 
INNER JOIN table2 t2 
        ON t1.id = t2.id1;

See this SQLFiddle