This article takes you to explore NULL in MySQL

Preface

I don't know if you have ever encountered such a problem. When we query the MySQL database, the condition is status!=1. In theory, all the results that do not meet the condition will be queried out, but the strange thing is that the results with a NULL value cannot be queried out. We must add the condition or status IS NULL. In this article, let's explore NULL in MySQL.

1 NULL in MySQL

For MySQL, NULL is a special value.

NULL means unknown and uncertain. NULL is not equal to any value (including itself)

2 Length occupied by NULL

The length of NULL in the database

mysql> select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
| NULL | 0 | 1 |
+--------------+------------+-------------+

NULL columns require additional space in the row to record whether their values ​​are NULL.

It can be seen that the length of the empty value '' is 0, which does not take up space; while the length of NULL is NULL, which requires additional space. Therefore, in some development specifications, it is recommended to set the database field to Not NULL and set the default value '' or 0.

3 Comparison of NULL values

IS NULL determines whether a character is empty, and does not represent an empty character or 0

The SQL92 standard states that the !=NULL condition always returns false, and aggregate operations always return 0.

Of course, you can use SET ANSI_NULLS OFF in the database to turn off the standard mode, but it is generally not recommended to do so.

Therefore, to determine whether a number is equal to NULL, you can only use IS NULL or IS NOT NULL.

4 SQL handles NULL values

MySQL provides us with the IFNULL(expr1, expr2) function, which allows us to easily handle NULL in the data.

IFNULL takes two parameters. If the first argument field is not NULL, the value of the first field is returned. Otherwise, the IFNULL function returns the value of the second argument (the default value).

select IFNULL(status,0) From t_user;

5. Impact of NULL value on query conditions

• Operators such as =, <, and > cannot be used. The result of arithmetic operations on NULL is NULL (so when status is NULL, status!=1 will not count NULL)
• When using COUNT(expr) statistics, data with NULL values ​​in this field will not be counted.

6 Impact of NULL values ​​on indexes

The first thing to note is that if a column of data in MySQL contains NULL, it does not necessarily cause the index to fail.

MySQL can use indexes on columns containing NULL

Using common indexes, such as normal indexes, composite indexes, and full-text indexes, on fields with NULL values ​​will not invalidate the indexes. However, when using a spatial index, the column must be NOT NULL.

7 Effect of NULL value on sorting

When sorting in ORDER BY, if there is a NULL value, then NULL is the smallest. In ASC positive order, the NULL value is at the front.

If we need to put NULL values ​​at the end when sorting in positive order, we need to use IS NULL.

select * from t_user order by age is null, age; 
Or select * from t_user order by isnull(name), age; 
# Equivalent to select * from (select name, age, (age is null) as isnull from t_user) as foo order by isnull, age; 

8 Difference between NULL and empty value

NULL means storing the NULL value in the field, and an empty value means storing an empty character ('') in the field.

1. Difference in occupied space

mysql> select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
| NULL | 0 | 1 |
+--------------+------------+-------------+
1 row in set

Summary: From the above, we can see that the length of the null value ('') is 0, which does not take up space; while the length of NULL is NULL, which actually takes up space, see the following explanation.

NULL columns require additional space in the row to record whether their values ​​are NULL.

NULL columns require extra space in the row to record whether their value is NULL.

In layman's terms: the empty value is like a vacuum cup with nothing in it, while the NULL value is a cup full of air. Although they look the same, they are essentially different.

Summarize

This is the end of this article about NULL in MySQL. For more information about NULL in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!