56,678
社区成员
发帖
与我相关
我的任务
分享SELECT COUT(主键)、count(1)、count(*)的区别
SELECT COUT(主键)、count(1)、count(*)的区别
...全文
请发表友善的回复…
发表回复
xiang3630 2008-10-16
- 打赏
- 举报
增加一条有NULL的记录测试一下就知道
测了，都一样！50000条数据
测了，都一样！50000条数据
WWWWA 2008-09-22
- 打赏
- 举报
增加一条有NULL的记录测试一下就知道
wwwwb 2008-09-22
- 打赏
- 举报
如果只是主键，那么3个SQL语句结果是一致的，因为主键不能为NULL，如是其它字段，且有NULL的话，结果是
有区别的
有区别的
wwwwb 2008-09-22
- 打赏
- 举报
不是吧，COUNT（*）count(1)是算NULL行的，其它不算NULL
zhifeiji007 2008-09-21
- 打赏
- 举报
select cout(主键),好像没有见过。
其他两个能得出相同结果。
其他两个能得出相同结果。
懒得去死 2008-09-19
- 打赏
- 举报
效果是一样的，性能也没有啥区别！
随风醉舞 2008-09-19
- 打赏
- 举报
返回table表里面的记录总数
select count(1) from table的效率更好。
select count(1) from table的效率更好。
fcoolx 2008-09-19
- 打赏
- 举报
根据explian 得出结论 没区别
mysql> explain select count(1) from table1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from table1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(id) from table1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
mysql> explain select count(1) from table1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from table1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(id) from table1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
