what is mysql count

mysql count is an aggregate function used to return the number of rows matching specified matching conditions; the syntax of the count function is such as "select count(*) from user;", which means counting all records, including NULL.

#This tutorial Operating environment: Windows 10 system, mysql8 version, Dell G3 computer.

What is mysql count?

MySql statistical function COUNT detailed explanation

1. COUNT() function overview

COUNT() is a Aggregation function that returns the number of rows matching specified conditions. In development, it is often used to count the data in the table, all data, not NULL data, or to remove duplicate data.

#2. COUNT() parameter description

COUNT(1): Count records that are not NULL.

COUNT(*): Count all records (including NULL).

COUNT(field): Count the records whose "field" is not NULL.

• If this field is defined as not null, read this field from the record line by line, judge that it cannot be null, and accumulate it line by line.

• If this field definition allows null, it is judged that it may be null, and the value must be taken out and judged. If it is not null, it will be accumulated.

COUNT(DISTINCT field): Count the records where the "field" is deduplicated and is not NULL.

-- MySql统计函数count测试
-- 创建用户表，新增测试数据
CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
  `name` varchar(64) DEFAULT NULL COMMENT '姓名',
  `sex` varchar(8) DEFAULT NULL COMMENT '性别',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  `born` date DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户表';

INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (1, '%张三%', '男', 22, '2022-04-22');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (2, '李四', '女', 12, '2022-04-01');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (3, '王小二', '女', 12, '2022-04-28');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (4, '赵四', '男', 23, '2022-04-28');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (5, '', '女', 23, '2022-04-28');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (6, NULL, '女', 60, '2022-04-28');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (7, NULL, '女', 61, '2022-04-28');

select * from user;

-- 统计数据：7条数据，统计所有的记录（包括NULL）。
select count(*) from user;

-- 统计数据：7条数据，统计不为NULL 的记录。
select count(1) from user;

-- 统计数据：5条数据，COUNT(字段)：统计该"字段"不为NULL 的记录，注意是null不是空''字符串
select count(name) from user;

-- 统计数据：5条数据，COUNT(DISTINCT 字段)：统计该"字段"去重且不为NULL 的记录。
select count(distinct name) from user;

3. COUNT() determines the existence

SQL no longer uses count, but instead uses LIMIT 1, so that when the database query encounters one, it will return. Do not If you continue to search for how many items there are, you can directly determine whether it is non-empty in the business code.

select 1 from emp LIMIT 1; The efficiency is the highest, especially the need to limit the number of rows, which is easy to ignore.

-- SQL查找是否"存在"
-- 员工表,存在则进行删除
drop table if EXISTS emp;
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    empname varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    depno mediumint unsigned not null default 0
);

-- 新增cehsi数据
测试数据：https://blog.csdn.net/m0_37583655/article/details/124385347

-- cahxun 
select * from emp ;

-- 时间：1.082s，数据：5000000
explain select count(*) from emp;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	  SIMPLE										Select tables optimized away

-- 时间：1.129s，数据：5000000
explain select count(1) from emp;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	  SIMPLE				Select tables optimized away

-- 时间：1.695s，数据：5000000
explain select 1 from emp;
id	select_type	table	partitions	type	possible_keys	key	 key_len	ref	rows	  filtered	Extra
1	  SIMPLE			emp		idx_emp_depno	3 4981060	100.00	Using index

-- SQL不再使用count，而是改用LIMIT 1，让数据库查询时遇到一条就返回，不要再继续查找还有多少条了,业务代码中直接判断是否非空即可
-- 时间：0.001s，数据：5000000
explain select 1 from emp LIMIT 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	  filtered	Extra
1	  SIMPLE			emp		idx_emp_depno		3	4981060		100.00	Using index

4. COUNT() Alibaba development specifications

1. [Mandatory] Do not use count (column name) or count (constant) To replace count(

), count() is the standard syntax for counting rows defined by SQL92. It has nothing to do with the database, and has nothing to do with NULL or non-NULL. Note: count(*) will count rows with NULL values. , and count (column name) will not count rows with NULL values ​​in this column.

2. [Mandatory] count(distinct col) calculates the number of unique rows in this column except NULL. Note that count( distinct col1, col2) If one of the columns is all NULL, then even if the other column has a different value, it will return 0.

[Related recommendations:

mysql video Tutorial】

The above is the detailed content of what is mysql count. For more information, please follow other related articles on the PHP Chinese website!