MYSQL: bringing a joined result with column names carrying the tables aliases

I run

select * from tableA as AA join tableB as BB;

There are ma开发者_如何转开发ny identical columns in the tables so I want all the columns be prefixed on output by the table alias. Like: AA.id, ..., BB.id, ...

How can I do this?

edit: I know that I can name columns manually, but I'd like all of them be named automatically after the table aliases.

Thanks

---

select * is "lazy". You should always specify which fields you want, and if there's conflicts between the two tables, you do have to explicitly specify which of the conflicting fields you want, and from which table.

Yes, it's tedious, especially if you DO want all the fields, but that's life...

---

create column name alias as well. Of course, You have to mention every column name which you want in output instead of * there is no automate version of this.

select AA.colname as AA_colname, BB.colname as BB_colname 
from tableA as AA join tableB as BB;

---

You can use:

SELECT AA.*, BB.* 
  FROM tableA as AA 
  JOIN tableB as BB

Ignoring the cartesian product this will produce on MySQL, it won't alleviate the fact that you will still have difficulty retrieving values from identically named columns in either table. SELECT * is also bad practice, risking data retrieval that you don't need.

You will have to define column aliases for identically named columns, or alter the tables to change the column names.

---

The easiest way to dynamically name columns is to generate a prepared statement that references the information_schema. This would give you the results you were looking for.

SET @sql = NULL;
SELECT CONCAT('SELECT ',GROUP_CONCAT(c.TABLE_NAME,'.',c.COLUMN_NAME,
  ' ''',t.TABLE_COMMENT,'.',c.COLUMN_NAME,''''),' FROM tableA JOIN tableB;')
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME IN ('tableA','tableB');
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;

It's simpler without the AA, BB aliases. The method above requires that you set these in the table comment field for each table, found in INFORMATION_SCHEMA.TABLES.TABLE_COMMENT. Update information_schema.tables set table_comment = 'AA' etc...

The GROUP_CONCAT() function has a default limit of 1024 characters, so depending on the number of columns in your tables, you may need to raise this limit in order to generate the prepared statement.

SET SESSION group_concat_max_len = 1000000;

This command will raise the group concat limit if needed.