Analysis of the reasons why MySQL field definitions should not use null

Why is NULL so often used?

(1) Java's null

Null is a headache, such as NullPointerException in Java. In order to avoid unexpected null pointers, you need to carefully make various if judgments, which is troublesome and bloated.

For this reason, there are many open source packages that have many processing

common lang3's StringUtils.isBlank(); CollectionUtils.isEmpty();

Guava's Optional

Even Java 8 introduced Optional to avoid this problem (similar to Guava, with slightly different usage)

(2) Why is MySQL's null so widely abused?

(a) Creating non-standard nulls is the default when creating a data table. Some MySQL clients may not specify not null in their automatically generated table statements.

(b) Some people may think that not null needs more space.

(c) To save trouble, there is no need to judge the null value when inserting data during development, which makes it easier to write SQL

2. Official Documents

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.

MySQL has difficulty optimizing queries that reference nullable columns, which makes indexes, index statistics, and values ​​more complex. Nullable columns require more storage space and require special processing within MySQL. After the nullable column is indexed, each record requires an extra byte and can also cause the fixed-size index in MYISAM to become a variable-size index. —— From "High Performance MySQL Second Edition"

It seems that there is no performance advantage to not specifying not null.

Three reasons why MySQL does not use null

(1) All situations where NULL values ​​are used can be represented by a meaningful value, which is conducive to the readability and maintainability of the code and can enhance the standardization of business data from a constraint perspective.

(2) Updates from NULL values ​​to non-NULL values ​​cannot be done in-place, which makes index splitting more likely to occur, thus affecting performance. (The performance improvement of null -> not null is very small, so don’t consider it as a priority optimization measure unless you are sure it causes problems)

(3) NULL values ​​are prone to problems in the timestamp type, especially when the explicit_defaults_for_timestamp parameter is not enabled.

(4) Negative condition queries such as NOT IN and != always return empty results when there is a NULL value, which makes the query prone to errors.

Four bad cases caused by null

Data initialization:

create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)


create table table2 (
    `id` INT (11) NOT NULL,
    `name` varchar(20)
)

insert into table1 values ​​(4,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values ​​(1,"zhaoyun"),(2, null)

(1) A NOT IN subquery always returns an empty result when there is a NULL value, and the query is prone to errors.

select name from table1 where name not in (select name from table2 where id!=1)

+-------------+
| name |
|-------------|
+-------------+

(2) Column values ​​are allowed to be empty, the index does not store null values, and these records are not included in the result set.

select * from table2 where name != 'zhaoyun'

+------+-------------+
| id | name |
|------+-------------|
| | |
+------+-------------+

select * from table2 where name != 'zhaoyun1'

+------+-------------+
| id | name |
|------+-------------|
| 1 | zhaoyun |
+------+-------------+

(3) When using concat, you must first check whether each field is non-null. Otherwise, if any field is empty, the concatenated result will be null.

select concat("1", null) from dual;

+--------------------+
| concat("1", null)|
|--------------------|
| NULL |
+--------------------+

(4) When calculating count, null columns are not included in the statistics.

select count(name) from table2;

+--------------------+
| count(user_name) |
|--------------------|
| 1 |
+--------------------+

Comparison of five index lengths

alter table table1 add index idx_name (name);
alter table table2 add index idx_name (name);
explain select * from table1 where name='zhaoyun';
explain select * from table2 where name='zhaoyun';

key_len of table1 = 82

key_len of table2 = 83

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

key_len 82 = 20 * 4 (utf8mb4 - 4 bytes, utf8 - 3 bytes) + 2 (the length of storing varchar variable-length characters is 2 bytes, and fixed-length fields do not require additional bytes)

key_len 83 = 20 * 4 (utf8mb4 - 4 bytes, utf8 - 3 bytes) + 2 (varchar variable-length characters are stored in 2 bytes, fixed-length fields do not require additional bytes) + 1 (null flag)

Therefore, it is best not to set the index field to NULL, because NULL will make the index, index statistics, and values ​​more complicated, and require an extra byte of storage space.

This is the end of this article about how to not use null in MySQL field definition. For more information about null in MySQL field definition, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!