Some problems related to null value in MySQL database

For beginners of SQL, the concept of null values often creates confusion, and they often assume that null is the same thing as an empty 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 (');

Each of these two statements inserts a value into the phone column, but the 1th statement inserts a null value, and the 2nd statement inserts an empty string. The meaning of the 1th situation could be interpreted as "unknown telephone number", and the meaning of the 2nd situation could be interpreted as "the person has no telephone and therefore no telephone number".

For null processing, you can use the is null and are not NULL operators, and the Ifnull () function.

In SQL, a comparison of a null value with any other value (even if it is null) will never be true. An expression containing NULL always exports null values, unless otherwise specified in the document on the operator and in the function of the expression. All columns in the following example return null:

Mysql> SELECT NULL, 1+null, CONCAT (' invisible ', NULL);

If you intend to search for columns with null columns, you cannot use expr = null test. The following statement does not return any rows because, for any expression, expr = null is never true:

Mysql> SELECT * from my_table WHERE phone = NULL;

To find null values, you must use the IS NULL test. In the following statement, the way to find null phone numbers and empty phone numbers is described:

Mysql> SELECT * FROM my_table WHERE phone is NULL;

Mysql> SELECT * from my_table WHERE phone = ';

More information and examples:

If you are using the MyISAM, InnoDB, BDB, or memory storage engine, you can add 1 indexes to a column that might have a null value. Otherwise, you must declare the index as NOT null and you cannot insert NULL into the column.

When you read data with the load data infile, you will update them for empty or missing columns. If you want to have a null value in the column, you should use N in the data file. In some cases, the literal word "NULL" can also be used.

When you use DISTINCT, GROUP by, or order BY, all null values are considered equivalent.

When you use ORDER BY, a null value is first displayed, and if you specify DESC in descending order, the null value is displayed last.

for aggregate (cumulative) functions, such as count (), MIN (), and sum (), null values are ignored. The exception to this is count (*), which counts rows instead of individual column values. For example, the following statement produces 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 have special handling for null values. If you insert null into the timestamp column, the current date and time are inserted. If NULL is inserted into an integer column with the Auto_increment property, the next number in the sequence is inserted.