计算子句中的列-性能 [英] Calculated column in where-clause - performance

问题描述

因为您不能在MySQL的where子句中使用计算列:

Since you can't use a calculated column in a where-clause in MySQL like this:

SELECT a,b,c,(a*b+c) AS d FROM table
WHERE d > n
ORDER by d

您必须使用

SELECT a,b,c,(a*b+c) AS d FROM table
WHERE (a*b+c) > n
ORDER by d

计算(在该示例中，(a * b + c)"是每行执行一次还是两次执行?有没有一种方法可以使速度更快?我觉得很奇怪，可以对列进行ORDER，但不必一条子句.

Is the calculation (in that example "(a*b+c)" executed once per row or twice? Is there a way to make it faster? I find it strange it's possible to ORDER on the column but not to have a WHERE-clause.

推荐答案

您可以使用HAVING过滤计算所得的列:

You can use HAVING to filter on a computed column:

SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d

请注意，您需要在SELECT子句中包含n才能使它起作用.

Note that you need to include n in the SELECT clause for this to work.

这篇关于计算子句中的列-性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！