MySQL中count(*)、count(1)和count(col)的区别汇总

2024-07-24 12:50:33

字体：大中小

来源：转载

供稿：网友

前言

count函数是用来统计表中或数组中记录的一个函数，count(*) 它返回检索行的数目，不论其是否包含 NULL值。最近感觉大家都在讨论count的区别，那么我也写下吧：欢迎留言讨论，话不多说了，来一起看看详细的介绍吧。

1、表结构：

dba_jingjing@3306>[rds_test]>CREATE TABLE `test_count` ( -> `c1` varchar(10) DEFAULT NULL, -> `c2` varchar(10) DEFAULT NULL, -> KEY `idx_c1` (`c1`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.11 sec)

2、插入测试数据：

dba_jingjing@3306>[rds_test]>insert into test_count values(1,10);Query OK, 1 row affected (0.03 sec)dba_jingjing@3306>[rds_test]>insert into test_count values(abc,null);ERROR 1054 (42S22): Unknown column 'abc' in 'field list'dba_jingjing@3306>[rds_test]>insert into test_count values('abc',null);Query OK, 1 row affected (0.04 sec)dba_jingjing@3306>[rds_test]>insert into test_count values(null,null);Query OK, 1 row affected (0.04 sec)dba_jingjing@3306>[rds_test]>insert into test_count values('368rhf8fj',null);Query OK, 1 row affected (0.03 sec)dba_jingjing@3306>[rds_test]>select * from test_count;+-----------+------+| c1 | c2 |+-----------+------+| 1 | 10 || abc | NULL || NULL | NULL || 368rhf8fj | NULL |+-----------+------+4 rows in set (0.00 sec)

测试：

dba_jingjing@3306>[rds_test]>select count(*) from test_count;+----------+| count(*) |+----------+| 4 |+----------+1 row in set (0.00 sec) EXPLAIN: { "query_block": { "select_id": 1, "message": "Select tables optimized away" 1 row in set, 1 warning (0.00 sec)

dba_jingjing@3306>[rds_test]>select count(1) from test_count;+----------+| count(1) |+----------+| 4 |+----------+1 row in set (0.00 sec) EXPLAIN: { "query_block": { "select_id": 1, "message": "Select tables optimized away" 1 row in set, 1 warning (0.00 sec)

dba_jingjing@3306>[rds_test]>select count(c1) from test_count;+-----------+| count(c1) |+-----------+| 3 |+-----------+1 row in set (0.00 sec) "table": { "table_name": "test1", "access_type": "index", "key": "idx_c1", "used_key_parts": [ "c1" ], "key_length": "33",

那么这里面的"key_length": "33",为什么是33呢，什么是二级索引？见下节

count(*) 和count(1) 是没有区别的，而count(col) 是有区别的

执行计划有特点：可以看出它没有查询索引和表，有时候会出现select tables optimized away 不会查表，速度会很快

Extra有时候会显示“Select tables optimized away”，意思是没有更好的可优化的了。