MySQL null value processing detailed description

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.