The difference between is null and =null in Mysql

In MySQL, when filtering is non-empty, it is often used to be not null and!=null, both of which are literally the same way of feeling, as in fact

It will be a big difference if you try to run it!

Why does this happen?

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

。
If you must want to use! = NULL to judge, this statement needs to be added:

Set ANSI_NULLS off

At this point you will find that is isn't null and! = NULL is equivalent

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

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

So, set to "null" instead of "No value" (by MySQL automatically assigns "null" value), and set to "NOT NULL" is "can no value"

。

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

Insert a null value to see the result.

NULL is neither ' 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 to put it in.

For example, char is using an empty string.
But notice that the empty string is actually a definite value, a string of length 0!

As for the null value, give you a correct understanding: null is understood as UNKNOWN.
The main meaning is ' do not know ', that is, it may be any value;
Another layer means ' information is missing ', such as a field value that stores name information is NULL, which means that the name information is missing.
So the NULL value is not any definite value!

For example, logic and/or arithmetic, right?
With Operation: true and True = True, true and False = False, false and True = False, false and false =

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

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

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

False

The third null and NULL, just understand, it is completely air-to-air, two operands are not known, the result is not known, so

Is null.

The difference between is null and =null in Mysql