The usage of count (), union () and group by statements in MySQL

This article introduces the knowledge about "count(), union() and group by statements in MySQL". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Different uses of count() in MySQL

Count() is an aggregate function that evaluates the returned result set line by line, adding 1 to the cumulative value if the count argument is not NULL, otherwise not adding. Finally, the cumulative value is returned. [Recommended: mysql video tutorial]

1. For count(primary key id), the InnoDB engine iterates through the table, retrieving the id value for each row and returning it to the server layer. After the server layer gets the id, it judges that it is impossible to be empty, and accumulates it by line.

2. For count(1), the InnoDB engine traverses the entire table but takes no value. server layer For each row returned, put a number 1 into it, judge if it is impossible to be empty, accumulate by row

3. For count(field), 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 by line; if this field definition allows null, then when executing, judge that it may be null, and take out the value and judge again, only accumulate if it is not null.

4. For count(*), not all fields are taken out, but optimized specifically. No value, count(*) must not be null, accumulate by row

II. Union execution process

To facilitate quantitative analysis, table t1 below is used as an example

create table t1(id int primary key, a int, b int, index(a));CREATE DEFINER=`root`@`%` PROCEDURE `idata`()BEGIN declare i int; set i=1; while(i