A tutorial on understanding and using null values in Mysql _mysql

The concept of null values is a common cause of confusion among novice sql, who often assume that null is the same thing as an empty string. That's not true! For example, the following statements are completely different:

Mysql> INSERT into my_table (phone) VALUES (NULL); 
Mysql> INSERT into my_table (phone) VALUES ("");

Two statements insert values into the phone column, but the first inserts a null value and the second inserts an empty string. The first meaning can be thought of as "the telephone number does not know", but the second can mean "she does not have a telephone".

In SQL, a null value is always False (false) when any other value or even a null value is compared. An expression containing null always produces a null value, unless it is indicated in the document of the operators and functions contained in the expression. In the following example, all columns return null:

mysql> SELECT null,1+null,concat (' invisible ', NULL);

If you want to find a column with a null value, you cannot use the =null test. 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 find null values, you must use the IS NULL test. The following example shows how to find a null phone number and an empty phone number:

Mysql> SELECT * FROM my_table WHERE phone is NULL; 
Mysql> SELECT * from my_table WHERE phone = "";

In MySQL, like many other SQL servers, you can't index columns that can have null values. You must declare that such column is not NULL, and that you cannot insert null into the indexed columns.

When reading data with the load infile, empty columns are "updated." If you want to have null values in a column, you should use \ n in a text file. The literal word ' NULL ' can also be used in certain situations.

When an order by is used, the null value is first rendered. If you sort in descending order with DESC, 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 are not NULL operators and the Ifnull () function.

The Ifnull () function of MySQL is similar to the NVL () function of Oracle. Here are some simple examples:
Ifnull (EXPR1, EXPR2)
If EXPR1 is not Null,ifnull () returns EXPR1, it returns EXPR2. Ifnull () returns a numeric or string value, depending on the context in which it is used.

Mysql> Select Ifnull (1,0); 
    -> 1 
mysql> Select Ifnull (0,10); 
    -> 0 
mysql> Select ifnull (1/0,10); 
    -> 
mysql> Select ifnull (1/0, ' yes '); 
    -> ' yes ' 
NVL (string1, Replace_with)

Function: If string1 is null, the NVL function returns the Replace_with value, otherwise the string1 value is returned.
By extension, this NVL function is the same as ISNULL (string1, replace_with) in SQL Server.
Note: string1 and replace_with must be of the same data type unless the TO_CHAR function is explicitly used.
Cases:

NVL (To_char (numeric_column), ' some string ')

Where Numeric_column refers to the value of a numeric type.
Cases:

NVL (yanlei777,0) > 0

NVL (yanlei777, 0) means that if yanlei777 is null, a value of 0 is taken

Analysis of null and void values
take a look at this piece of code:

CREATE TABLE ' test ' (
' col1 ' VARCHAR () CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL,
' col2 ' VARCHAR (1 0) CHARACTER SET UTF8 COLLATE utf8_general_ci NULL
) ENGINE = MYISAM;

Error (no null value can be inserted):

INSERT into ' Test ' VALUES (null,1);

Correct (insert ' no problem '):

INSERT into ' Test ' VALUES (', 1);
INSERT into ' Test ' VALUES (', NULL ');
INSERT into ' Test ' VALUES (' 1 ', ' 2 ');

Retrieves (' is not NULL, so not NULL ' counts ' both:

SELECT * from ' test ' WHERE col1 are not NULL

Result: All three data

Retrieving (normal search):

SELECT * from ' test ' WHERE col1 <> '

Result: The last piece of data

Retrieves (<> ', excludes both "and null data, retrieving only data with content):

SELECT * from ' test ' WHERE col2<> '

Summarize:
1. Null storage is not "," is another special character representation.
2. Null is also excluded when retrieving <> ' because there is no real meaningful content
3. Not NULL is strictly literal, ' will not be excluded.
4. A field that defines not null and can be inserted into the '