Shulou(Shulou.com)06/02 Report--

This article mainly explains the "MySQL series how to understand the null value", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "MySQL series how to understand the null value" bar!

Catalogue

1. test data

The inconvenient influence of 2.null value on us

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

1) the difference between spaces, null values, and null

2) there is a null value, what should I do?

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 (1recorder nullpje 80phie 70phi68), (2recorder 'Zhang San', 60pr nullpenal null), (3reci'Li Si', null,90,80), (4recorder 'Wang Wu', 90jin60jue 75), (5recorder 'nullwu', 90jol 50pint 95)

The results are as follows:

The inconvenient influence of 2.null value on us

1) filtering is different. Only is null or is not null can be used.

# null values cannot be compared using = = or!! # the following two uses are both wrong select * from test_nullwhere name = = null;select * from test_nullwhere name! = null;# null values generally use is null or is not null to compare # the following two uses are correct select * from test_nullwhere name is null;select * from test_nullwhere name is not null

2) the null value will cause the +-* / operation to fail.

Select *, (chinese+math+english) as Total score from test_null

The results are as follows:

3) the null value has no effect on the aggregate function, and the aggregate function will directly ignore the null value.

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

The results are as follows:

3. How should we judge spaces, null values, and null? 1) the difference between spaces, null values, and null

Use a figurative metaphor to illustrate the difference between the three. First of all, the space is easy to understand, an empty string, occupies a certain amount of space. What is difficult to understand is the null value and null, which means that a cup is vacuum and there is nothing. Null means that 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 judgment of the optimizer. Null values ('') do not take up space, and note that there are no spaces between null values. When count () counts the number of records in a column, if there is a null value, it will be automatically ignored by the system, but the null value will be counted into it. It is determined that null uses is null and is not null, but null characters are processed using =,! =. For the timestamp data type, if a null value is inserted, the value that appears is the current system time. Insert a null value and 0000-0000: 00:00 appears. For tables that have already been created, the performance improvement from ordinary columns changing null to not null is small, so there is no need to pay attention to tuning.

2) there is a null value, what should I do?

From the above analysis, we already know that when there is a null value in the table, it will invalidate the addition, subtraction, multiplication and division operations. So what do we do with these null values?

The first way: use is not null to filter out these null values directly, but this will filter out other fields that are not missing values, resulting in a waste of data.

The second way: which is also the way we recommend, we use functions to fill in the missing values.

Use of the ifnull () and coalesce () functions:

Select id, coalesce (name,' John Doe') name, coalesce (chinese,0) chinese, ifnull (math,0) math, ifnull (english,0) englishfrom test_null

The results are as follows:

Thank you for your reading, the above is the content of "MySQL series how to understand the value of nul". After the study of this article, I believe you have a deeper understanding of how to understand the value of null in the series of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.