Ten NULL values of MySQ study notes _ MySQL

Processing the ten NULL values of MySQ learning notes is a special feature of MySQL.

In concept, NULL means "no value" or "unknown value", and it is regarded as a slightly different value. To test NULL, you cannot use arithmetic comparison operators such as =, <或!=。为了说明它，试试下列查询：
Mysql> SELECT 1 = NULL, 1! = NULL, 1 <NULL, 1> NULL;
+ ---------- + ----------- + ---------- +
| 1 = NULL | 1! = NULL | 1 <NULL | 1> NULL |
+ ---------- + ----------- + ---------- +
| NULL |
+ ---------- + ----------- + ---------- +
It is clear that you get meaningless results from these comparisons. Instead, use the is null and is not null operators:
Mysql> SELECT 1 is null, 1 is not null;
+ ----------- + --------------- +
| 1 is null | 1 is not null |
+ ----------- + --------------- +
| 0 | 1 |
+ ----------- + --------------- +
In MySQL, 0 means false and 1 means true.
The concept of NULL value is a common cause of confusion among SQL beginners. They often think that NULL is the same as an empty string. No! For example, the following statements are completely different:
Mysql> insert into my_table (phone) VALUES (NULL );
Mysql> insert into my_table (phone) VALUES ("");
The two statements insert values into the phone column, but the first statement inserts a NULL value and the second statement inserts an empty string. The first one can be thought of as "the phone number is unknown", while the second one can mean "she has no phone number ".
In SQL, the NULL value is always FALSE when any other value or even NULL value is compared ). An expression that contains NULL always generates a NULL value, unless it is specified in the document containing operators and functions in the expression. In the following example, all columns return NULL:
Mysql> select null, 1 + NULL, CONCAT ('invisable', NULL );
+ ------ + -------- + -------------------------- +
| NULL | 1 + NULL | CONCAT ('invisable', NULL) |
+ ------ + -------- + -------------------------- +
| NULL |
+ ------ + -------- + -------------------------- +
If you want to find a column whose value is NULL, you cannot use = NULL for testing. The following statement does not return any rows because expr = NULL is false for any expression:
Mysql> SELECT * FROM my_table WHERE phone = NULL;
To search for a NULL value, you must use the is null test. The following example shows how to find a NULL phone number and a blank phone number:
Mysql> SELECT * FROM my_table WHERE phone is null;
Mysql> SELECT * FROM my_table WHERE phone = "";

TIPS:
In MySQL, just like many other SQL servers, you cannot index columns with NULL values. You must declare that such a column is not null, and you cannot insert NULL to the index column.
When order by is used, the NULL value is first displayed. If you use DESC to sort data in descending order, the NULL value is finally displayed. When group by is used, all NULL values are considered equal.
To help with NULL processing, you can use the is null and is not null operators and IFNULL () functions.
For certain column types, NULL values are specially processed. If you insert NULL into the first TIMESTAMP column of the table, the current date and time are inserted. If you insert NULL into an AUTO_INCREMENT column, insert the next number in the sequence.