Mysql explores not null constraints and null values, and mysqlnull

Source: Internet
Author: User

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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.