Shulou(Shulou.com)06/01 Report--

People who use mysql regularly 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 the judgment field is not empty, do you want to use it?

Select * from table where column''

Still need to use it.

Select * from table where column is not null

With the above questions, let's take a brief look at the differences between null and not null, what are the differences between them, and their respective efficiency issues.

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

1. Null values do not take up space

2. The NULL in mysql actually takes up space. Here 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 represents a cup filled with air. Although the cup looks empty, there is a big difference.

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

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)

An error occurred in mysql:

# 1048-Column 'col1' cannot be null

One more.

INSERT INTO `codetc` VALUES (', 1)

Successfully inserted.

It can be seen that the NOT NULL field can not be inserted into the "NULL", can only be inserted into the "null value", the above question 1 will have the answer.

For question 2, as we have said above, NULL is not a null value, but takes up space, so when mysql makes a comparison, NULL will participate in the field comparison, so it has a partial impact on efficiency.

And the null value is not stored in the B-tree index, so if the field of the index can be NULL, the efficiency of the index will be much lower.

Let's insert a few more pieces of data into codetc's table:

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

Now, according to the demand, I want to count all the data in the codetc table where col1 is not empty. Should I use "'" or "IS NOT NULL"? let's look at the difference between the results.

SELECT * FROM `codetc` WHERE col1 IS NOT NULL;SELECT * FROM `codetc`WHERE col1''

As you can see, the results are very different, so in practice, we must figure out whether we need to use null or not null according to our business needs.

Note: the MySQL field avoids NULL as much as possible, and you should specify the column NOT NULL unless you want to store the NULL. In MySQL, it is difficult to optimize the query for columns with null values, and null values are not stored on table indexes, so if the field of the index can be NULL, the efficiency of the index will be much lower. Because they make the index, index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.

Mysql setting field not null becomes null

Statement:

ALTER TABLE Table name MODIFY Field name VARCHAR (20) DEFAULT NULL

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.