Databases allow null values, which is often the beginning of tragedy (1-minute series)

Database fields allow null values, there will be some problems, here contains some knowledge, and everyone to talk about.

Data preparation:

create table user (

id int,

name varchar(20),

index(id)

)engine=innodb;

insert into user values(1,’shenjian’);

insert into user values(2,’zhangsan’);

insert into user values(3,’lisi’);

Description:

Id is a non-unique index and null is allowed.

If a negative query fails to match an index, a full table scan will result.

explain select * from user where id! = 1;

Index field ID is not equal to query, as shown in the figure above:

(1) type=ALL;

(2) select * from table where rows= 1;

Null values are allowed, not equal to (! =)** query, which may result in unexpected results.

insert into user(name) values(‘wangwu’);

Create a data set with id NULL and see that there are four records.

select * from user where id! = 1 _; _

Executing again is not the same as querying.

Can you guess how many records there are in the result set (4, not 1 excluded)?

A wrong!

The result set has only 2 records, and the null record records do not appear in the result set.

select * from user where id! =1 or id is null;

An OR condition must be added if we want to get a result set that conforms to our expectations.

_ Voiceover: _ Yuck not yuck, have you stepped in this big hole?

** Table scan may result in a full table scan if there are some or conditions.

explain select * from user where id=1;

An equivalent query on the index field ID that hits the index, as shown in the figure above:

(1) type=ref;

(2) rows=1;

explain select * from user where id is null;

A null query on the index field ID can also hit the index, as shown in the figure above:

(1) type=ref;

(2) rows=1;

explain select * from user where id=1 or id is null;

SQL > alter table scan; SQL > alter table scan;

(1) type=ALL;

(2) select * from table where rows=4;

explain select * from user where id=1

union

select * from user where id is null;

At this point, the union query should be optimized to hit the index again, as shown above:

(1) type=ref;

(2) rows=1;

_ Voiceover: _ The third row of temporary table ALL is the merge of two result sets.

conclusion

(1) Negative comparison (e.g.! =) causes a full table scan;

(2) If null values are allowed, they are not equal to (! Select * from row where row is null; select * from row where row is null;

(3) OR may lead to full table scan, which can be optimized as union query;

(4) add the default value when building the table, so as to avoid null value pit;

(5) The Explain tool is a good thing;

Hope you have a good harvest!