本篇内容主要讲解“mysql中count()语句的用法介绍”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql中count()语句的用法介绍”吧!

查看表结构：

mysql>showcreatetablecoupon_use_test\G***************************1.row***************************Table:coupon_use_testCreateTable:CREATETABLE`coupon_use_test`(`id`int(11)NOTNULLDEFAULT'0',`user_id`varchar(40)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`coupon_code`varchar(40)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'',`status`varchar(2)CHARACTERSETutf8COLLATEutf8_binDEFAULT'00',`use_time`datetimeDEFAULTNULL,`remark1`varchar(200)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`remark2`varchar(200)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`remark3`varchar(200)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`create_time`timestampNULLDEFAULTCURRENT_TIMESTAMP,`create_user_id`varchar(128)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,`update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,KEY`idx_create_time`(`create_time`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)

查看create_time字段为空的行数

mysql>select*fromcoupon_use_testwherecreate_timeisnull;Emptyset(0.00sec)

把id为1的记录create_time改为空

mysql>updatecoupon_use_testsetcreate_time=nullwhereid=1;QueryOK,1rowaffected(6.56sec)Rowsmatched:1Changed:1Warnings:0mysql>selectcount(*)fromcoupon_use_testwherecreate_timeisnull;+----------+|count(*)|+----------+|1|+----------+1rowinset(0.00sec)

count(*)

mysql>selectcount(*)fromcoupon_use_test;+----------+|count(*)|+----------+|1800000|+----------+1rowinset(0.69sec)mysql>explainselectcount(*)fromcoupon_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|Usingindex|+----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+1rowinset,1warning(0.00sec)

可以看到count(*)走了create_time字段的索引idx_create_time

count(1)

mysql>selectcount(1)fromcoupon_use_test;+----------+|count(1)|+----------+|1800000|+----------+1rowinset(0.63sec)mysql>explainselectcount(1)fromcoupon_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|Usingindex|+----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+1rowinset,1warning(0.00sec)

count(create_time)

mysql>selectcount(create_time)fromcoupon_use_test;+--------------------+|count(create_time)|+--------------------+|1799999|+--------------------+1rowinset(0.73sec)mysql>explainselectcount(create_time)fromcoupon_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|Usingindex|+----+-------------+-----------------+------------+-------+---------------+-----------------+---------+------+---------+----------+-------------+1rowinset,1warning(0.00sec)

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()语句的用法介绍”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！