Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL null will cause what problems", in daily operation, I believe many people in MySQL Null will cause what problems there are doubts, Xiaobian consulted all kinds of data, sorted out simple and easy to use operation methods, I hope to answer "MySQL Null will cause what problems" doubts helpful! Next, please follow the small series to learn together!

Before we get started, let's take a look at the MySQL server configuration and version information, as shown in the following figure:

After reading the relevant configurations, let's first create a test table and some test data.

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

The constructed test data is shown in the following figure:

With the data in hand, let's look at what happens when there is a NULL value in the column.

1. count Data loss

When a column has a NULL value, and then use count to query the column, there will be data "missing" problem, as shown in the following SQL:

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

The query execution results are as follows:

As you can see from the above results, when you use the count(name) query, you lose two NULL data losses.

solutions

If there is a NULL value in a column, count(*) is used for statistics.

Extended knowledge: Do not use count(constant)

Alibaba Java Development Manual mandatory: do not use count(column name) or count(constant) instead of count(), count() is the SQL92 definition of the standard statistical row syntax, has nothing to do with the database, has nothing to do with NULL and non-NULL.

Note: count(*) counts rows with NULL values, while count(column name) does not count rows with NULL values.

2. distinct data loss

When using count(distinct col1, col2) query, if one column is NULL, then the query results in data loss even if the other column has a different value, as shown in SQL:

select count(distinct name,mobile) from person;

The query execution results are as follows:

The raw data of the database are as follows:

From the above results, it can be seen that the 10 pieces of data in the mobile phone number column are different, but the query result is 8.

3.select Data Loss

If a column has a NULL value, if a non-equal query is executed (/!=) Results with NULL values are lost. For example, the following data:

I need to query for all data except name equal to "Java," expecting to return data with id from 2 to 10, but 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 into thin air, which is not in line with our normal expectations.

solutions

To solve the above problem, you only need to spell the result of NULL value in the query result, and execute SQL as follows:

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

The final implementation results are as follows:

4. Causes null pointer exception

If a column has a NULL value, it may cause sum(column) to return NULL instead of 0. If the result of sum query is NULL, it may cause the program to execute a spatiotemporal pointer exception (NPE). Let's demonstrate this problem.

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

--If there is a goods table, delete DROP TABLE IF EXISTS goods; --Create goods table CREATE TABLE goods ( id INT PRIMARY KEY auto_increase, num int ) ENGINE ='innodb '; -- goods table insert test data into goods(num) values(3),(6),(6),(NULL); select * from goods;

The raw data in the table are as follows:

Next we use the sum query to execute the following SQL:

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

The query execution results are as follows:

A null pointer exception can be caused when the query results in NULL instead of 0.

Resolving null pointer exceptions

Null-pointer exceptions can be avoided in the following ways:

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

The query execution results are as follows:

5. Increased query difficulty

When a column has NULL values, it is more difficult to query NULL values or non-NULL values.

The so-called query difficulty increase refers to that when performing a NULL value query, a query method matching NULL values must be used, such as IS NULL or IS NOT NULL or an expression such as IFNULL(cloumn), while traditional =,!=,... When these expressions are no longer usable, it makes queries more difficult, especially for small programmers. Let's demonstrate these problems.

Still using the person table as an example, its raw data is as follows:

Misuse 1:

select * from person where namenull;

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

Misuse 2:

select * from person where name!= null;

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

Correct Usage 1:

select * from person where name is not null;

The results are as follows:

Correct Usage 2:

select * from person where ! isnull(name);

The results are as follows:

recommended usage

Alibaba Java Development Manual recommends that we use ISNULL(cloumn) to determine NULL values, because in SQL statements, if the line breaks before null, it affects readability; and ISNULL(column) is a whole, simple and easy to understand. ISNULL(column) execution efficiency is also faster to analyze from performance data.

Extended knowledge: NULL does not affect indexes

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

We then use explain to analyze the query plan to see if NULL in name affects the index selection.

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

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

At this point, the study of "MySQL null will cause what problems" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.