NULL and NOT NULL for "MySQL" inquiry

Believe that a lot of people who have been using MySQL for a long time, the concept of these two field properties is not very clear, the general will have the following questions:

1. My field type is not NULL, why can I insert a null value
2. is less efficient than null for a gross NOT NULL
3. When judging a field not to be empty, the exact select * from table where column <> "is to use a select * from table where the column is not NULL.

With some of the above questions, let's delve into the difference between null and NOT NULL. First, we need to figure out the concept of "null" and "NULL": 1. A null value is 2 of the space that is not occupied. The null in MySQL is actually space-consuming, and here's an explanation from the official MySQL

"NULL columns require additional space in the row to record whether their values is null. For MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte. "

For example, you have a cup, empty value means the cup is vacuum, null means the cup is filled with air, although the cup looks empty, but the difference is very big.

After figuring out the concepts of "null" and "null," the question is basically clear, let's take a look at the example:

TABLE  (   varcharvarchar    

Insert Test

Mysql>InsertIntoTestValues(Null,1);ERROR1048(23000):Column' Col1 'CannotBeNullMysql>InsertIntoTestValues(‘‘,Null);QueryOk,1RowAffected(0.00Sec)Mysql>InsertIntoTestValues(‘‘,1);QueryOk,1RowAffected(0.00Sec)Mysql>InsertIntoTestValues(' NULL ',1);QueryOk,1RowAffected(0.00Sec)mysql> select * from test;| col1 | col2 | +------+------+| | null || | 1 || null | 1 | +------+------+3 rows in set  ( Span class= "MF" >0.00 sec)         

Visible, not NULL field is not inserted null, can only insert "null", the above question 1 also has the answer. The reader should note that the last null inserted is not NULL, but rather the string "null". and the table for the InnoDB engine has the same test effect.

For question 2, as we have already said, NULL is not a null value, but a space, so when MySQL compares it, NULL participates in field comparisons, so there is a part of the effect on efficiency. and the B-Tree index (MyISAM table) does not store null values, so if the indexed field can be null, the efficiency of the index will be much lower. It is worth noting that NULL is avoided as much as possible.

1. Many tables contain nullable columns, even if the application does not need to save NULL, because nullable is the default property of the column (except for timestamp), but it is generally preferable to specify not NULL unless you really need to store a null value.
2. If the query contains nullable columns, it is more difficult for MySQL to optimize because nullable columns make index statistics and value comparisons more complex. Nullable columns use more storage space, and special handling is required in MySQL. When a nullable field is indexed, an extra byte is required for each index record, and in Myasim it may even cause a fixed-size index (for example, an index of only one integer column) to become a variable-size index.
3. It is common to change a nullable column to not NULL for a small performance boost, so it is not necessary to find and modify this situation in the existing schema first, unless you are sure that this is causing the problem. However, if you plan to build indexes on columns, you should try to avoid columns that are designed to be null. There are, of course, some exceptions, such as it is worth mentioning that InnoDB uses a separate bit to store null values, so for sparse data (many values are null and only a few rows are non-null) there is a good space efficiency. But this does not apply to MyISAM.

---cited from the fourth chapter of "High Performance mysql-Third Edition" schema and data type optimization

Solve the last question now, according to the requirements, I want to count all the data in the test table that col1 not empty, should I use "<>" or "is not NULL", let's look at the difference between the results.

Mysql>SELECT*From' Test 'WHERECol1IsNotNull;+------+------+|Col1|Col2|+------+------+|Null|1|||1|||Null|+------+------+3RowsInchSet (0.00 sec) mysql > select * from where col1 <>  "+------+------+| col1 | col2 | +------+------+| null | 1 | +------+------+1 row in set  ( Span class= "MF" >0.00 sec)         

As you can see, the results are very different, so we have to find out exactly what kind of search criteria to use, based on business needs.

NULL and NOT NULL for "MySQL" inquiry