Mysql探究notnull約束與空值

Mysql探究notnull約束與空值

昨天有個前端開發突然問了我一個基礎性的問題，“我的表列有not null約束，為什麼還可以插入空值呢？而且更奇怪的是，之前插入空值都可以，現在我用工具插入空值為什麼就會報錯呢？”。

首先來看看她說的那個表，看看裡面的“空值”是何方妖孽。查看了一下

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

此時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)

看到字符長度為0，也不存在空格；那我們再看下之前插入的語句（只看telephone一列），發現telephone列插入的是空值('')。

INSERT INTO `websiteCode`(telephone) VALUES ('');

我們再來做一個實驗，大概的看下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

從結果可以看到Mysql裡面的空值和null大概的差別。那麼，當用select排除非空集合的數據時，也就理所當然的應該有兩個條件，not null and <> ''。

mysql> show create table melody_null\G;
*************************** 1. row ***************************
	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)

看到上面的實驗結果，大概可以看出“空值” 和 “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去約束一下，防止該使用索引而不使用的情況發生。