我们都用过count()函数,最常用的就是全表统计行数。
select count(*) from tableName; count(*) 这里是计算全表的行数。
我们看官网的解释是:COUNT(*) counts the number of rows。
我们建表测试一下:
CREATE TABLE `tb_testFn_count` (
`id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(10) DEFAULT NULL,
`description` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
测试数据为:
INSERT INTO tb_testFn_count (title,description) VALUES ('title1','NO.1'),('title2','NO.2'),('title3','NO.3'),('title4','NO.4'),('title1',NULL);SELECT * FROM tb_testfn_count;
/*
id title description
1 title1 NO.1
2 title2 NO.2
3 title3 NO.3
4 title4 NO.4
5 title1 null
*/
SELECT COUNT(*) FROM tb_testfn_count;
/*
COUNT(*)
5
*/
SELECT COUNT(title) FROM tb_testfn_count;
/*
COUNT(title)
5
*/
SELECT COUNT(description) FROM tb_testfn_count;
/*
COUNT(description)
4
*/
从上面结果可以看出:
count(*)是统计行数
count(title)是统计title列有效数据行数
count(description)也是统计有效数据量行数,从结果看null值没有计入统计中。
关于count()进行分组统计,官方文档是这样说的:
(这里举例的表示官方文档上的,详情可以参考官方文档,文末有链接)
If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by
If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is indeterminate. The server is free to select the value from any row:
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
1 row in set (0.00 sec)
正确情形:
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
从上面可以看出,当使用count()函数进行分组统计时,如果未加group by,那么有两种情况:
1.当SET sql_mode = 'ONLY_FULL_GROUP_BY';
此时,查询会报错。
2.当SET sql_mode = '';
此时,The server is free to select the value from any row:mysql server会从所有行中任意取一个值。
注意:情况2中,这种不加group by的情况和加group by的情况返回结果是完全不同的。
不加group by 会count(*)统计所有行数,并随机返回一行数据,一般是第一行。
加group by 会按照指定列分组后统计,如上述正确那种情况。
参考官方文档:
https://dev.mysql.com/doc/refman/5.7/en/counting-rows.html