Should nullable fields in MySQL be set to NULL or NOT NULL?

People who often use MySQL may encounter the following situations:

1. My field type is not null, why can I insert a null value?

2. Why is not null more efficient than null?

3. When judging whether a field is not empty, what should be used

select * from table where column <> ''

Still need to use

select * from table where column is not null

With the above questions in mind, let's briefly study the difference between null and not null, what the difference between them is and their respective efficiency issues.

First, we need to clarify the concepts of "null value" and "NULL":

1. Empty values ​​do not take up space

2. 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 do an example to test it:

CREATE TABLE `codetc` ( 
`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 `codetc` VALUES (null,1);

mysql error:

#1048 - Column 'col1' cannot be null

One more

INSERT INTO `codetc` 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 a few more pieces of data into the codetc table:

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

Now according to the requirements, I want to count all the data in codetc 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 `codetc` WHERE col1 IS NOT NULL;

SELECT * FROM `codetc` WHERE col1 <> '';

As you can see, the results are quite different, so in actual situations, we must figure out whether we need to use null or not null based on business needs.

Note: MySQL fields try to avoid NULL, you should specify the column as NOT NULL unless you want to store NULL. In MySQL, columns containing null values ​​are difficult to query and NULL values ​​are not stored when indexing a table. Therefore, if the indexed field can be NULL, the efficiency of the index will drop significantly. Because they make indexes, index statistics, and comparison operations more complex. You should replace null values ​​with 0, a special value, or an empty string.

mysql set field not null to null

Statement:

ALTER TABLE table name MODIFY field name VARCHAR(20) DEFAULT NULL 

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links