Tutorial on understanding and using NULL values in MySQL

The concept of NULL values is a common cause of confusion among SQL novices, who often think of NULL as something like 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 ("");

The two statements insert the value into the phone column, but the first inserts an NULL value and the second inserts an empty string. The first can mean "no phone number," while the second can mean "she doesn't have a phone."

In SQL, the NULL value is always false when compared with any other value, even the NULL value (FALSE). 1 expression containing NULL always yields 1 NULL value unless indicated in the documentation 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 value of NULL, you cannot use the =NULL test. The following statement does not return any lines because expr = NULL is false for any expression:

mysql> SELECT * FROM my_table WHERE phone = NULL;

To find the NULL value, you must use the IS NULL test. The following example shows how to find the 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 1, you cannot index columns that can have NULL values. You must declare such a column as NOT NULL, and you cannot insert NULL into the indexed column.

When reading data with LOAD DATA INFILE, empty columns are updated with "". If you want to have an NULL value in a column, you should use \N in a text file. The literal word 'NULL' can also be used in certain situations.

When ORDER BY is used, the NULL value is first rendered. If you use DESC in descending order, the NULL value is displayed last. When using GROUP BY, 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.

The IFNULL() function of MySQL functions similarly to the NVL() function of Oracle. Here are some simple examples:
IFNULL(expr1, expr2)
If expr1 is not NULL, IFNULL() returns expr1, otherwise it returns expr2. IFNULL() returns 1 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); 
    -> 10 
mysql> select IFNULL(1/0,'yes'); 
    -> 'yes' 
NVL( string1, replace_with)

What it does: If string1 is NULL, the NVL function returns the value of replace_with, or string1.
By extension 1, this NVL functions the same as ISNULL(string1, replace_with) in SQLserver.
Note: string1 and replace_with must be of the same data type unless you explicitly use the TO_CHAR function.
Ex. :

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

Where numeric_column represents the value of a numeric type.
Ex. :

nvl(yanlei777,0) > 0

NVL(yanlei777, 0) means that if yanlei777 is NULL, the value is 0

Discrimination between null and null value
Take a look at this piece of code:

CREATE TABLE `test` (
`col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
) ENGINE = MYISAM ;

Error (null value cannot 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');

Search (' not null, so not null will count ') :

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

0

Results: All 3 pieces of data

Search (normal search) :

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

1

Result: The last one

Retrieval ( < > ", will exclude both "and null data, and only retrieve the data with content) :

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

2

Conclusion:
1. null is not stored as a '', but as a special character.
2. null in search < > ", will also be excluded, because there is no really meaningful content
not null is not to be excluded, strictly speaking.
4. Define not null field, insert ""