在MySQL中按别名选择列 [英] Select column by alias in MySQL

问题描述

我想了解MySQL中的某些特定行为.运行"select @@ version"，我看到我的版本是5.6.34-log.

I would like to understand certain specific behavior in MySQL. Running "select @@version", I see my version is 5.6.34-log.

为了便于重现，让我使用生成的表放置样本:

Let me put the sample, using a generated table in order to make it easier to reproduce:

SELECT 
    CONCAT(a, b) AS 'c1', CONCAT((SELECT c1), 2)
FROM
    (SELECT 'a', 'b', 'c' UNION ALL SELECT 1, 2, 3) t1;

正如标题所建议的那样，我最初搜索的内容是如何通过别名选择列，以便重用计算所得的字段，从而避免了冗长的查询.如 answer 中学到了这种方法，但不太了解-实际上，我什至不知道如何称这种操作/条款.

What I was searching for initially was, as suggested by the title, how to select a column by its alias, in order to reuse calculated fields, avoiding long queries. Most of the answers either suggest using subqueries or variables - one has poor readability, and the other is not assured by the very own DB devs, as stated in documentation. Then, I learned this method from this answer, and can't quite understand it - indeed, I don't even know how to call this kind of operation/clause.

至少在此MySQL版本中，它似乎工作得很好.唯一的例外是涉及包含聚合函数的列(如下所示)-它会引发1247(错误引用)错误，并且感觉很合理.

It seems to work very well, at least in this MySQL version. The only exception is when it comes to columns which contains aggregate functions (shown below) - it throws a 1247 (misreference) error, and that feels quite reasonable.

-- THIS DOESN'T WORK!
SELECT 
    CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2) as c2
FROM
    (SELECT 'a' as a, 'b' as b, 'c' as c UNION ALL SELECT '1', 2, 3) t1;

我已经阅读了有关该主题的许多答案，但这是对这种操作的唯一参考，并且由于我不知道其名称，因此无法对其进行更深入的研究.有谁知道该结构的名称，我如何更好地理解它?

I've read many answers around this theme, but that is the only reference to this kind of operation, and, since I don't know its name, I can't look deeper into it. Does anyone know how is this structure called, how can I understand it better?

我不尝试执行无法正常运行的查询中所示的操作.确实，我正在尝试了解MySQL的行为.已经存在的问题很容易理解如何使用子查询来执行此操作，依此类推-并不是重点.我的主要问题是了解MySQL在那里执行哪种操作以及如何调用它，因为我从没读过类似的东西(这会是带有自动选择的查询吗?)

I'm not trying to perform that operation shown in the not working query. Indeed, I'm trying to understand MySQL behavior. The already existing questions are enoug to understand how to do that using a subquery, and so on - that is not the point. My main issue is to understand what kind of operation is MySQL performing there and how is it called, since I've never read anything about something like that (would it be a query with an auto select?)

这篇帖子激发了有关此MySQL行为的更具体，更好的书面问题，可以在

EDIT 2: This post has inspired a more specific and better written question about this MySQL behavior, which can be found here.

推荐答案

简短答案:

1. 对SELECT列表或
中的别名的引用
2. 别名表达式

到目前为止，我发现的唯一文档是: https://bugs.mysql.com/bug.php?id=79549

The only documentation I've found on this so far has been: https://bugs.mysql.com/bug.php?id=79549

在该链接中包含以下内容:

In that link there is the following:

[2015年12月9日15:35] Roy Lyseng ... 这是原始决定的更长背景:

[9 Dec 2015 15:35] Roy Lyseng ... Here is a longer background for the original decision:

与WHERE子句中(以及在GROUP BY中)子查询中对别名的引用相反， 没有理由(标准合规性除外)，我们不允许在SELECT列表 ，因为它们应该在查询执行的同一阶段可用.但是5.6中的支持相当随意:

Contrary to references to aliases in subqueries in the WHERE clause (and in GROUP BY, for that matter), there is no reason (except standard compliance) that we should not allow references to aliases in the SELECT list, since they should be available in the same phase of query execution. But the support in 5.6 was quite arbitrary:

鉴于此:创建表t1(a int，b int)，

Given this: create table t1(a int, b int),

SELECT列表中的别名无效:

Alias in SELECT list is not valid:

  select a+b as c,c+1 from t1;

错误1054(42S22):字段列表"中的未知列"c"

ERROR 1054 (42S22): Unknown column 'c' in 'field list'

但是在子查询中，对c的引用是有效的:

But within a subquery, reference to c is valid:

  select a+b as c,(select c+1) from t1;

并且子查询必须在别名定义之后:

And subquery must be after definition of alias:

  select (select c+1),a+b as c from t1;

错误1247(42S22):不支持引用'c'(项目列表中的正向引用)

ERROR 1247 (42S22): Reference 'c' not supported (forward reference in item list)

因此，可以很容易地说，对 对SELECT列表中的别名的引用 的支持相当特殊.尽管如此，我们将尝试重新实现旧的解决方案，但没有尝试清除此功能支持中的明显漏洞.但是不会重新实现WHERE子句中子查询中的别名.

So, it is easy to say that support for references to aliases in SELECT list was rather ad-hoc. Nevertheless, we will try to reimplement the old solution, but with no attempt at cleaning up the obvious holes in the support for this feature. But referencing aliases in subqueries in the WHERE clause will not be reimplemented.

除了在标准文档中描述此功能的错误报告之外，我还在寻找文档.但到目前为止没有运气.

I'm yet looking for documentation beyond the bug report describing this functionality in the standard documents; but thus far no luck.

这篇关于在MySQL中按别名选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！