In MySQL, null and empty string null differences in the detailed

For beginners of SQL, the concept of null values often creates confusion, and they often assume that null is the same thing as a MySQL empty string. This is not the case. For example, the following statement is completely different:mysql> INSERT into my_table (phone) VALUES (NULL);

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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

This is equivalent to the factory to the staff of the House, a total of only a set, Lao Zhang and Old Lee want, but the manager told Lao Zhang, the next time you have your house, but Lao Li has no one to speak to him. So Lao Zhang's house is "empty" because it is a blank cheque, but after all the manager has opened his mouth, and Lao Li's house is null, because no one has considered him.
The difference between the two is small, but if not carefully, it may limit us to the dilemma. Because null values cannot be used in the primary key field. We should remember that.