本篇内容主要讲解“MySQL中count()语句的用法介绍”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql中count()语句的用法介绍”吧!
创新互联服务项目包括忻城网站建设、忻城网站制作、忻城网页制作以及忻城网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，忻城网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到忻城省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
查看表结构：
mysql> show create table coupon_use_test \G *************************** 1. row *************************** Table: coupon_use_test Create Table: CREATE TABLE `coupon_use_test` ( `id` int(11) NOT NULL DEFAULT '0', `user_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `coupon_code` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `status` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '00', `use_time` datetime DEFAULT NULL, `remark1` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `remark2` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `remark3` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `create_user_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `idx_create_time` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
查看create_time字段为空的行数
mysql> select * from coupon_use_test where create_time is null; Empty set (0.00 sec)
把id为1的记录create_time改为空
mysql> update coupon_use_test set create_time = null where id = 1; Query OK, 1 row affected (6.56 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select count(*) from coupon_use_test where create_time is null; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
count(*)
mysql> select count(*) from coupon_use_test; +----------+ | count(*) | +----------+ | 1800000 | +----------+ 1 row in set (0.69 sec) mysql> explain select count(*) from coupon_use_test; +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | coupon_use_test | NULL | index | NULL | idx_create_time | 5 | NULL | 1771323 | 100.00 | Using index | +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
可以看到count(*)走了create_time字段的索引idx_create_time
count(1)
mysql> select count(1) from coupon_use_test; +----------+ | count(1) | +----------+ | 1800000 | +----------+ 1 row in set (0.63 sec) mysql> explain select count(1) from coupon_use_test; +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | coupon_use_test | NULL | index | NULL | idx_create_time | 5 | NULL | 1771323 | 100.00 | Using index | +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
count(create_time)
mysql> select count(create_time) from coupon_use_test; +--------------------+ | count(create_time) | +--------------------+ | 1799999 | +--------------------+ 1 row in set (0.73 sec) mysql> explain select count(create_time) from coupon_use_test; +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | coupon_use_test | NULL | index | NULL | idx_create_time | 5 | NULL | 1771323 | 100.00 | Using index | +----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
count(*)和count(1)可以查询全表总行数，count(create_time)查询到的行数不包括null。
count(1) 与 count(*) 比较 :
1> 如果数据表没有主键，那么 count(1) 比 count(*) 快
2> 如果有主键的话，那主键 (联合主键) 作为 count条件也比 count(*) 要快
3> 如果你的表只有一个字段的话那 count(*) 就是最快
在不加 WHERE 限制条件的情况下，COUNT(*) 与 COUNT(COL) 基本可以认为是等价的，但是在有 WHERE 限制条件的情况下，COUNT(*) 会比 COUNT(COL) 快非常多
COUNT(*) 通常是对主键进行索引扫描，而COUNT(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的COL的纪录数
count(*) 与 count(1) 两者比较，主要还是要取决于 count(1) 所相对应的数据字段，如果count(1)是聚索引 id 那肯定是count(1)快，但是差的很小，因为 count(*) 自动会优化指定到那一个字段，所以没必要去count(?)用count(*)sql会自动完成优化
1> 任何情况下 SELECT COUNT(*) FROM tablename 是最优选择
2> 尽量减少 SELECT COUNT(*) FROM tablename WHERE COL = 'value’ 这种查询
3> 杜绝 SELECT COUNT(COL) FROM tablename 的出现
到此，相信大家对“mysql中count()语句的用法介绍”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
