Mysql explores not null constraints and null values, and mysqlnull

Mysql explores not null constraints and null values, and mysqlnull

Yesterday, a front-end developer suddenly asked me a basic question: "My table columns have the not null constraint. Why can I insert null values? What's more strange is that you can insert null values before. Why is an error reported when I use a tool to insert null values ?".

First, let's look at the table she is talking about and see what the "null" in it is. I checked it.

(root@localhost:)> select telephone from websitecode order by telephone limit 10; +-----------+| telephone |+-----------+|           ||           ||           ||           ||           ||           ||           ||           ||           ||           |+-----------+10 rows in set (0.04 sec)</span>

In this case, telephone has a non-empty constraint:

Check whether there is a null value in the column. The result is as follows:

(root@localhost:)> select telephone,length(telephone) from websitecode order by telephone limit 10;        +-----------+-------------------+| telephone | length(telephone) |+-----------+-------------------+|           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 ||           |                 0 |+-----------+-------------------+10 rows in set (0.03 sec)</span>

We can see that the character length is 0 and there is no space. Then let's look at the previously inserted Statement (only in the telephone column) and find that the telephone column is inserted with a null value ('').

INSERT INTO `websiteCode`(telephone) VALUES ('');</span>

Let's start another experiment. Let's take a look at the differences between null and null.

mysql> show create table melody \G;*************************** 1. row ***************************       Table: melodyCreate Table: CREATE TABLE `melody` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `val` varchar(10) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> insert into melody values (1,'');Query OK, 1 row affected (0.10 sec)mysql> insert into melody values (2,null);ERROR 1048 (23000): Column 'val' cannot be nullmysql> insert into melody(id) values (2);ERROR 1364 (HY000): Field 'val' doesn't have a default value</span>

From the results, we can see the approximate difference between null and null in Mysql. Then, when using select to exclude data from a non-empty set, two conditions should naturally exist, not null and <> ''.

 

mysql> show create table melody_null\G;*************************** 1. row ***************************<span style="white-space:pre"></span>Table: melody_nullCreate Table: CREATE TABLE `melody_null` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `val` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> insert into melody_null values(1,'aaaa');Query OK, 1 row affected (0.13 sec)mysql> insert into melody_null values(2,'');Query OK, 1 row affected (0.12 sec)mysql> insert into melody_null(id) values (3);Query OK, 1 row affected (0.09 sec)mysql> select * from melody_null;+----+------+| id | val  |+----+------+|  1 | aaaa ||  2 |      ||  3 | NULL |+----+------+3 rows in set (0.00 sec)mysql> select * from melody_null where val is not null;+----+------+| id | val  |+----+------+|  1 | aaaa ||  2 |      |+----+------+2 rows in set (0.00 sec)mysql> select * from melody_null where val is not null and val <> '';+----+------+| id | val  |+----+------+|  1 | aaaa |+----+------+1 row in set (0.00 sec)</span>

Seeing the above experiment results, we can see the obvious difference between "NULL" and "NULL". Let's talk about these two concepts first:

1. null values do not occupy space.

2. mysql NULL actually occupies space. The following is an official explanation of 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 ."

mysql> select length('');+------------+| length('') |+------------+|          0 |+------------+1 row in set (0.00 sec)mysql> select length(null);+--------------+| length(null) |+--------------+|         NULL |+--------------+1 row in set (0.00 sec)

Let's talk about the index usage in terms of extension. We can see from the above that NULL is not a NULL value, but takes up space. Therefore, when mysql is comparing, NULL is involved in field comparison, which affects the efficiency.

In addition, the B-tree index does not store NULL values. Therefore, if the index field can be NULL, the index efficiency will decrease a lot. Therefore, if you reference an index column, it is best to use not null to constrain it to prevent the use of the index from being used.