反引号和撇号之间的最大区别是什么? [英] What makes the big difference between a backtick and an apostrophe?

问题描述

以下两个查询给出的结果截然不同的原因是什么?

What is the reason that the following two queries give wildly different results?

MariaDB [mydatabase]> SELECT COUNT(DISTINCT(`price`)) FROM `products`; --Good
+--------------------------+
| COUNT(DISTINCT(`price`)) |
+--------------------------+
|                     2059 |
+--------------------------+
1 row in set (0.01 sec)

MariaDB [mydatabase]> SELECT COUNT(DISTINCT('price')) FROM `products`; --Bad
+--------------------------+
| COUNT(DISTINCT('price')) |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.01 sec)

我已经在谷歌上搜索了反引号和撇号(又称为单引号)之间的区别，但是我找不到任何迹象表明为什么对于像上面这样的列名它们会有不同的解释.

I've googled around for an explanation of the difference between backticks and apostrophes (aka. single quotes), but I am unable to find any indication as to why they would be interpreted differently for a column name like in the above.

后一个查询中的单引号字符串实际上不是被解释为列名，而是被解释为任意字符串文字，可以说是"1"吗?如果是这样，要找到任何有关单引号含义的页面并不容易.

Is it that the single-quoted string in the latter query is actually not interpreted as a column name, but just as an arbitrary string literal, of which there could be said to be "1"? If so, it ain't easy to find any pages expounding on this meaning of the apostrophe.

推荐答案

'price'(撇号或引号)是一个字符串.它永远不会改变，因此计数始终为1.

'price' (apostrophes or quotes) is a string. It never changes, so the count is always 1.

`price`(后退)指的是price列.因此可能会超过1.

`price` (backtics) refers to the column price. So it could be more than 1.

内部括号无关. COUNT(DISTINCT price)与您的原始版本相同.

The inner parentheses are irrelevant. COUNT(DISTINCT price) is the same as your backtic version.

• SELECT COUNT(*) FROM tbl WHERE ...是询问多少行的常用方法.
• SELECT foo, COUNT(*) FROM tbl GROUP BY foo是询问foo的每个不同值需要多少行的一种常见方法.
• SELECT foo, COUNT(foo) FROM tbl GROUP BY foo与上述相同，但不计算foo IS NULL所在的行.

• SELECT COUNT(*) FROM tbl WHERE ... is a common way to ask how many rows.
• SELECT foo, COUNT(*) FROM tbl GROUP BY foo is a common way to ask how many rows for each distinct value of foo.
• SELECT foo, COUNT(foo) FROM tbl GROUP BY foo is the same as above, but does not count rows where foo IS NULL.

SELECT DISTINCT ... GROUP BY ...是废话.使用DISTINCT或使用GROUP BY.

SELECT DISTINCT ... GROUP BY ... is a nonsense statement. Either use DISTINCT or use GROUP BY.

这篇关于反引号和撇号之间的最大区别是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！