是什么使反引号和撇号之间有很大区别?

2022-01-15 00:00:00 sql mariadb mysql

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

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` (backtics) 指的是 price 列.所以它可能超过 1.

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

内括号无关紧要.COUNT(DISTINCT price) 和你的 backtic 版本一样.

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.

相关文章