Mysql uses group by instead of NULL for grouping

In the project development, the query data needs to be merged with the same data, but the field is null, so it is not merged.

Create tables and add data

create table t_student(
       `id` int not null primary key auto_increment,
       `name` varchar(32) ,
       `age` int   
)
insert into t_student(`name`,age) values("aa",11);
insert into t_student(`name`,age) values('bb',12);
insert into t_student(`name`,age) values('cc',13);
insert into t_student(`name`,age) values('cc',14);
insert into t_student(`name`,age) values('cc',15);
insert into t_student(`name`,age) values(null,16);
insert into t_student(`name`,age) values(null,17);
Copy the code

Query data a total of 7 pieces of data

select * from t_student
Copy the code

Results:

Let’s do the name merge

select * from t_student group by name
Copy the code

Results:

The result was to merge all the null’s together.

The solution uses the replacement UUID()

In stackoverflow.com/questions/4… I saw a method on the. If name is null, set it to a random value UUID() to avoid null merging. Use the UUID () :

select * from t_student group by IFNULL(name,UUID())
Copy the code

Results: