Introduction to NOT NULL and!=null and NULL in MySQL

In MySQL, when you filter non-empty, you often use are not null and!=null, and these two methods are literally similar to each other, actually like

It will be very different if you try to run it.

Why is this happening?

Null means nothing, no =, >, < ... All judgments are false, and all can only be judged with IS null.
By default, it is recommended to use is not NULL to judge because SQL defaults to! = NULL judgment will always return 0 rows, but no syntax errors

。
If you must want to use! = NULL to judge, you need to add this statement:

Set ANSI_NULLS off

At this point you will find that not NULL and!= null are equivalent

A field that is set to "null" means that if the value of this field is empty, a "null" value is inserted automatically.

A field that is set to NOT NULL means that if the value of this field is empty, the null value is not automatically inserted (no value).

So, set to "null" means "cannot be no value" (Automatically assigned by MySQL "null" value), and set to "not NULL" is "can be no value"

。

In fact, to prove this is very simple, to build a test table, two fields (VC type), one set to "null", one set to "not NULL", two are

Insert a null value and see the result.

NULL is not ' nor ' 0.

Your field is defined as NOT NULL, but it is assigned a null, then the database system chooses a default value for that field type.

For example, char is an empty string.
Note, however, that the empty string is already a definite value, a string of length 0!

As for the null value, give you a correct understanding: to interpret null as UNKNOWN.
The main meaning is ' don't know ', that is, it may be any value;
Another layer of meaning is ' lack of information ', for example, a field value that stores name information is NULL and represents a missing name information.
So the NULL value is not any definite value!

For example, is the logic and/or operation of the bar?
With arithmetic: true and True = True, true and False = False, false and True = False, false and false =

False
The first true and NULL, and its results are determined entirely by null. If it is true, the result is true, and if it is false, the result is

False Because the null representative does not know, so the result is not known, so is null.

The second false and NULL, whose result does not need to be determined by NULL, because the attribute of the and operation has false false, so the result is

False

The third null and NULL, it is very understandable, it is completely air-to-air, two operands are not know, the result is naturally not know, so

Is null.