MySQL Null can cause 5 problems (all fatal)

Before we officially start, let's take a look at the configuration and version number information of the MySQL server, as shown in the following figure:

"The food and fodder must be ready before the troops move." After reading the relevant configuration, let's create a test table and some test data.

-- If the person table exists, delete it first DROP TABLE IF EXISTS person; 
 
-- Create a person table, where the username field can be empty, and set a normal index for it CREATE TABLE person (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 mobile VARCHAR(13),
 index(name)
)ENGINE='innodb';
 
-- Add test data to the person table insert into person(name,mobile) values('Java','13333333330'),
 ('MySQL','13333333331'),
 ('Redis','13333333332'),
 ('Kafka','13333333333'),
 ('Spring','13333333334'),
 ('MyBatis','13333333335'),
 ('RabbitMQ','13333333336'),
 ('Golang','13333333337'),
 (NULL,'13333333338'),
 (NULL,'13333333339');
 
select * from person;

The constructed test data is shown in the following figure:

Now that we have the data, let's look at what problems will occur when there are NULL values ​​in the column?

1. Count data is lost

When a column has a NULL value, using count to query the column will result in data "loss", as shown in the following SQL:

select count(*),count(name) from person;

The query execution results are as follows:

From the above results, we can see that when count(name) query is used, two data with NULL values ​​are lost.

Solution

If a column has a NULL value, count(*) is used for data statistics.

Extended knowledge: Don't use count(constant)

Alibaba's "Java Development Manual" stipulates that you should not use count(column name) or count(constant) to replace count( ). count( ) is the standard syntax for counting rows defined by SQL92. It has nothing to do with the database and has nothing to do with NULL or non-NULL.

Note: count(*) will count the rows with NULL values, while count(column name) will not count the rows with NULL values ​​in this column.

2. Distinct data loss

When using count(distinct col1, col2) query, if one of the columns NULL , then even if the other column has a different value, the query result will lose data, as shown in the following SQL:

select count(distinct name,mobile) from person;

The query execution results are as follows:

The original data of the database is as follows:

From the above results, we can see that the 10 data in the mobile phone number column are all different, but the query result is 8.

3.Select data loss

If a column has NULL values, executing a non-equal query (<>/!=) will cause the results of NULL values ​​to be lost. For example, the following data:

I need to query all data except those with name equal to "Java". The expected result is data with id from 2 to 10. However, when executing the following query:

select * from person where name<>'Java' order by id;
-- or select * from person where name!='Java' order by id;

The query results are as follows:

It can be seen that the two NULL data disappeared out of thin air, and this result does not meet our normal expectations.

Solution

To solve the above problem, you only need to add the result with NULL value to the query result. Execute SQL as follows:

select * from person where name<>'Java' or isnull(name) order by id;

The final execution results are as follows:

4. Causes Null Pointer Exception

If a column has a NULL value, the return result of sum(column) may be NULL instead of 0. If the result of sum query is NULL , it may cause a Null Pointer Exception (NPE) during program execution. Let's demonstrate this problem.

First, let's build a table and some test data:

-- If the goods table exists, delete it first DROP TABLE IF EXISTS goods; 
 
-- Create the goods table CREATE TABLE goods (
 id INT PRIMARY KEY auto_increment,
 num int
)ENGINE='innodb';
 
-- Add test data to the goods table insert into goods(num) values(3),(6),(6),(NULL);
 
select * from goods;

The original data in the table are as follows:

Next, we use the sum query and execute the following SQL:

select sum(num) from goods where id>4;

The query execution results are as follows:

When the query result is NULL instead of 0, it may cause a Null Pointer Exception.

Solving Null Pointer Exception

You can avoid NullPointerException by using:

select ifnull(sum(num), 0) from goods where id>4;

The query execution results are as follows:

5. Increased query difficulty

When a column contains NULL values, it becomes more difficult to query NULL values ​​or non- NULL values.

The so-called increased query difficulty means that when performing a NULL value query, you must use NULL value matching query method, such as IS NULL or IS NOT NULL or IFNULL(cloumn) expressions for querying, while traditional =、!=、<>... and other expressions cannot be used. This increases the difficulty of querying, especially for novice programmers. Next, let's demonstrate these problems.

Let's take person table as an example. Its original data is as follows:

Wrong usage 1:

select * from person where name<>null;

The execution result is empty and no data is found, as shown in the following figure:

Wrong usage 2:

select * from person where name!=null;

The execution result is also empty, and no data is queried, as shown in the following figure:

Correct usage 1:

select * from person where name is not null;

The execution results are as follows:

Correct usage 2:

select * from person where !isnull(name);

The execution results are as follows:

Recommended Usage

Alibaba's "Java Development Manual" recommends that we use ISNULL(cloumn) to determine the NULL value . The reason is that in the SQL statement, if there is a line break before null, it affects readability; while ISNULL(column) is a whole, concise and easy to understand. Analyzing from the performance data, ISNULL(column) is also more efficient.

Extended knowledge: NULL does not affect the index

Careful friends may have discovered that when I created name field of person table, I created a normal index for it, as shown in the following figure:

Then we use explain to analyze the query plan to see whether the NULL value in name affects the index selection.

The execution result of explain is shown in the following figure:

From the above results, we can see that even if there is a NULL value in name , it will not affect MySQL's use of indexes for queries.

Summarize

In this article, we discussed five possible problems that may occur when a column is NULL : loss of query results, null pointer exceptions, and increased query difficulty. Therefore, we recommend that you set the is not null constraint when creating a table. If a column does not have a value, you can set a null value ('') or 0 as its default value.

This concludes this article about 5 problems (all fatal) caused by MySQL being Null. For more information about problems caused by MySQL being Null, please search 123WORDPRESS.COM’s previous articles or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!