Mysql探究not null约束与空值，mysqlnull

Mysql探究not null约束与空值，mysqlnull

昨天有个前端开发突然问了我一个基础性的问题，“我的表列有not null约束，为什么还可以插入空值呢？而且更奇怪的是，之前插入空值都可以，现在我用工具插入空值为什么就会报错呢？”。

首先来看看她说的那个表，看看里面的“空值”是何方妖孽。查看了一下

 

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

此时telephone 存在非空约束：

确实有空值的情况，然后先怀疑了下会不会是列中存在空格，查看了一下，结果如下

(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>

看到字符长度为0，也不存在空格；那我们再看下之前插入的语句（只看telephone一列），发现telephone列插入的是空值('')。

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

我们再来做一个实验，大概的看下null和空值有啥差别。

mysql> show create table melody \G;
*************************** 1. row ***************************
       Table: melody
Create Table: CREATE TABLE `melody` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 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 null

mysql> insert into melody(id) values (2);
ERROR 1364 (HY000): Field 'val' doesn't have a default value</span>

从结果可以看到Mysql里面的空值和null大概的差别。那么，当用select排除非空集合的数据时，也就理所当然的应该有两个条件，not null and <> ''。

 

mysql> show create table melody_null\G;
*************************** 1. row ***************************
<span style="white-space:pre">	</span>Table: melody_null
Create Table: CREATE TABLE `melody_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 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>

看到上面的实验结果，大概可以看出“空值” 和 “NULL” 很明显的差别，那么先来说下这俩的概念：

1、空值是不占用空间的

2、mysql中的NULL其实是占用空间的，下面是来自于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)

在来扩展的说一下索引的使用情况，从上面也可以看出，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。

而且B树索引是不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。所以，如果引用索引列的话，最好使用not null去约束一下，防止该使用索引而不使用的情况发生。