“This is the 8th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

MySQL > select * from ‘status’ where status =’ status’; Select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from))); In this article we will explore NULL in MySQL.

1 NULL in MySQL

For MySQL, NULL is a special value.

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

2 Length occupied by NULL

The length that NULL occupies in the database

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

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

It can be seen that the length of the null value ” is 0, which does not take up space; 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 NULL. It does not represent a NULL character or a 0

The SQL92 standard says,! Of course you can use SET ANSI_NULLS OFF to turn OFF standard mode in a database, but it is generally not recommended to do soCopy the code

Therefore, the only way to determine whether a number IS equal to NULL IS NULL or IS NOT NULL

4 SQL Processes NULL values

MySQL provides IFNULL(expr1,expr2) to handle NULL data easily

IFNULL takes two arguments. If the first parameter 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).

select IFNULL(status,0) From t_user;
Copy the code

5 Impact of NULL on query conditions

  • Do not use operators such as =,<,>. All operations on NULL will result in NULL. =1 will not count NULL)
  • If COUNT(expR) is used to collect statistics, the data whose field is NULL will not be counted

6 Impact of NULL on indexes

The first thing to note is that a column containing NULL in MySQL does not necessarily invalidate an index.

MySQL can use indexes on columns that contain NULL

Using common indexes on null-worthy fields, such as normal indexes, composite indexes, full-text indexes, and so on, does not invalidate an index. However, in the case of spatial indexes, the column must be NOT NULL.

7 Effect of NULL on sorting

In ORDER BY, if NULL is present, then NULL is the smallest, ASC positive ordering, NULL is the first

If we want to put NULL after positive sorting, we need to borrow IS NULL

select * from t_user order by age is null, age; Select * from t_user order by isnull(name), age; # equivalent to select * from (select name, age, (age isnull) as ISnull from t_user) as foo order by isnull, age;Copy the code