Зачем мне нужно "OR NULL" в MySQL при подсчете строк с условием

Question

Возникает вопрос о статистической функции MySQL COUNT(), которая время от времени всплывает в моей голове. Я хотел бы получить некоторое объяснение того, почему это работает так, как есть.

Когда я начал работать с MySQL, я быстро понял, что его COUNT (условие), похоже, работает правильно только в том случае, если условие также содержит 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 или есть логическое объяснение, почему мне нужно добавить странно выглядящий OR NULL в конец условия COUNT, чтобы получить правильный результат?

Answer 1

Это должно раскрыть все

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

Выход

1   |   0   |   1   |   NULL

факты

1. COUNT складывает столбцы / выражения, которые оцениваются как NOT NULL. Все будет увеличиваться на 1, пока оно не равно нулю. Исключением является COUNT(DISTINCT), где он увеличивается, только если он еще не подсчитан.

2. Когда BOOLEAN выражение используется само по себе, оно возвращает либо 1, либо 0.

3. Когда логическое значение OR-ед с NULL, это NULL, только когда это 0 (ложь)

Другим

Да, если количество является ТОЛЬКО желаемым столбцом, можно использовать WHERE value=4 но если это запрос, который хочет подсчитать 4, а также получить другие значения / агрегаты, тогда фильтр не работает. Альтернатива была бы SUM(value=4)например,

SELECT sum(value=4)
  FROM test

Answer 2

COUNT() функция принимает аргумент, который рассматривается как NULL или же NOT NULL, Если это NOT NULL - затем он увеличивает значение и ничего не делает иначе.

В вашем случае выражение value=4 либо TRUE или же FALSEочевидно оба true а также false не равны нулю, поэтому вы получаете 10.

но я заинтересован в решении COUNT(условие).

countрешение на основе всегда будет медленнее (намного медленнее), потому что оно приведет к полному сканированию таблицы и итеративному сравнению каждого значения.

Answer 3

COUNT(expression) подсчитывает количество строк, для которых выражение не NULL. Выражение value=4 только NULL, если значение равно NULL, в противном случае это либо TRUE (1), либо FALSE (0), оба из которых считаются.

1 = 4         | FALSE
4 = 4         | TRUE
1 = 4 OR NULL | NULL
4 = 4 OR NULL | TRUE

Вы можете использовать SUM вместо:

SELECT SUM(value=4) FROM test

Это не особенно полезно в вашем конкретном примере, но может быть полезно, если вы хотите подсчитать строки, удовлетворяющие нескольким различным предикатам, используя одно сканирование таблицы, например, в следующем запросе:

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

Answer 4

Вот интуитивное изображение после того, как я протестировал его:

Answer 5

Это потому, что COUNT(выражение) считает значения. В теории SQL NULL является СОСТОЯНИЕМ, а не ЗНАЧЕНИЕМ и, следовательно, не учитывается. NULL - это состояние, которое означает, что значение поля неизвестно.

Теперь, когда вы пишете "значение =4", это оценивается как логическое ИСТИНА или ЛОЖЬ. Так как TRUE и FALSE являются ЗНАЧЕНИЯМИ, результат равен 10.

Когда вы добавляете "ИЛИ НУЛЬ", вы на самом деле имеете "ИСТИНА ИЛИ НУЛЬ" и "ЛОЖЬ ИЛИ НУЛЬ". Теперь "TRUE OR NULL" оценивается как TRUE, а "FALSE OR NULL" - в NULL. Таким образом, результат равен 3, потому что у вас есть только 3 значения (и семь состояний NULL).

Answer 6

Я бы предположил, что вы найдете более стандартный синтаксис лучше перемещается между различными ядрами базы данных и всегда будет давать правильный результат.

 select count(*)
 from test
 where value = 4

Синтаксис вы использовали вариант Mysql?