MySQL如何在参考文档中定义DISTINCT() [英] How does mysql define DISTINCT() in reference documentation

问题描述

这个问题是关于在SELECT修改关键字和函数上找到对MySQL语法的明确引用. /EDIT

This question is about finding definitive reference to MySQL syntax on SELECT modifying keywords and functions. /EDIT

AFAIK SQL定义了DISTINCT关键字的两种用法-SELECT DISTINCT字段...和SELECT COUNT(DISTINCT字段)... 但是，在我管理的一个Web应用程序中，我注意到

AFAIK SQL defines two uses of DISTINCT keywords - SELECT DISTINCT field... and SELECT COUNT(DISTINCT field) ... However in one of web applications that I administer I've noticed performance issues on queries like

SELECT DISTINCT(field1), field2, field3 ...

在单列上的

DISTINCT()毫无意义，我几乎可以肯定将其解释为

DISTINCT() on a single column makes no sense and I am almost sure it is interpreted as

SELECT DISTINCT field1, field2, field3 ...

但是我怎么证明呢?

我已经在mysql网站上搜索了有关此特定语法的参考，但找不到任何参考.是否有人在mysql中具有指向DISTINCT()的定义的链接，或者是否知道有关此方面的其他权威信息?

I've searched mysql site for a reference on this particular syntax, but could not find any. Does anyone have a link to definition of DISTINCT() in mysql or knows about other authoritative source on this?

最佳

编辑 在mysql论坛上问了同样的问题后，我了解到，在解析SQL mysql时，它并不关心函数和列名之间的空格(但我仍然缺少参考).

EDIT After asking the same question on mysql forums I learned that while parsing the SQL mysql does not care about whitespace between functions and column names (but I am still missing a reference).

似乎您可以在函数和括号之间留有空格

As it seems you can have whitespace between functions and the parenthesis

SELECT LEFT (field1,1), field2...

并使mysql将其理解为SELECT LEFT(field，1)

and get mysql to understand it as SELECT LEFT(field,1)

类似地，SELECT DISTINCT(field1)，field2 ...似乎分解为 SELECT DISTINCT(field1)，field2 ...，然后将DISTINCT视为未定义(或未记录)的函数，而是作为SELECT修改关键字和对field1的括号进行评估，就像它们是字段表达式的一部分一样.

Similarly SELECT DISTINCT(field1), field2... seems to get decomposed to SELECT DISTINCT (field1), field2... and then DISTINCT is taken not as some undefined (or undocumented) function, but as SELECT modifying keyword and the parenthesis around field1 are evaluated as if they were part of field expression.

如果有人能指出文档中指出函数和括号之间的空格不重要，或者提供指向适当的MySQL论坛的链接的方式，那将是很棒的选择，在邮件列表中我可能会提出一个疑问以将其放入参考.

It would be great if someone would have a pointer to documentation where it is stated that the whitespace between functions and parenthesis is not significant or to provide links to apropriate MySQL forums, mailing lists where I could raise a question to put this into reference.

编辑 我找到了对服务器选项 IGNORE SPACE 的引用. 它指出可以使用IGNORE SPACE SQL模式来修改解析器如何处理对空格敏感的函数名"，随后又指出，最新版本的mysql已将该数字从200减少到30.

EDIT I have found a reference to server option IGNORE SPACE. It states that "The IGNORE SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive", later on it states that recent versions of mysql have reduced this number from 200 to 30.

例如，剩余的30个之一是COUNT. 同时启用IGNORE SPACE

One of the remaining 30 is COUNT for example. With IGNORE SPACE enabled both

SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;

是合法的.

因此，如果这是一个例外，我只能得出一个结论，通常情况下，默认情况下，函数会忽略空间.

So if this is an exception, I am left to conclude that normally functions ignore space by default.

如果函数默认情况下忽略空格，那么如果上下文是模棱两可的(例如，对于select表达式的第一项上的第一个函数)，则它们无法与关键字区分开，并且不能引发错误，MySQL必须接受它们作为关键字.

If functions ignore space by default then if the context is ambiguous, such as for the first function on a first item of the select expression, then they are not distinguishable from keywords and the error can not be thrown and MySQL must accept them as keywords.

不过，我的结论仍然有很多假设，我仍将不胜感激，并接受任何指示，以了解在何处对此进行跟进.

Still, my conclusions feel like they have lot of assumptions, I would still be grateful and accept any pointers to see where to follow up on this.

推荐答案

出于完整性考虑，我正在回答自己的问题并链接到另一个问题. 看来，此行为是SQL标准的直接结果，允许在函数和括号之间留有空格.

For completeness sake I am answering my own and linking to another question of my own. It seems that this behaviour is a direct consequence of SQL standard allowing whitespace between the function and parenthesis.

由于(通常)允许说出FUNCTION_NAME(x)，所以当将此功能应用于选择的第一项时

Since it is (generally) allowed to say FUNCTION_NAME (x) then when this function is applied to a first term of select

SELECT FUNCTION_NAME (x)

然后解析器将很难确定这是函数名还是SELECT修改关键字的上下文.

then there parser is going to have a hard time establishing if this is a context of a function name or SELECT modifying keyword.

因此，在上述情况下，FUNCTION_NAME实际上是解析器的FUNCTION_NAME_OR_KEYWORD.

So in the above case the FUNCTION_NAME is actually FUNCTION_NAME_OR_KEYWORD to the parser.

但是它更进一步:由于允许使用函数名和括号之间的空格，因此解析器实际上无法区分

But it goes further: since the space between function name and parenthesis IS allowed the the parser actually can NOT distinguish between

SELECT FUNCTION_NAME_OR_KEYWORD (x)

和

SELECT FUNCTION_NAME_OR_KEYWORD(x)

(必须测试关键字以查看它们是否为函数)，并且由于(x)将被解析为x，因此对于FUNCTION_NAME_OR_KEYWORD-> DISTINCT(以及所有其他SELECT修改关键字)而言，两者之间没有区别

(it must test the keywords to see if they are functions), and since (x) will be parsed to x it follows that for FUNCTION_NAME_OR_KEYWORD -> DISTINCT (and all other SELECT modifying keywords) there is no difference between

SELECT DISTINCT x, y, z, ...

和

SELECT DISTINCT(x), y, z, ...

QED，但没有硬引用(假设 standard <我相信/a>不在乎函数名称和括号之间的空格是合理的，但是我无法遵循BNF语法，直到可以引用确切的规则为止.

QED, but without hard references (assumption that standard does not care about whitespace between function names and parenthesis is, I believe, justified, but I was unable to follow BNF grammar to the point that I could quote the exact rule).

注意:mysql具有一定数量的函数，它关心函数和括号之间的空格，但是我认为这些都是例外(因此可以选择服务器选项来忽略它)

NOTE: mysql has certain number of functions where it cares about whitespace between functions and parenthesis, but I believe that these are exceptions (hence server option to ignore it)

这篇关于MySQL如何在参考文档中定义DISTINCT()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！