MariaDB:是否可以在不使用"AS"的情况下检索重复的列名 [英] MariaDB: Can I retrive duplicate column names without using 'AS'

问题描述

我有很多使用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以在此处进行证明.您的客户端库可能无法区分2列.例如，这是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"的情况下检索重复的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！