MySQL determines null

From: http://lz12366.iteye.com/blog/673290

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 (""); two statements insert values into the phone column, but the first one inserts a null value and the second one 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); if you want to find a column whose value is null, 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 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 = ""; in MySQL, 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 you use load data infile to read data, use ''to update the empty column. If you want to include a null value in a column, you should use \ n in a text file. The literal word 'null' can also be used in some cases. See the 7.16 load data infile syntax.

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.

Bytes ---------------------------------------------------------------------------------------------------------------

The ifnull () function of MySQL is similar to the nvl () function of oracle.The following is a simple example.:

Ifnull (expr1, expr2)
If expr1 is not null, ifnull () returns expr1; otherwise, it returns expr2. Ifnull () returns a number or string value, depending on the context in which it is used.

Mysql> select ifnull (1/0);-> 1 mysql> select ifnull ();-> 0 mysql> select ifnull (, 10 ); -> 10 mysql> select ifnull (1/0, 'yes');-> 'yes'
 

Nvl (string1, replace_with)

Function: If string1 is null, The nvl function returns the value of replace_with; otherwise, the value of string1 is returned.
This nvl serves the same purpose as isnull (string1, replace_with) in sqlserver.
Note: string1 and replace_with must be of the same data type unless the to_char function is explicitly used.
For example, nvl (to_char (numeric_column), 'some string') Where numeric_column represents a numerical value.
For example, nvl (yanlei777, 0)> 0
Nvl (yanlei777, 0) indicates that if yanlei777 is null, the value 0 is used.