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

Earlier, I saw that someone asked about nullable and indexes in PHPHub migration database files. I believe that many people who have used MySQL for a long time (especially those who pay too much attention to business development) are not very clear about the concept of these two field attributes, and generally have the following questions:

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

Because not null is more efficient than null.

Determine whether to use column''or column is not null when the field is not empty.

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

Is null the same as the null value?

First, we need to understand the concepts of null values and null:

Null values do not take up space.

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.

A little chestnut.

After figuring out 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

Question1: my field type is not null, why can I insert a null value?

Execute the following SQL, and an error occurs, prompting Column 'col1' cannot be null.

INSERT INTO `test` VALUES (null, 1)

One more, the execution is successful.

INSERT INTO `test`VALUES (', 1)

It can be seen that the NOT NULL field can not be inserted into NULL (this is nonsense), can only insert null values, the above question 1 will have the answer.

Question 2: why is not null more efficient than null?

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.

Question 3: when judging that the field is not empty, whether to use column'' or column is not null.

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

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

Now, according to the demand, 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 see the difference between the results.

The data in the table is now as follows:

Compare the execution effect of the following two sentences of SQL.

SELECT * FROM `test` WHERE col1 IS NOT NULL

SELECT * FROM `test`WHERE col1''

As you can see, the results are very different, so we must figure out what kind of search criteria to use and whether or not to be null according to our business needs.

A small pit that I encountered.

When I first joined the job as the first requirement online a long time ago, I only noticed that not null was more efficient than null.

All right, when I add fields to the existing table, I set it all to not null, and I feel awesome.

Because many Service have insert actions to operate on this table, it is conceivable that as soon as it was launched, the error Column 'col1' cannot be null pervaded everyone's mailbox throughout the development team.

Therefore, when the volume of business is not very large, the use of many technologies actually need to be considered according to the actual situation.

These are the details of NULL and NOT NULL in MySQL, please pay attention to other related articles!

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.