The difference between is null and =null in Mysql

Source: Internet
Author: User

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

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.