One thousand reasons for not using Null, Null

One thousand reasons for not using Null, Null

Hong Kong, Null seems to be a headache everywhere. For example, NullPointerException, which is a headache in Java, in order to avoid the unexpected Null pointer exception, for thousands of years, programmers have to carefully judge if in the code, which is troublesome and bloated. Therefore, Java 8 has introduced Optional to avoid this problem.

Next we will talk about null in MySQL. in a large number of MySQL optimization articles and books, we have mentioned that the field should NOT be NULL as much as possible, rather than NULL unless in special circumstances. However, they only give the conclusion not to explain the cause, just as chicken soup does not give a spoon, so many beginners are skeptical about this conclusion or are in the fog. This article analyzes in detail the reason for using Null today and provides some reasons for not using Null.

1. why are so many people using NULL?

• NULL is the default value when creating a data table. Beginners or uninformed or troublesome programmers will not pay attention to this.

• Many people think that not null requires more space, which is not the focus.

• The key point is that many Programmers think that NULL does not need to judge data insertion during development, making it easier and faster to write SQL statements.

2. Is it possible to spread data in different ways?

MySQL official website documentation:

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

It is difficult for Mysql to optimize the query of null reference columns, which makes the index and index statistics and values more complex. Empty columns require more storage space and special processing in mysql. After an empty column is indexed, each record requires an additional byte, which can also change the fixed-size index in MYisam to a variable-size index.

-- From high-performance mysql version 2

According to this analysis, it is not just a matter of rumors. This has a theoretical basis and a source.

3. Why not use Null?

(1) All NULL values can be expressed by a meaningful value, which facilitates code readability and maintainability and enhances the business data standardization from constraints.

(2) update from a NULL value to a non-NULL value cannot be performed in-situ, which is more prone to index splitting and affects performance.

Note: However, the performance prompt of changing the NULL column to not null is very small. Unless it is determined that it causes problems, do NOT take it as a priority optimization measure, the most important thing is the suitability of the column type used.

(3) The NULL value is prone to problems in the timestamp type. In particular, the parameter explicit_defaults_for_timestamp is not enabled.

(4) not in ,! = When a negative condition query has a NULL value, the returned result is always NULL, and the query is prone to errors.

Create table table_2 (

'Id' INT (11) not null,

User_name varchar (20) NOT NULL

)

 

 

Create table table_3 (

'Id' INT (11) not null,

User_name varchar (20)

)

 

Insert into table_2 values (4, "zhaoliu_2_1"), (2, "lisi_2_1"), (3, "wangmazi_2_1"), (1, "zhangsan_2"), (2, "lisi_2_2"), (4, "zhaoliu_2_2"), (3, "wangmazi_2_2 ")

 

Insert into table_3 values (1, "zhaoliu_2_1"), (2, null)

 

-- 1. If the not in subquery has a NULL value, the returned result is always NULL, which is prone to query errors.

Select user_name from table_2 where user_name not in (select user_name from table_3 where id! = 1)

 

Mysql root@10.48.186.32: t_test_zz5431> select user_name from table_2 where user_name not

-> In (select user_name from table_3 where id! = 1 );

+ ------------- +

| User_name |

| ------------- |

+ ------------- +

0 rows in set

Time: 0.008 s

Mysql root@10.48.186.32: t_test_zz5431>

 

-- 2. The Single Column index does not store null values, and the composite index does not store all null values. If the column permits null, The result set "unexpected" may be obtained.

-- If the name can be null, the index does not store null values, and the results do not contain these records. Therefore, use the not null constraint and default value.

Select * from table_3 where name! = 'Zhaoliu _ 2_1'

 

-- 3. If you splice two fields: for example, question number + score, you must first judge non-null for each field. Otherwise, if any field is empty, the concatenation result is null.

Select CONCAT ("1", null) from dual; -- the execution result is null.

 

-- 4. If a Null column exists, count (Null column) requires special attention. null values are not counted.

Mysql root@10.48.186.32: t_test_zz5431> select * from table_3;

+ ------ + ------------- +

| Id | user_name |

| ------ + ------------- |

| 1 | zhaoliu_2_1 |

| 2 | <null> |

| 21 | zhaoliu_2_1 |

| 22 | <null> |

+ ------ + ------------- +

4 rows in set

Time: 0.007 s

Mysql: t_test_zz5431> select count (user_name) from table_3;

+ -------------------- +

| Count (user_name) |

| ------------------ |

| 2 |

+ -------------------- +

1 row in set

Time: 0.007 s

 

-- 5. Pay attention to the Null field judgment method. If it is null, an error is returned.

Mysql root @ localhost: cygwin> create index IDX_test on table_3 (user_name );

Query OK, 0 rows affected

Time: 0.040 s

Mysql root @ localhost: cygwin> select * from table_3 where user_name is null \ G

* ************************** [1. row] ***************************

Id | 2

User_name | None

 

1 row in set

Time: 0.002 s

Mysql root @ localhost: cygwin> select * from table_3 where user_name = null \ G

 

0 rows in set

Time: 0.002 s

Mysql root @ localhost: cygwin> desc select * from table_3 where user_name = 'zhaoliu _ 2_1 '\ G

* ************************** [1. row] ***************************

Id | 1

Select_type | SIMPLE

Table | table_3

Type | ref

Possible_keys | IDX_test

Key | IDX_test

Key_len | 23

Ref | const

Rows | 1

Extra | Using where

 

1 row in set

Time: 0.006 s

Mysql root @ localhost: cygwin> desc select * from table_3 where user_name = null \ G

* ************************** [1. row] ***************************

Id | 1

Select_type | SIMPLE

Table | None

Type | None

Possible_keys | None

Key | None

Key_len | None

Ref | None

Rows | None

Extra | Impossible WHERE noticed after reading const tables

 

1 row in set

Time: 0.002 s

Mysql root @ localhost: cygwin> desc select * from table_3 where user_name is null \ G

* ************************** [1. row] ***************************

Id | 1

Select_type | SIMPLE

Table | table_3

Type | ref

Possible_keys | IDX_test

Key | IDX_test

Key_len | 23

Ref | const

Rows | 1

Extra | Using where

 

1 row in set

Time: 0.002 s

Mysql root @ localhost: cygwin>

(5) The Null column requires more storage space: an extra byte is required as a flag to determine whether the column is NULL.

Alter table table_3 add index idx_user_name (user_name );

Alter table table_2 add index idx_user_name (user_name );

Explain select * from table_2 where user_name = 'zhaoliu _ 2_1 ';

Explain select * from table_3 where user_name = 'zhaoliu _ 2_1 ';

We can see that the same varchar (20) length is greater than the table_3 index length because:

The character sets of the two tables are different, and one field is NULL and the other is non-NULL.

The calculation rules of key_len are related to three factors: data type, character encoding, and whether the value is NULL.

Key_len 62 = 20*3 (utf8 3 bytes) + 2 (storage varchar variable length 2 bytes, fixed length field no additional bytes)

Key_len 83 = 20*4 (utf8mb4 4 bytes) + 1 (identify whether it is Null) + 2 (store varchar variable length 2 bytes, fixed length field no additional bytes)

Therefore, it is recommended that the index field not be NULL, because NULL will make the index, index statistics and values more complex, and requires an additional byte of storage space. Based on the above reasons and the reasons, I think we should not use Null.

Source: xrzs,

My.oschina.net/leejun2005/blog/1342985

Sharing a circle of friends is another kind of appreciation

The more we share, The more we have

 

Welcome to the efficient data analysis community

Add me to the big data dry goods group: tongyuannow

More than 100000 people are interested in joining us