MySQL series of experience summary and analysis tutorials on NUll values

1. Test Data

create table test_null (
    id int,
    name varchar(20),
    chinese int,
    math int,
    english int
) charset=utf8;

insert into test_null 
values
(1,null,80,70,68),
(2,'张三',60,null,null),
(3,'Li Si',null,90,80),
(4,'Wang Wu',90,60,75),
(5,null,null,50,95);

The results are as follows:

2. The inconvenience caused by null value

1) The filtering is different, you can only use is null or is not null;

# Null values ​​cannot be compared using == or != # Both of the following are incorrect select *
from test_null
where name == null;

select *
from test_null
where name != null;

# Null values ​​are generally compared using is null or is not null # The following two usages are the correct choices *
from test_null
where name is null;

select *
from test_null
where name is not null;

2) The presence of a null value will cause + - * / operations to fail;

select 	
	*,(chinese+math+english) as total score from test_null;

The results are as follows:

3) Null values ​​have no effect on aggregate functions, and aggregate functions will directly ignore null values;

select 
    sum(chinese) total score of Chinese,
    sum(math) total score of mathematics,
    sum(english) total foreign language score from test_null

The results are as follows:

3. How should we judge spaces, empty values ​​and null?

1) The difference between spaces, empty values ​​and null

Use a vivid metaphor to illustrate the difference between these three. First of all, spaces are easy to understand. An empty string occupies a certain amount of space. What is difficult to understand is actually the empty value and null. The empty value is equivalent to a cup in a vacuum state, with nothing in it, while null means there is air in the cup.

In MySQL, null is unknown and takes up space. NULL makes indexes, index statistics, and values ​​more complex and affects the optimizer's decisions. Empty values ('') do not take up space. Note that there is no space between the '' of empty values. When counting the number of records in a column using count() , if there is a null value, it will be automatically ignored by the system, but the empty value will be counted. To judge whether null is used, is null and is not null . However, to judge whether an empty character is used = ，!=， <> are used. For the timestamp data type, if you insert null值, the value that appears is the current system time. If you insert a null value, 0000-00-00 00:00:00 will appear. For tables that have already been created, changing null to not null in common columns will only bring a small performance improvement, so you don't need to pay attention to it when tuning.

2) What should I do if a null value appears?

Through the above analysis, we already know that when there are null values ​​in the table, addition, subtraction, multiplication and division operations will fail. So how do we deal with these null values?

The first method: directly use is not null to filter out these null values, but this will filter out other fields with non-missing values, resulting in data waste.

The second method: This is also the method we recommend. We use functions to fill in missing values.

Use of ifnull() and coalesce() functions:

select 
    	id,
    	coalesce(name,'Anonymous') name,
    	coalesce(chinese,0) chinese,
    	ifnull(math,0) math,
    	ifnull(english,0) english
from test_null;

The results are as follows:

The above is the detailed content of the experience summary and analysis of MySQL series on NUll values. For more information about NUll values ​​in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!