The difference between null and NOT NULL in MySQL (reprint)

Believe that a lot of people who have been using MySQL for a long time, the concept of these two field properties is not very clear, the general will have the following questions:

1, my field type is not NULL, why I can insert null value

2, the efficiency of the wool NOT NULL is higher than null

3, when the judgment field is not empty, in the end to select * FROM table where column <> "or to use the SELECT * from table where the column is not NULL.

With some of the above questions, let's delve into the difference between null and NOT NULL.

First, we need to understand the concept of "null" and "NULL":

1, empty value is not space-consuming

2. The null in MySQL is actually occupied space, the following is the explanation from the official MySQL

"NULL columns require additional space in the row to record whether their values is null. For MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte. "

For example, you have a cup, empty value means the cup is vacuum, null means the cup is filled with air, although the cup looks empty, but the difference is very big.

After figuring out the concepts of "null" and "null," the question is basically clear, let's take a look at the example:

[PHP]View Plaincopy

1. CREATE TABLE ' Test ' (
2. ' Col1 ' VARCHAR (Ten) CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL,
3. ' Col2 ' VARCHAR (Ten) CHARACTER SET UTF8 COLLATE utf8_general_ci NULL
4. ) ENGINE = MYISAM;

Insert data:

[PHP]View Plaincopy

1. INSERT into ' Test ' VALUES (null,1);

MySQL error occurred: [PHP]View Plaincopy

1. #1048-column ' col1 ' cannot be null

One more piece.

[PHP]View Plaincopy

1. INSERT into ' Test ' VALUES (', 1);

Successfully inserted.
Visible, NOT NULL field cannot insert "null", can only insert "null value", above question 1 also has the answer.

For question 2, as we have already said, NULL is not a null value, but a space, so when MySQL compares it, NULL participates in field comparisons, so there is a part of the effect on efficiency.

and the B-tree index does not store null values, so if the indexed field can be null, the efficiency of the index will be much lower.

We will then insert several data into the test table:

[PHP]View Plaincopy

1. INSERT into ' Test ' VALUES (', NULL);
2. INSERT into ' Test ' VALUES (' 1 ', ' 2 ');

Now the data in the table:

Now, on demand, I want to count all the data in the test table that are not empty col1, should I use "<>" or "is not NULL", let's look at the difference between the results.

[PHP]View Plaincopy

1. SELECT * from ' test ' WHERE col1 are not NULL

[PHP]View Plaincopy

1. SELECT * from ' test ' WHERE col1 <> '

As you can see, the results are very different, so we have to find out exactly what kind of search criteria to use, based on business needs.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The difference between null and NOT NULL in MySQL (reprint)