before we speak NULL, let's look at an exampleThe table data is as follows:3306>select * from T1;+------+-------+| id | name |+------+-------+| 1 | chen || 2 | zhang || 3 | NULL |+------+-------+3 rows in Set (0.00 sec)then we want to find out all the other lines whose names are not ' Chen ', which we might write:3306>select * from t1 where name!= ' Chen ';+------+-------+| id | name |+------+-------+| 2 | zhang |+------+-------+1 row in Set (0.00 sec)But the result seems not as we would like, the ID 1 of the record is not detected, null and ' Chen ' is not equal ah, why can not find it? null values are very confusing for beginners and are often considered null as the empty string ' ', but in fact, the two are very different, and null means "a missing unknown value", while the string ' is a definite value, which is inherently different. As an example:mysql> INSERT INTO t_user values (NULL), mysql> insert INTO T_user (");The first INSERT statement says that the phone number is not sure what it is, a missing unknown phone numbersThe second one means that the cell phone number is OK, and its cell phone number is an empty string In order to deal with null-related comparisons, it is possible to test comparisons in MySQL with is null, are NOT NULL, <=>, IsNull (), and cannot be compared with tests such as =, <, <>, and null values. Any value (including and null itself) is NULL compared to null and the function operation result is both empty Here are three examples to illustrate the problem:any related operation with NULL is NULL3306>select NULL, 1+null, CONCAT (' Invisible ', null);+------+--------+--------------------------+| NULL | 1+null | CONCAT (' Invisible ', NULL) |+------+--------+--------------------------+| NULL | NULL | NULL |+------+--------+--------------------------+1 row in Set (0.00 sec)3306>select 1>null, 1=null, 1!=null;+--------+--------+---------+| 1>null | 1=null | 1!=null |+--------+--------+---------+| NULL | NULL | NULL |+--------+--------+---------+1 row in Set (0.00 sec)use the specified function action to return 0 or 13306 > SELECT 1 is null, 1 are NOT NULL, 1<=>null,null<=>null;+-----------+---------------+----------+-------------+| 1 is NULL | 1 are NOT NULL | 1<=>null | Null<=>null |+-----------+---------------+----------+-------------+| 0 | 1 | 0 | 1 |+-----------+---------------+----------+-------------+1 row in Set (0.00 sec)But there is an exception to everything:when using the distinct, group by, and order by, the different null values are considered equal calculations at this time. Most importantly, in MySQL, 0 and null represent false, and any other value represents the trueso looking back at the first example, when our where condition is name!= ' Chen ', it is compared to the line of id=1, that is, testing:' chen '! = ' Chen 'This test result is 0, is false, so id=1 this line will not be detected. Next Test:' zhang '! = ' Chen 'This test result is 1, is true, so id=2 this line can be detected. Next Test:null!= ' Chen 'This test result is null, FALSE, so the id=3 line will not be detected. so the end result is only id=2 this line of data is detected. What if we want to reach our goal? 3306>select * from t1 where name!= ' Chen ' or name is null;+------+-------+| id | name |+------+-------+| 2 | zhang || 3 | NULL |+------+-------+2 rows in Set (0.01 sec)or3306>select * from T1 where ifnull (name,0)! = ' Chen ';+------+-------+| id | name |+------+-------+| 2 | zhang || 3 | NULL |+------+-------+2 rows in Set (0.00 sec)The first method is recommended. because MySQL's lookup of NULL columns is query-optimized:3306>explain SELECT * from Test a where a.code= ' aaaaaa ' or A.code is null\g*************************** 1. Row ***************************id:1Select_type:simpletable:atype: ref_or_null Possible_keys:codeKey:codekey_len:21Ref:constrows:73603Extra: Using index condition 1 row in Set (0.01 sec)The second query that adds a function to the column does not work. Other precautions1. When you use ORDER by ... ASC, the null value is placed first, and when you use ORDER BY ... desc, NULL is placed on the last side, equivalent to null being an infinitesimal value. 2, aggregate functions such as count (), min (), sum () are ignored for null values. The only exception is with COUNT (*), such asSELECT Count (*), count (age) by person;The above statement is the first count (*) to calculate the total number of rows for the person table, and the second is to calculate the number of non-null lines for the person table 's age column3. In the MyISAM and InnoDB memory engines, it is possible to add an index to a column that contains null values, while in other engines it must be declared as NOT NULL to add an index4, when using the load data infile, empty or no column will be replaced with a null character, if you want to import null value, you need to use \ n in the data file to represent. 5. If you insert a null value into timestamp, the current time will be inserted, and if a self-increment column inserts a null value, the next self-increment will be inserted.
MySQL null value processing detailed description
