Differences between null and not null in mysql and efficiency issues

Differences between null and not null in mysql and efficiency issues

Mysql users may encounter the following situations:

1. My field type is not null. Why can I insert null values?

2. Why is not null more efficient than null?

3. When determining whether a field is not empty

Select * from table where column <>''

Still use

Select * from table where column is not null.

With the above questions, let's take a simple look at what is the difference between null and not null, what is the difference between them and their respective efficiency issues.

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

1. null values do not occupy space.

2. mysql NULL actually occupies space. The following is an official explanation of 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 indicates that the cup is in a vacuum state. NULL indicates that the cup is filled with air. Although the cup looks empty, the difference is great.

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

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

Mysql error:

#1048-Column 'col1' cannot be null

Next one

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

Successfully inserted.

It can be seen that the field not null cannot be inserted with "NULL", and only "NULL" can be inserted. The above question 1 also has the answer.

For question 2, we have already said that NULL is not a NULL value but takes up space. Therefore, during mysql comparison, NULL is involved in field comparison, therefore, efficiency is partly affected.

In addition, the B-tree index does not store NULL values. Therefore, if the index field can be NULL, the index efficiency will be greatly reduced.

We can insert several data records into the phpernote table:

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

Now, as needed, I want to count all the data in the phpernote table that col1 is not empty. Should I use "<>'' "or" IS NOT NULL, let's take a look at the differences in the results.

SELECT * FROM `phpernote` WHERE col1 IS NOT NULL;SELECT * FROM `phpernote` WHERE col1 <> '';

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

Articles you may be interested in

• Differences in the usage of tinyint, smallint, int, and bigint types in mysql
• Use MySQL Slow Log to analyze the high CPU usage of MySQL
• Note the following when querying strings with single quotes and inserting strings with single quotes in Mysql:
• MySQL uses limit 1 to improve query efficiency
• Differences among scrollHeight, scrollWidth, scrollLeft, and scrolltop in javascript
• Common knowledge about Mysql INDEX OPTIMIZATION
• Differences between MySQL Database Engine MyISAM and InnoDB
• Comparison of file_get_contents with curl in PHP