MariaDB: Can I retrive duplicate column names with

问题:

I have a lot of tables that use id as the primary key. When I join several tables together and select *, only one of the id columns is displayed. When using MySql, the only way I could get around this was to use SELECT AS, for example SELECT id AS groupId.

Is this still the case with MariaDB? My duplicate column names are not limited to just the id field, so I end up with really verbose select statements.

I was hoping that MariaDB might introduce a query feature that allowed the column names to be prefaced with their table names. For example, a query might bring back columns names group.id and person.id.

EDIT: Per the comments from Shadow, the problem is not with MariaDB (or MySql for that matter). The result set does indeed include multiple 'id' fields. The problem is with the client library, in my case PDO.

回答1:

If you have multiple columns in a resultset with the same name, then even mysql returns all of them, but they will have the same name in the resultset as well, unless you use a field alias. Created an sqlfiddle to prove it here. Probably, your client library cannot distinguish between the 2 columns. This is a known issue for example with laravel.

Both mysql and mariadb allow you to prefix the field names with table names, but, the table name is not returned as part of the field name. However, most client libraries are able to retrieve metadata for the columns in a resultset returned by mysql / mariadb, which would include the table name for non-calculated columns. See for example mysqli_fetch_field_direct() function.

You can use the table name retrieved from metadata to determine which id column belongs to which table.