问题描述

我有很多表使用 id 作为主键.当我将几个表连接在一起并 select * 时，只显示一个 id 列.使用 MySql 时，我可以解决此问题的唯一方法是使用 SELECT AS，例如 SELECT id AS groupId.

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.

MariaDB 仍然是这种情况吗?我的重复列名不仅限于 id 字段，因此我最终得到了非常冗长的 select 语句.

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.

我希望 MariaDB 可能会引入一个查询功能，该功能允许列名以其表名开头.例如，查询可能会返回列名称 group.id 和 person.id.

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.

根据 Shadow 的评论，问题不在于 MariaDB(或 MySql).结果集确实包含多个id"字段.问题出在客户端库上，在我的例子中是 PDO.

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.

推荐答案

如果结果集中有多个同名的列，那么即使 mysql 也会返回所有列，但它们在结果集中也会有相同的名称，除非您使用字段别名.创建了一个 sqlfiddle 来证明它这里.您的客户端库可能无法区分这两列.这是一个已知问题，例如 laravel.

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.

mysql 和 mariadb 都允许你在字段名前加上表名，但是表名不会作为字段名的一部分返回.但是，大多数客户端库都能够检索 mysql/mariadb 返回的结果集中列的元数据，其中包括未计算列的表名.参见例如 mysqli_fetch_field_direct() 函数.

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.

您可以使用从元数据中检索到的表名来确定哪个 id 列属于哪个表.

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

这篇关于MariaDB:我可以在不使用“AS"的情况下检索重复的列名吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！