<> Implementation effect :

1,count(1) and count(*)
When the table has a large amount of data , After analyzing the table , use count(1) More than using count() It took a lot of time !
From the perspective of implementation plan ,count(1) and count(
) The effect is the same . But after the table has been analyzed ,count(1) Will compare count(*) It takes less time (1w Data volume within ), But not much .

If count(1) It's a clustered index ,id, That must be count(1) fast , But the difference is very small .
because count(), Which field will be optimized automatically . So there's no need to go count(1), use count(
),sql It'll help you with the optimization , therefore :count(1) and count(*) Basically no difference !

2,count(1) and count( field )
The main difference between the two is
count(1) All records in the table are counted , Include fields as null Record of .
count( field ) The number of times the field appears in the table is counted , Ignore field as null The situation . That is, the field is not counted null Record of .

count(*) and count(1) and count( Listing ) difference
Execution effect :

* count(*) All columns are included , Equivalent to the number of rows , In the statistical results , The column value is not ignored NULL.
* count(1) Includes ignoring all columns , use 1 Represents a line of code , In the statistical results , The column value is not ignored NULL .
*
count( Listing ) Only include the column name , In the statistical results , Null column values are ignored ( The null here is not just an empty string or 0, It means null) Count of , That is, a field value is NULL Time , No statistics .
On the efficiency of implementation :

* The column name is the primary key ,count( Listing ) Will compare count(1) fast .
* The column name is not a primary key ,count(1) Will compare count( Listing ) fast .
* If the table has multiple columns and no primary key , be count(1) Is better than count(*).
* If there is a primary key , be select count( Primary key ) The efficiency of implementation is optimal .
* If the table has only one field , be select count(*) optimal .
<> Case analysis
mysql> create table counttest(name char(1), age char(2)); Query OK, 0 rows
affected(0.03 sec) mysql> insert into counttest values -> ('a', '14'),('a', '15'
), ('a', '15'), -> ('b', NULL), ('b', '16'), -> ('c', '17'), -> ('d', null), ->(
'e', ''); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings
:0 mysql> select * from counttest; +------+------+ | name | age | +
------+------+ | a | 14 | | a | 15 | | a | 15 | | b | NULL | | b | 16 | | c | 17
| | d | NULL | | e | | +------+------+ 8 rows in set (0.00 sec) mysql> select
name, count(name), count(1), count(*), count(age), count(distinct(age)) -> from
counttest-> group by name; +
------+-------------+----------+----------+------------+----------------------+
| name | count(name) | count(1) | count(*) | count(age) | count(distinct(age)) |
+------+-------------+----------+----------+------------+----------------------+
| a | 3 | 3 | 3 | 3 | 2 | | b | 2 | 2 | 2 | 1 | 1 | | c | 1 | 1 | 1 | 1 | 1 | |
d| 1 | 1 | 1 | 0 | 0 | | e | 1 | 1 | 1 | 1 | 1 | +
------+-------------+----------+----------+------------+----------------------+
5 rows in set (0.00 sec)

Technology
©2019-2020 Toolsou All rights reserved,
be based on RK3399 PWM Drive development Output string at specified position ( Detailed analysis )Mybatis Mapping file Mapper.xml in # and $ The difference between vue use THREE.js Create a cube that you can control TypeScript Data types in is enough python in switch_to_alert Usage of ( Essence )2020 year 6 month 29 day C# Class library Interface signature verification Shortest path Dijkstra (Python3)css Basics 2:flex Multi row layout with gaps python primitive -- lock Lock