How does MySQL determine whether a field is null?

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

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

Why not null is more efficient than null

When judging that a field is not empty, should we select * from table where column '' or should we use select * from table wherecolumn is not null yet.

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

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. A null value means that the cup is vacuum, and NULL means that the cup is filled with air. Although the cup looks empty, But the difference is huge.

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 ;

Insert data:

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

Mysql error occurred:

#1048 - Column 'col1' cannot be null

Another one

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

was successfully inserted.

It can be seen that "NULL" cannot be inserted into the NOT NULL field, only "null value" can be inserted, and the above question has the answer.

As for the problem, 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.

MYSQL recommends that the column attribute be NOT NULL as much as possible

Length verification: Note that there are no spaces between the '' of the null value.

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

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

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

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

| 0 | NULL | 2 |

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

Note:

When count() is used to count the number of records in a column, if the NULL value is used, the system will automatically ignore it, but if the NULL value is used, the system will automatically ignore it. The value will be counted into it.

Use IS NULL or IS NOT NULL to judge NULL. You can use the ifnull() function in SQL statement functions to process. Use ='' or '' to judge null characters.

Special considerations for MySQL, for the timestamp data type, if you insert a NULL value into a column inserted into 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 depends on the actual business.

The above is the detailed content of How does MySQL determine whether a field is null?. For more information, please follow other related articles on the PHP Chinese website!