Mysql: Common methods for count

COUNT()Function: Returns the number of rows that match the specified condition

In most cases, business scenarios require statistics on the number of data sets and processing and conversion. Here you can use the count() function to complete it, paying special attention to parameter issues, count(*), count(1), count(column name), and count(distinct column name). The following are the differences between these parameters

1. Usage

COUNT(*) Syntax

COUNT(*) functionReturns the number of records in the table,Include all columns，Number of rows equivalent to statistics table, when stating the results,Records with column value NULL are not ignored.

COUNT(1) Syntax

The COUNT(1) function ignores all columns. 1 represents a fixed value. It can also be replaced by count(2) and count(3). When stating the results,Records with column value NULL are not ignored.

COUNT(column_name) syntax

COUNT(column_name) function returns the number of values of the specified column (NULL does not count): only includes the column name specified column, return the number of records in the specified column, when stating the results,Records with column value NULL (excluding empty strings and 0) will be ignored, that is, records with column value NULL are not included.

COUNT(DISTINCTcolumn_name) Syntax

COUNT(DISTINCT column_name) function returns the number of different values of the specified column: only include the column name specified column,Returns the number of records with different values in the specified column, when stating the results,Records with column value NULL (excluding empty strings and 0) will be ignored, that is, records with column value NULL are not included.

two,performanceEfficiency comparison.count(*)&count(1)&count(column name)

(1)Column as primary key, count (column name) efficiency is better than count (1)

(2)Column is not primary key, count(1) is more efficient than count(column name)

(3) If in the tableThe primary key exists, count (primary key column name) is the best efficiency

(4)There is only one column in the table.Then count(*) is the best efficiency

(5) If the table hasMultiple columns, and there is no primary key, then count(1) is more efficient than count(*)

3. How to count column values as NULL

Note: Because records with NULL column values are not ignored during count(*) and count(1) statistics, the number of records with NULL column values can be counted in the following two ways:

(1) select count(*) from T where column name is null;
(2) select count(1) from T where column name is null;

In addition, we have some special circumstances as follows: