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

问题描述

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

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

推荐答案

• COUNT(*)对结果集中的所有行(或使用GROUP BY的组)进行计数.
• COUNT(column_name)仅计算column_name不为空的那些行.即使在没有NULL值的情况下，这在某些情况下也可能会变慢，因为必须检查该值(除非该列不可为空).
• COUNT(1)与COUNT(*)相同，因为1永远不能为NULL.
• 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;

结果:

a   b   c
3   2   3

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