Mysql null value and mysqlnull

Mysql null value and mysqlnull

NULL is a type of "no type" value. It usually indicates "no value", "unknown value", "missing value", "Super border", and "not in it, in our daily use, it is easy to confuse NULL strings. Here we have roughly sorted out some features of the NULL value so that we can correctly use the NULL value.

1. If the default value is not added during table creation, the default value is NULL.

 

2. Differences between NULL and NULL strings

1) the NULL value is case-insensitive. It is displayed as uppercase NULL during query, while the NULL String is displayed as the insert case. During query, It is filtered based on whether the character set is case-sensitive, alternatively, you can use statement-level collate or binary to specify whether the data is case sensitive.

2) the NULL string can be processed as a normal string, while the NULL values are determined only by is null and is not null. See 5th.

3. If no value is specified during data insertion, if no other default value exists, the default value is NULL.

 

4. When an uppercase NULL character is inserted, it is difficult to distinguish whether NULL is a NULL value or a NULL String. In this case, the NULL value must be used for determination, NULL values are determined only by is NULL and is not NULL.

 

It is difficult to distinguish with the naked eye. Here, we can see that id2 has only three rows of NULL values, 7 rows of non-NULL values, and 4 rows of NULL strings.

 

5. The processing functions for NULL values in SQL include isnull (expr)(Return the logic judgment result of is NULL)And ifnull (expr1, expr2)(If expr1 is null, expr2 is returned; otherwise, expr1 is returned)

 

6. For comparison and arithmetic operations with NULL values, the result is still NULL. However, when the <=> operator is used, NULL <=> NULL is true.

 

7. logical operation with the NULL value. The is NULL operation only returns the number 1 If null is null, And the is not NULL operation only returns the false number 0 if null is not null.

 

8. mysql treats \ N (case sensitive) as NULL values.

 

9. The NULL value is different from the NULL character ''. When the query field is NULL, an equal sign can be used. If the length is not considered, all fields are displayed as NULL, but the length is different.

 

 

10. When NULL and other characters are used for arithmetic operations, they are treated as 0, but the calculation result is NULL when used as the denominator.

 

11. The NULL value is filtered by the count (field) function, but not in count (*).

 

12. In the group by function, NULL is placed before the minimum value as the same value.

 

13. If any other function has a NULL value, the returned result is NULL, for example: