Database Query-is null and = null, isnull
In mysql, is not null and! are often used when Filtering non-null values! = Null. The two methods are literally similar, as shown in
If you try it, it will be very different!
Why is this happening?
Null indicates nothing, cannot =,>, <... All judgments are false, and all judgments can only be made using is null.
By default, is not null is recommended for determination, because SQL IS correct by default! = 0 rows are always returned for Null judgment, but there is no syntax error
.
If you want to use it! = Null to determine, you need to add this statement:
Set ANSI_NULLS off
At this time, you will find that is not null and! = Null is equivalent
If it is set to "NULL", it indicates that if the value of this field is NULL, a "NULL" value is automatically inserted.
If a field is set to "not null", it means that if the value of this field is NULL, the "NULL" value is NOT automatically inserted (without any value ).
Therefore, if it is set to "NULL", it means "cannot be NULL" (MYSQL automatically assigns a "NULL" value). If it is set to "not null", it means "can be NULL"
.
In fact, it is easy to prove this. Create a test table with two fields (VC type), one set as "NULL", the other set as "not null", and both
Insert a null value and check the result.
NULL is neither ''nor 0.
If your field is defined as not null but is assigned a null value, the database system selects a default value based on the field type,
For example, char is an empty string.
However, note that a null string is actually a fixed value, that is, a string with a length of 0!
The NULL value gives you a correct understanding: To understand NULL as UNKNOWN.
The main meaning is "unknown", that is, it may be any value;
The other layer indicates missing information. For example, if the field value of a stored name is NULL, it indicates that the name information is missing.
Therefore, the NULL value is not any definite value!
For example, can logic and/or operation be performed?
Operation: true and true = true, true and false = false, false and true = false, false and false =
False
The first is true and null, and its result is determined completely by null. If it is true, the result is true. If it is false, the result is
False. Null indicates that you do not know the result, so it is null.
The second is false and null. The result does not need to be determined by null. Because of the features of the and operation, false is returned, so the result is
False.
The third is null and null. You can understand it. It is completely empty or empty. Both operands do not know, and the result is naturally unknown.
Is null.
The following is an example:
SQL> select * from test;
ID NAME EMIL
----------------------------------------
1 hj 124rd
2 fg
SQL>
In the test table, emil does not fill in any data query:
Select name from test where emil = null;
SQL> select name from test where emil = null;
Unselected row
Select name from test where emil is null;
NAME
----------
Fg
SQL>
Display a result.
By default, is null is recommended for condition determination, because SQL returns 0 rows for where xx = null by default without syntax errors.