MySQL中count(*)、count(1)和count(col)的區(qū)別匯總

2024-07-24 12:50:33

字體：大中小

來源：轉載

供稿：網友

前言

count函數是用來統(tǒng)計表中或數組中記錄的一個函數，count(*) 它返回檢索行的數目，不論其是否包含 NULL值。最近感覺大家都在討論count的區(qū)別，那么我也寫下吧：歡迎留言討論，話不多說了，來一起看看詳細的介紹吧。

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呢，什么是二級索引？見下節(jié)

count(*) 和count(1) 是沒有區(qū)別的，而count(col) 是有區(qū)別的

執(zhí)行計劃有特點：可以看出它沒有查詢索引和表，有時候會出現(xiàn)select tables optimized away 不會查表，速度會很快

Extra有時候會顯示“Select tables optimized away”，意思是沒有更好的可優(yōu)化的了。