天天看點

count(1) count(*)

mysql> select 1 from t;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
+---+
4 rows in set (0.00 sec)      
mysql> select count(1) from t;    
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)      
mysql> select count(*) from t; 
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)      
mysql> select count(a) from t; 
+----------+
| count(a) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)      
mysql> explain  extended SELECT count(*) FROM `employees`;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | index | NULL          | PRIMARY | 4       | NULL | 299689 |   100.00 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.17 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------+
| Level | Code | Message                                                                   |
+-------+------+---------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `employees`.`employees` |
+-------+------+---------------------------------------------------------------------------+
1 row in set (0.17 sec)

mysql> explain  extended SELECT count(1) FROM `employees`; 
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | index | NULL          | PRIMARY | 4       | NULL | 299689 |   100.00 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------+
| Level | Code | Message                                                                   |
+-------+------+---------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(1) AS `count(1)` from `employees`.`employees` |
+-------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)      
  • COUNT(*)

     counts all rows
  • COUNT(column)

     counts non-NULLs only
  • COUNT(1)

     is the same as 

    COUNT(*)

     because 1 is a non-null expressions