Detailed explanation of NULL and NOT NULL in MySQL

What is this short article about?

I saw someone ask about the issue of nullable and index in PHPHub migration database file before. I believe that many people who have used MySQL for a long time (especially People who usually pay too much attention to business development) are not very clear about the concepts of these two field attributes. They usually have the following questions:

My field type is not null, why can I insert null values;

Not null is more efficient than null;

When judging that a field is not empty, should we use column '' or should we use column is not null.

With the above questions, let’s take a closer look at the difference between null and not null.

Is null the same as a null value?

First of all, we need to understand the concepts of null value and null:

Null value does not take up space ;

null in MySQL actually takes up space. The following is the official explanation from MYSQL:

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

For example, you have a cup. The null value means that the cup is vacuum, and the NULL value means that the cup is vacuum. The cup is filled with air. Although the cup looks empty, the difference is huge.

A little chestnut

After understanding the concepts of "null value" and "NULL", the problem is basically clear. Let's test it with an example:

CREATE TABLE  `test` (
     `col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
     `col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
 ) ENGINE = MYISAM ;

Question 1: My field type is not null, why can I insert null values?

Execute the following SQL, and an error occurs, indicating that Column 'col1' cannot be null.

INSERT INTO `test` VALUES ( null, 1);

One more message, executed successfully.

INSERT INTO `test` VALUES ('',1);

It can be seen that NULL cannot be inserted into NOT NULL fields (isn’t this nonsense?), only null values ​​can be inserted, and the answer to question 1 above is there.

Question 2: Why is not null more efficient than null?

Regarding question 2, as we have said above, NULL is not actually a null value, but takes up space. Therefore, when mysql performs comparison, NULL will participate in field comparison, so it will partially affect the efficiency. .

And B-tree indexes will not store NULL values, so if the indexed fields can be NULL, the index efficiency will drop a lot.

Question 3: When judging that a field is not empty, should we use column<>'' or should we use column is not null.

Let's insert a few pieces of data into the test table:

INSERT INTO `test` VALUES ('', NULL);
INSERT INTO `test` VALUES ('1', '2');

Now according to the requirements, I want to count all the data in the test table where col1 is not empty. I should use <> ;'' or IS NOT NULL, let's take a look at the difference in results.

The data in the table is now as follows:

Compare the execution effect of the following two SQL sentences respectively

SELECT * FROM `test` WHERE col1 IS NOT NULL

SELECT * FROM `test` WHERE col1 <> &#39;&#39;

As you can see, the results are very different, so we must figure out what kind of search conditions to use and whether to use null based on business needs.

A small pitfall I encountered

When I first joined the company a long time ago and made the first requirement online, I only noticed that the efficiency ratio was not null. null is efficient.

Okay~ When I added fields to the existing table, I set them all to not null, and I felt stupid.

Because many Services have insert actions to operate this table, the result is as you can imagine. Just after it went online, the error Column 'col1' cannot be null filled the mailboxes of everyone in the entire development team.

So, when the business volume is not very large, the use of many technologies actually needs to be comprehensively considered based on the actual situation.

The above is the detailed content of Detailed explanation of NULL and NOT NULL in MySQL. For more information, please follow other related articles on the PHP Chinese website!