Several issues related to NULL values in MySQL Databases

Several issues related to NULL values in MySQL Databases

For SQL beginners, the concept of NULL value is often confusing. They often think that NULL is the same as the NULL String. This is not the case. For example, the following statements are completely different:

Mysql> insert into my_table (phone) VALUES (NULL );

Mysql> insert into my_table (phone) VALUES ('');

Both statements insert values into the phone column, but 1st statements insert NULL values and 2nd statements insert NULL strings. The meaning of 1st cases can be interpreted as "the phone number is unknown", and the meaning of 2nd cases can be interpreted as "the person has no phone number, so there is no phone number ".

To perform NULL processing, the is null and is not null operators and IFNULL () functions can be used.

In SQL, comparing NULL values with any other values (even NULL) is never "true ". Expressions that contain NULL always export NULL values, unless otherwise specified in the document on operators and functions of expressions. In the following example, all columns return NULL:

Mysql> select null, 1 + NULL, CONCAT ('invisable', NULL );

If you want to search for a column with a NULL value, you cannot use expr = NULL for testing. The following statement does not return any rows because expr = NULL is never "true" for any expression ":

Mysql> SELECT * FROM my_table WHERE phone = NULL;

To search for a NULL value, the is null test must be used. The following statement describes 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 = '';

For more information and examples:

If you are using the MyISAM, InnoDB, BDB, or MEMORY storage engine, you can add one index to a column that may have NULL values. Otherwise, the index column must be declared as not null and NULL cannot be inserted into the column.

When reading DATA using load data infile, null or lost columns are updated. If you want to have a NULL value in the column, use \ N in the data file. In some cases, you can also use the literal word "NULL ".

When DISTINCT, group by, or order by is used, all NULL values are considered equal.

When order by is used, the NULL value is first displayed. If DESC is specified in descending ORDER, the NULL value is finally displayed.

For aggregate (accumulative) functions, such as COUNT (), MIN (), and SUM (), NULL values are ignored. The exception is COUNT (*), which counts rows instead of individual column values. For example, the following statement generates two counts. First, count the number of rows in the table, and then count the number of non-NULL values in the age column:

Mysql> select count (*), COUNT (age) FROM person;

For some column types, MySQL will perform special processing on the NULL value. If NULL is inserted into the TIMESTAMP column, the current date and time are inserted. If NULL is inserted into an integer column with the AUTO_INCREMENT attribute, the next number in the sequence is inserted.