数据库系统原理与应用教程（033）—— MySQL 的数据完整性（六）：非空（NOT NULL）约束

一、NULL 的特点

关于 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.（NULL 列需要行中的额外空间来记录它们的值是否为空。对于 myisam 表，每个 NULL 列需要额外的一位，四舍五入到最接近的字节。）

这说明，MySQL 中的 NULL 其实是占用空间的。例如：

/* CREATE TABLE t21( id int primary key, name VARCHAR(10) NOT NULL, addr VARCHAR(100) NULL ) ENGINE = MYISAM; */
mysql> CREATE TABLE t21(
    ->     id int primary key,
    ->     name VARCHAR(10) NOT NULL,
    ->     addr VARCHAR(100) NULL
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.04 sec)

-- 插入数据，当 name 列的值为 null 时导致插入失败
mysql> insert into t21 values(1,null,'河南省新乡市');
ERROR 1048 (23000): Column 'name' cannot be null

-- 空字符串可以插入，因此 null 和空字符串不相同
mysql> insert into t21 values(1,'','河南省新乡市');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t21;
+----+------+--------------------+
| id | name | addr               |
+----+------+--------------------+
|  1 |      | 河南省新乡市       |
+----+------+--------------------+
1 row in set (0.02 sec)

如果一个列允许取 NULL 值会存在如下问题：

（1）表索引时不存储 NULL 值，如果索引字段可以为 NULL，索引的效率会下降。

（2）当数据和 NULL 值进行数学运算和比较运算时，结果仍然为 NULL。因此，要判断一个数据是否为 NULL 值，必须用 IS NULL 或 IS NOT NULL 来判断。

（3）建议在创建表时，所有列都设置为不允许取空值（NOT NULL）。

二、定义非空约束

创建表时，所有字段默认可以取空值，如果需要将某个字段定义为不允许取空值，可以使用非空约束（NOT NULL）。

语法格式如下：

create table 表名(
    列名 类型 not null,
    ....
);

例如：创建表 t31，所有列都不允许取空值

/* create table t31( e_id int primary key, e_name char(20) not null, gender char(1) not null, jobtime datetime not null, salary int not null, phone char(20) not null, addr varchar(100) not null ); */
mysql> create table t31(
    ->     e_id int primary key,
    ->     e_name char(20) not null,
    ->     gender char(1) not null,
    ->     jobtime datetime not null,
    ->     salary int not null,
    ->     phone char(20) not null,
    ->     addr varchar(100) not null
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> desc t31;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id    | int(11)      | NO   | PRI | NULL    |       |
| e_name  | char(20)     | NO   |     | NULL    |       |
| gender  | char(1)      | NO   |     | NULL    |       |
| jobtime | datetime     | NO   |     | NULL    |       |
| salary  | int(11)      | NO   |     | NULL    |       |
| phone   | char(20)     | NO   |     | NULL    |       |
| addr    | varchar(100) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.03 sec)

插入数据，所有列都必须指定数据。

mysql> insert into t31 values(1001,'王大伟','男','1998-7-1',10200,'13737325689','河南省新乡市');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t31;
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| e_id | e_name    | gender | jobtime             | salary | phone       | addr               |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| 1001 | 王大伟    | 男     | 1998-07-01 00:00:00 |  10200 | 13737325689 | 河南省新乡市       |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
1 row in set (0.02 sec)

-- 任何的列不不能为 null，否则插入失败
mysql> insert into t31 values(1002,'刘涛','男','1996-6-11',12200,'13703732544',null);
ERROR 1048 (23000): Column 'addr' cannot be null

三、取消非空约束

要使某个列允许取空值，则修改该列的属性，去掉 not null 选项即可。

例如：使 t31 表中的 addr 列允许为空。

mysql> desc t31;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id    | int(11)      | NO   | PRI | NULL    |       |
| e_name  | char(20)     | NO   |     | NULL    |       |
| gender  | char(1)      | NO   |     | NULL    |       |
| jobtime | datetime     | NO   |     | NULL    |       |
| salary  | int(11)      | NO   |     | NULL    |       |
| phone   | char(20)     | NO   |     | NULL    |       |
| addr    | varchar(100) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table t31 modify addr varchar(100);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t31;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| e_id    | int(11)      | NO   | PRI | NULL    |       |
| e_name  | char(20)     | NO   |     | NULL    |       |
| gender  | char(1)      | NO   |     | NULL    |       |
| jobtime | datetime     | NO   |     | NULL    |       |
| salary  | int(11)      | NO   |     | NULL    |       |
| phone   | char(20)     | NO   |     | NULL    |       |
| addr    | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

插入数据，使 addr 为 null：

mysql> insert into t31 values(1002,'刘涛','男','1996-6-11',12200,'13703732544',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t31;
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| e_id | e_name    | gender | jobtime             | salary | phone       | addr               |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
| 1001 | 王大伟    | 男     | 1998-07-01 00:00:00 |  10200 | 13737325689 | 河南省新乡市       |
| 1002 | 刘涛      | 男     | 1996-06-11 00:00:00 |  12200 | 13703732544 | NULL               |
+------+-----------+--------+---------------------+--------+-------------+--------------------+
2 rows in set (0.00 sec)