MySQL's understanding and tutorial on NULL value, mysqlnull

MySQL's understanding and tutorial on NULL value, mysqlnull

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('Invisible',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 = "";

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 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.

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.

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,10);     -> 0 mysql> select IFNULL(1/0,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.
Example:

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

Numeric_column indicates a numeric value.
Example:

nvl(yanlei777,0) > 0

NVL (yanlei777, 0) indicates that if yanlei777 is NULL, the value 0 is used.

Analysis of null and null values
Let's look at the following 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 values cannot be inserted ):

INSERT INTO `test` VALUES (null,1);

Correct (insert ''is correct ):

INSERT INTO `test` VALUES ('',1);INSERT INTO `test` VALUES ('', NULL);INSERT INTO `test` VALUES ('1', '2');

Search (''is not null, so not null will calculate ):

SELECT * FROM `test` WHERE col1 IS NOT NULL

Result: All three data items are returned.

Search (normal search ):

SELECT * FROM `test` WHERE col1 <> ''

Result: the last row of data.

Search (<> '', both'' and null data are excluded, and only data with content is retrieved ):

SELECT * FROM `test` WHERE col2<>''

Summary:
1. null is not stored as '', which is represented by other special characters.
2. null will also be excluded when retrieving <> '', because there is no truly meaningful content.
3. not null strictly follows the literal meaning, ''will not be excluded.
4. Define a field not null. you can insert''