以ä¸é对Mysql count(1) ãcount(*)ãcount(å段)åºå«å´ç»ä»¥ä¸é®é¢è¿è¡å±å¼
â1ãCOUNTæå ç§ç¨æ³ï¼
2ãCOUNT(å段å)åCOUNT(*)çæ¥è¯¢ç»ææä»ä¹ä¸åï¼
3ãCOUNT(1)åCOUNT(*)ä¹é´æä»ä¹ä¸åï¼
4ãCOUNT(1)åCOUNT(*)ä¹é´çæçåªä¸ªæ´é«ï¼
5ã为ä»ä¹ãé¿éå·´å·´Javaå¼åæåã建议使ç¨COUNT(*)
6ãMySQLçMyISAMå¼æ对COUNT(*)åäºåªäºä¼åï¼
7ãMySQLçInnoDBå¼æ对COUNT(*)åäºåªäºä¼åï¼
8ãä¸é¢æå°çMySQL对COUNT()åçä¼åï¼æä¸ä¸ªå ³é®çåææ¯ä»ä¹ï¼
9ãSELECT COUNT(*) çæ¶åï¼å ä¸å whereæ¡ä»¶æå·®å«åï¼
10ãCOUNT(*)ãCOUNT(1)åCOUNT(å段å)çæ§è¡è¿ç¨æ¯ææ ·çï¼â
- COUNTæå ç§ç¨æ³ï¼
 çï¼count(*) ãcount(常é)ãcount(å段å)ãcount(distinct å段å)
- COUNT(å段å)åCOUNT(*ï¼çæ¥è¯¢ç»ææä»ä¹ä¸å
 çï¼COUNT(å段å)ï¼ç¬¦åæ¡ä»¶çå段åè¿è¡ç»è®¡ï¼ä¼å¤æå段æ¯å¦ä¸ºnul lï¼ä¸ºNullä¸è¿è¡ç»è®¡
    COUNT(*): çåäºcount(1),ä¼ç»è®¡ææè¡ï¼å å«NUll
å¦æéè¦ç»è®¡ç表æ没æè¾ å©ç´¢å¼åªæ主é®ç´¢å¼ï¼å°ä¼ä»¥è¾ å©ç´¢å¼è¿è¡ç»è®¡ï¼è¿ä¹æ¯innodb对select count(*)çä¼åï¼å 为主é®ç´¢å¼çå¶åèç¹åå¨äºä¸»é®åè¡æ°æ®çå¼ï¼èè¾ å©ç´¢å¼åªåå¨äºå¯¹åºç主é®ç´¢å¼å对åºç主é®ç´¢å¼çä½ç½®ï¼æä»¥è¾ å©ç´¢å¼çç´¢å¼æ æ´ä½ï¼æ以ç»è®¡çæ¶åä¼ä¼éè¾ å©ç´¢å¼ã
å¦æ没æè¾ å©ç´¢å¼ä¼éæ©ä¸»é®ç´¢å¼è¿è¡ç»è®¡
mysql> show create table t1;ï¼æ¤æ¶æ²¡æè¾
å©ç´¢å¼ï¼
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc select count(1) from t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
mysql> desc select count(*) from t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
mysql> show create table t1; (å¢å è¾
å©ç´¢å¼)
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> desc select count(*) from t1; ä¼éè¾
å©ç´¢å¼(èåç´¢å¼)è¿è¡ç»è®¡
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_name | 43 | NULL | 7 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.06 sec)
- COUNT(1)åCOUNT(*)ä¹é´æä»ä¹ä¸åï¼åªä¸ªæçæ´é«ï¼
â对äºcount(1)åcount()ï¼MySQLçä¼åæ¯å®å ¨ä¸æ ·çï¼æ ¹æ¬ä¸åå¨è°æ´å¿«ï¼ä½ä¾æ§å»ºè®®ä½¿ç¨count()ï¼å 为è¿æ¯SQL92å®ä¹çæ åç»è®¡è¡æ°çè¯æ³ã
count(1) ä¼ç»è®¡è¡¨ä¸çææçè®°å½æ°ï¼å å«å段为 NULL çè®°å½ï¼ä½å®æ¯ç¨ 1 代æ¿äºææåï¼ä¸å¨å ³æ³¨è¡¨ä¸å ·ä½åçæ åµï¼count(*) å æ¬äºææçåï¼ç¸å½äºè¡æ°ï¼å¨ç»è®¡ç»æçæ¶åï¼å®åæ ·ä¸ä¼å¿½ç¥ä¸º NULL çå¼ã
4.为ä»ä¹ãé¿éå·´å·´Javaå¼åæåã建议使ç¨COUNT(*)
å 为COUNT()âæ¯SQL92å®ä¹çæ åç»è®¡è¡æ°çè¯æ³ï¼å¹¶ä¸æçé«ï¼æ以请ç´æ¥ä½¿ç¨COUNT()æ¥è¯¢è¡¨çè¡æ°
5.MySQLçMyISAMå¼æ对COUNT(*)åäºåªäºä¼åï¼
âMySQL MyISAMåå¨å¼æï¼ç»è®¡æ°æ®è¡¨çè¡æ°åªéè¦O(1)å¤æ度ï¼è¿æ¯å 为æ¯å¼ MyISAMçæ°æ®è¡¨é½æä¸ä¸ªmetaä¿¡æ¯æåå¨äºrow_countå¼ãèä¸è´æ§ç±è¡¨çº§éæ¥ä¿è¯
6.MySQLçInnoDBå¼æ对COUNT(*)åäºåªäºä¼åï¼â
âInnoDBä¸è½ä½¿ç¨è¿ç§ç¼åæä½ï¼å 为æ¯æäºå¡ï¼å¤§é¨åæä½é½æ¯è¡çº§éï¼è¡å¯è½è¢«å¹¶è¡ä¿®æ¹ï¼é£ä¹ç¼åè®°å½ä¸åç¡®ã
ä½æ¯ï¼InnoDBè¿æ¯é对COUNT(*)è¯å¥åäºäºä¼åçã
éè¿ä½ææ¬çç´¢å¼è¿è¡æ«è¡¨ï¼èä¸å ³æ³¨è¡¨çå ·ä½å 容ã
InnoDBä¸ç´¢å¼å为èç°ç´¢å¼ï¼ä¸»é®ç´¢å¼ï¼åéèç°ç´¢å¼ï¼é主é®ç´¢å¼ï¼ï¼èç°ç´¢å¼çå¶åèç¹ä¸ä¿åçæ¯æ´è¡è®°å½ï¼èéèç°ç´¢å¼çå¶åèç¹ä¸ä¿åçæ¯è¯¥è¡è®°å½ç主é®çå¼ã
MySQLä¼ä¼å éæ©æå°çéèç°ç´¢å¼æ¥æ«è¡¨ã
ä¼åçåææ¯æ¥è¯¢è¯å¥ä¸ä¸å å«whereæ¡ä»¶ågroup byæ¡ä»¶ã
7.MySQLçInnoDBå¼æ对COUNT(*)åäºåªäºä¼åï¼
MyISAMï¼å¦æ没æ WHERE éå¶çè¯ï¼MySQLç´æ¥è¿åä¿åææ»çè¡æ°ï¼èå¨æ WHERE éå¶çæ åµä¸ï¼æ»æ¯éè¦å¯¹ MySQL è¿è¡å ¨è¡¨éåã
InnoDBï¼å¦æ没æ WHERE éå¶çè¯éè¿éåæå°çå¯ç¨çäºçº§ç´¢å¼æ¥å¤ç SELECT COUNT(*) è¯å¥ï¼èå¨æ WHERE éå¶çæ åµä¸ï¼ä¼æ ¹æ® WHERE éå¶ä¸éæ©éåæå°çå¯ç¨çäºçº§ç´¢å¼ï¼æ²¡æåä¸ºå ¨è¡¨éåã
8.COUNT(*)ãCOUNT(1)åCOUNT(å段å)çæ§è¡è¿ç¨æ¯ææ ·ç?
COUNT(*) å æ¬äºææçåï¼ç¸å½äºè¡æ°ï¼å¨ç»è®¡ç»æçæ¶åï¼ä¸ä¼å¿½ç¥åå¼ä¸º NULL
COUNT(1) å æ¬äºå¿½ç¥ææåï¼ç¨ 1 代表代ç è¡ï¼å¨ç»è®¡ç»æçæ¶åï¼ä¸ä¼å¿½ç¥åå¼ä¸º NULL