What is the difference between mysql count(1) and count(*)

created at 07-27-2021 views: 2

count(1) and count() are generally used to count the amount of data in the entire table, including null values. count (column name) will only count the total number of columns that are not null. So in terms of quantity count(1) = count() >= count(column name).

Execution effect:

The effect of count(1) and count(*) is the same

Starting from MySQL 5.7, a tracking optimizer function is provided. The optimizer formats count() into count(0) before optimizing SQL. However, count(column name) is not optimized to others, because null values are removed, so in terms of performance count(1) = count()> count(column name)

The difference between count(*) and count(1) and count (column name)

In terms of execution effect:

  • count(*) includes all the columns, which is equivalent to the number of rows. When counting the results, the NULL value will not be ignored.
  • count(1) includes ignoring all columns, and 1 represents the code line. When counting the results, the value that is NULL will not be ignored.
  • count(column name) only includes the column name. When counting the results, the column value will be ignored (the empty string here does not refer to an empty string or 0, but a null) count, that is, a field value When NULL, no statistics.

In terms of execution efficiency:

count(*)=count(1)>count(primary key)>count(column)
created at:07-27-2021
edited at: 07-27-2021: