为什么我需要"OR NULL"?在MySQL中计算带有条件的行时 [英] Why do I need "OR NULL" in MySQL when counting rows with a condition
问题描述
关于MySQL的COUNT()聚合函数存在一个问题，该函数会时不时地出现在我的脑海中.我想对为什么它按原样工作有一些解释.
There is a question about MySQL's COUNT() aggregate function that keeps popping into my head time to time. I would like to get some explanation to why it is working the way it is.
当我开始使用MySQL时，我很快了解到，如果condition末尾也包含OR NULL，则它的COUNT(condition)似乎只能正常工作.在更复杂的COUNT条件的情况下，找出确切的位置是一个经验过程.在MSSQL中，不需要此OR NULL即可获得正确的结果，因此我想了解其解释.所以，这是一个例子.
When I started working with MySQL I quickly learned that its COUNT(condition) seems only to work properly if condition also contains an OR NULL in the end. In case of more complicated COUNT conditions it was an empirical process to find out where to put it exactly. In MSSQL you do not need this OR NULL to get proper results, so I would like to know the explanation for it. So, here is an example.
我们有一个非常基本的表，其中包含以下结构和数据:
Lets have a very basic table with the following structure and data:
CREATE TABLE test (
`value` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO test (value) VALUES(1);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(5);
INSERT INTO test (value) VALUES(6);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(4);
INSERT INTO test (value) VALUES(5);
INSERT INTO test (value) VALUES(2);
INSERT INTO test (value) VALUES(8);
INSERT INTO test (value) VALUES(1);
场景:我想计算值= 4的行数.一个明显的解决方案是使用WHERE过滤并执行COUNT(*)，但我对COUNT(condition)感兴趣基于解决方案.
Scenario: I would like to count how many rows I have where the value = 4. An obvious solution would be to filter for it using a WHERE and do a COUNT(*) but I am interested in a COUNT(condition) based solution.
所以，我想到的解决方案是:
So, the solution that comes to my mind is:
SELECT COUNT(value=4)
FROM test
结果为10.这显然是错误的.
The result is 10. This is obviously wrong.
OR OR NULL的第二次尝试:
Second attempt with OR NULL:
SELECT COUNT(value=4 OR NULL)
FROM test
结果为3.这是正确的.
The result is 3. It is correct.
有人可以解释其背后的逻辑吗?是MySQL中的某个错误，还是有逻辑上的解释，为什么我需要在COUNT条件的末尾添加看起来很奇怪的OR NULL才能获得正确的结果?
Can someone explain the logic behind this? Is this some bug in MySQL or is there a logical explanation why I need to add that strange-looking OR NULL to the end of the COUNT condition to get the correct result?
推荐答案
这应该显示所有内容
SELECT 4=4, 3=4, 1 or null, 0 or null
输出
1 | 0 | 1 | NULL
事实
-
COUNT个累加结果为NOT NULL的列/表达式.只要不为空，任何东西都将增加1.例外是COUNT(DISTINCT)，它仅在尚未计数时才递增.
COUNT adds up the columns / expressions that evaluate to NOT NULL. Anything will increment by 1, as long as it is not null. Exception is COUNT(DISTINCT) where it increments only if it is not already counted.
单独使用BOOLEAN表达式时，它将返回1或0.
When a BOOLEAN expression is used on its own, it returns either 1 or 0.
当布尔值OR -ed为NULL时，仅当其为0(假)时为NULL
When a boolean is OR-ed with NULL, it is NULL only when it is 0 (false)
致他人
是的，如果计数是所需的唯一列，则可以使用WHERE value=4，但是如果这是一个要对4的以及进行计数并获取其他计数/汇总的查询，则过滤器不起作用.另一种选择是SUM(value=4)，例如
Yes if the count is the ONLY column desired, one could use WHERE value=4 but if it is a query that wants to count the 4's as well as retrieving other counts/aggregates, then the filter doesn't work. An alternative would have been SUM(value=4), e.g.
SELECT sum(value=4)
FROM test
这篇关于为什么我需要"OR NULL"?在MySQL中计算带有条件的行时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
