在MySQL中使用条件计算行数时，为什么需要“OR NULL

bfrts1fy 于 2022-11-21 发布在 Mysql

关注(0)|答案(6)|浏览(226)

有一个关于MySQL的COUNT（）聚合函数的问题一直在我的脑海中闪现。我想得到一些解释，为什么它是这样工作的。
当我开始使用MySQL时，我很快就了解到，它的COUNT（condition）似乎只有在condition最后还包含一个OR NULL时才能正常工作。在更复杂的COUNT条件中，需要根据经验来找出它的确切位置。在MSSQL中，你不需要这个OR NULL来得到正确的结果，所以我想知道它的解释。下面是一个例子。
让我们有一个非常基本的表，其中包含以下结构和数据：

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（条件）的解决方案感兴趣。
所以，我想到的解决方案是：

SELECT COUNT(value=4) 
  FROM test

结果是10。这显然是错误的。
使用OR NULL进行第二次尝试：

SELECT COUNT(value=4 OR NULL) 
  FROM test

结果是3。它是正确的。
有人能解释一下这背后的逻辑吗？这是MySQL中的某个bug吗？或者有什么逻辑解释为什么我需要在COUNT条件的末尾添加那个看起来很奇怪的OR NULL来得到正确的结果？

mysql

来源：https://stackoverflow.com/questions/5011239/why-do-i-need-or-null-in-mysql-when-counting-rows-with-a-condition

6条答案

按热度按时间

bd1hkmkf1#

这应该能揭示所有

SELECT 4=4, 3=4, 1 or null, 0 or null

输出量
详情

COUNT将计算结果为NOT NULL的列/表达式相加。只要不为空，任何值都将以1递增。COUNT（DISTINCT）例外，它仅在尚未计数时递增。
1.当单独使用BOOLEAN表达式时，它返回1或0。
1.当布尔值用NULL进行OR艾德时，仅当它为0（false）时才为NULL

对其他人

是的，如果计数是唯一需要的列，可以使用WHERE value=4，但是如果它是一个想要计数4的 * 以及 * 检索其他计数/聚合的查询，那么过滤器就不起作用。

SELECT sum(value=4)
  FROM test

赞(0）回复(0）举报 2022-11-21

i7uq4tfw2#

COUNT()函数接受一个参数，该参数被视为NULL或NOT NULL。如果它是NOT NULL-，则它递增该值，否则不执行任何操作。
在你的例子中，表达式value=4要么是TRUE，要么是FALSE，显然true和false都不为空，这就是为什么你得到10。
但我对基于COUNT（条件）的解决方案感兴趣。
基于count的解决方案将总是比较慢（慢得多），因为它会导致表全扫描和每个值的迭代比较。

赞(0）回复(0）举报 2022-11-21

mu0hgdu03#

COUNT(expression)计算表达式不为NULL的行数。表达式value=4仅在值为NULL时才为NULL，否则为TRUE（1）或FALSE（0），这两种情况都会计算在内。
您可以改用SUM：

SELECT SUM(value=4) FROM test

这在您的特定示例中不是特别有用，但如果您希望使用单个表扫描来计算满足多个不同 predicate 的行数，则会非常有用，例如在以下查询中：

SELECT
    SUM(a>b) AS foo,
    SUM(b>c) AS bar,
    COUNT(*) AS total_rows
FROM test

赞(0）回复(0）举报 2022-11-21

dwbf0jvd4#

我建议您会发现，标准语法越多，在不同的数据库引擎之间移动得越好，并且总是会给予正确的结果。

select count(*)
 from test
 where value = 4

您使用的语法是Mysql变体吗？

赞(0）回复(0）举报 2022-11-21

uxhixvfz5#

这是因为COUNT（expression）计算VALUES。在SQL理论中，NULL是一个状态，而不是一个值，因此不计算在内。NULL是一种状态，表示字段的值未知。
现在，当你写“value=4”时，它的结果是布尔值TRUE或FALSE。因为TRUE和FALSE都是VALUES，所以结果是10。
当您加上“OR NULL”时，您实际上有“TRUE OR NULL”和“FALSE OR NULL”。现在，“TRUE OR NULL”的计算结果为TRUE，而“FALSE OR NULL”的计算结果为NULL。因此，结果为3，因为您只有3个值（和7个NULL状态）。

赞(0）回复(0）举报 2022-11-21

n9vozmp46#

下面是我测试后的直观图片：