count(*) 和 count(column_name)，有什么区别? [英] count(*) and count(column_name), what's the diff?

问题描述

count(*) 和 count(column_name)，mysql 有什么区别.

解决方案

• COUNT(*) 计算结果集中的所有行(如果使用 GROUP BY，则为分组).
• COUNT(column_name) 只计算那些 column_name 不是 NULL 的行.在某些情况下，即使没有 NULL 值，这也可能会更慢，因为必须检查该值(除非该列不可为空).
• COUNT(1) 与 COUNT(*) 相同，因为 1 永远不会为 NULL.

要查看结果的差异，您可以尝试这个小实验:

CREATE TABLE table1 (x INT NULL);INSERT INTO table1 (x) VALUES (1), (2), (NULL);选择COUNT(*) 作为一个，COUNT(x) AS b,COUNT(1) 作为 c从表1;

结果:

<前>a b c3 2 3

count(*) and count(column_name), what's the difference in mysql.

解决方案

• COUNT(*) counts all rows in the result set (or group if using GROUP BY).
• COUNT(column_name) only counts those rows where column_name is NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable).
• COUNT(1) is the same as COUNT(*) since 1 can never be NULL.

To see the difference in the results you can try this little experiment:

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
    COUNT(*) AS a,
    COUNT(x) AS b,
    COUNT(1) AS c
FROM table1;

Result:

a   b   c
3   2   3

这篇关于count(*) 和 count(column_name)，有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！