Detailed explanation of the difference between MySQL null and not null and null and empty value ''''

I believe that many people who have used MySQL for a long time are still not very clear about the concepts of these two field attributes, and generally have the following questions:

• My field type is not null, why can I insert a null value?
• Why is not null more efficient than null?
• When judging whether a field is not empty, should we use select * from table where column <> '' or select * from table where column is not null?

With the above questions in mind, let's take a closer look at the differences between null and not null.
First, we need to clarify the concepts of "null value" and "NULL":

• Empty values ​​do not take up space.
• NULL in MySQL actually takes up space. The following is an 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, if you have a cup, a null value means that the cup is vacuum, and NULL means that the cup is full of air. Although the cups look empty, the difference is huge.

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

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 ; 

Insert data:

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

mysql error:

#1048 - Column 'col1' cannot be null

One more

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

Inserted successfully.

It can be seen that "NULL" cannot be inserted into the NOT NULL field, only "null value" can be inserted. The answer to question 1 above is also available.

Regarding question 2, we have already said above that NULL is not actually a null value, but it takes up space. Therefore, when MySQL makes comparisons, NULL will participate in the field comparison, which has some impact on efficiency.
Moreover, B-tree indexes do not store NULL values, so if the indexed field can be NULL, the efficiency of the index will drop significantly.

Let's insert some more data into the test table:

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

Now the data in the table:

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

SELECT * FROM `test` WHERE col1 IS NOT NULL

SELECT * FROM `test` WHERE col1 <> ''

As you can see, the results are quite different, so we must figure out what search criteria to use based on business needs.

MYSQL recommends that column attributes be NOT NULL as much as possible

Length verification: Note that there is no space between the empty values''.

mysql> select length(''),length(null),length(' ');

+------------+--------------+--------------+

| length('') | length(null) | length(' ') |

+------------+--------------+--------------+

| 0 | NULL | 2 |

+------------+--------------+--------------+

Note:

• When counting the number of records in a column using count(), if a NULL value is used, the system will automatically ignore it, but the null value will be counted.
• To judge NULL, use IS NULL or IS NOT NULL. In SQL statement function, you can use ifnull() function to process it. To judge the null character, use ='' or <>'' to process it.
• Special considerations for MySQL: For the timestamp data type, if you insert a NULL value into a column of this data type, the value that appears is the current system time. If you insert a null value, 0000-00-00 00:00:00 will appear.
• Whether to use is null or ='' to judge the null value should be distinguished according to the actual business.

This concludes this article on the differences between MySQL null and not null and between null and empty value ''''. For more information about MySQL null and not null, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!