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

This article mainly introduces the method of MySQL to judge whether the field is null. It is very detailed and has a certain reference value. Interested friends must read it!

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 select * from table wherecolumn''or select * from table wherecolumn 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.

First of all, we need to understand the concepts of "null value" 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.

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

Insert data:

INSERT INTO `test` VALUES (null,1)

An error occurred in mysql:

# 1048-Column 'col1' cannot be null

One more.

INSERT INTO `test`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 will be answered.

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

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

Length verification: note that there is no space between the null values''.

Mysql > select length (''), length (null), length ('') +-+ | length ('') | length (null) | length ('') | +-+ | 0 | NULL | 2 | +- -- +

Note:

When count () counts the records of a column, the system will automatically ignore the null value if it is used, but the null value will be counted into it.

Judge that NULL uses IS NULL or IS NOT NULL. In the SQL statement function, you can use the ifnull () function to process, and to judge empty characters, use =''or''to process.

For MySQL special considerations, for timestamp data types, if null values are inserted into columns inserted into this data type, the value that appears is the current system time. Insert a null value and you get 0000-00-0000: 00:00

Whether the null value is judged by is null or =''should be distinguished according to the actual business.

The above is all the contents of MySQL's method to determine whether the field is null. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.