count(1)和count(列名)的坑

count(1)和count(列名)的坑

参考网址:

count(1),count(*),count(列名)三者的区别

https://blog.csdn.net/ZZQHELLO2018/article/details/105711996?ops_request_misc=%25257B%252522request%25255Fid%252522%25253A%252522160974372916780277072033%252522%25252C%252522scm%252522%25253A%25252220140713.130102334…%252522%25257D&request_id=160974372916780277072033&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2alltop_click~default-1-105711996.first_rank_v2_pc_rank_v29&utm_term=count(1)%E3%80%81count(*)%20%E4%B8%8E%20count(%E5%88%97%E5%90%8D)%20%E7%9A%84%E6%89%A7%E8%A1%8C%E5%8C%BA%E5%88%AB

1.准备数据库脚本

 CREATE TABLE `demo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

针对性的添加测试数据

insert into demo select null , 'user1' from dual;
insert into demo select null , 'user2' from dual;
insert into demo select null , 'user3' from dual;
insert into demo select null , null  from dual;
insert into demo select null , null  from dual;
insert into demo select null , null  from dual;
insert into demo select null , null  from dual;

2.补充知识点

字段唯一约束 UNIQUE KEY(列名)

1.可以为空

2.列中的值不能重复

3.列中可以存在多个null值

3.测试

查询表中现有的数据

MariaDB [test]> select * from demo;
+----+-------+
| id | name  |
+----+-------+
|  4 | NULL  |
|  5 | NULL  |
|  6 | NULL  |
|  7 | NULL  |
|  8 | NULL  |
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
+----+-------+
8 rows in set (0.000 sec)

示例1

MariaDB [test]> select name , count(1) from demo group by name;
+-------+----------+
| name  | count(1) |
+-------+----------+
| NULL  |        5 |
| user1 |        1 |
| user2 |        1 |
| user3 |        1 |
+-------+----------+
4 rows in set (0.000 sec)

示例2

MariaDB [test]> select name , count(id) from demo group by name;
+-------+-----------+
| name  | count(id) |
+-------+-----------+
| NULL  |         5 |
| user1 |         1 |
| user2 |         1 |
| user3 |         1 |
+-------+-----------+
4 rows in set (0.000 sec)

示例3

select name , count(name) from demo group by name;
+-------+-------------+
| name  | count(name) |
+-------+-------------+
| NULL  |           0 |
| user1 |           1 |
| user2 |           1 |
| user3 |           1 |
+-------+-------------+
4 rows in set (0.000 sec)

说明:

我们用group by 对记录数进行分组查询时使用count(1)和count(主键)

建议使用count(1),这样是最保险的,而且count(1)效率也不比count(主键)慢