MySQL 表的约束

表的约束

数据类型虽然可以约束字段，但其约束方式较为单一。为了更好地保证数据的合法性以及从业务逻辑角度确保数据的正确性，还需要一些额外的约束。例如，对于一个字段是邮箱（email）的情况，通常要求其值是唯一的。

表的约束有很多种，这里主要介绍以下几种：null/not null、default、comment、zerofill、primary key、auto_increment、unique key。

1. 空属性

空属性有两个值：null（默认值）和not null（不为空）。数据库默认字段基本都是可以为空的，但在实际开发中，尽可能保证字段不为空，因为数据为空可能无法参与运算。

例如，创建一个班级表，包含班级名和班级所在的教室。从正常的业务逻辑来看：

• 如果班级没有名字，就无法确定是哪个班级。

• 如果教室名字可以为空，就不清楚在哪里上课。

因此，在设计数据库表时，需要在表中进行限制，满足上述条件的数据不能插入到表中，这就是“约束”的作用。

mysql> select null;+------+| NULL |+------+| NULL |+------+1 row in set (0.00 sec)mysql> select 1+null;+--------+| 1+null |+--------+| NULL |+--------+1 row in set (0.00 sec)mysql> create table myclass( -> class_name varchar(20) not null, -> class_room varchar(10) not null);Query OK, 0 rows affected (0.02 sec)mysql> desc myclass;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| class_name | varchar(20) | NO | | NULL | || class_room | varchar(10) | NO | | NULL | |+------------+-------------+------+-----+---------+-------+

2. 默认值

默认值是指某一种数据会经常性地出现某个具体的值，可以在一开始就指定好。当数据在插入时未给该字段赋值时，就会使用默认值。

例如：

mysql> create table tt10 ( -> name varchar(20) not null, -> age tinyint unsigned default 0, -> sex char(2) default \'男\' -> );Query OK, 0 rows affected (0.00 sec)mysql> desc tt10;+-------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| name | varchar(20) | NO | | NULL | || age | tinyint(3) unsigned | YES | | 0 | || sex | char(2) | YES | | 男 | |+-------+---------------------+------+-----+---------+-------+mysql> insert into tt10(name) values(\'zhangsan\');Query OK, 1 row affected (0.00 sec)mysql> select * from tt10;+----------+-----+-----+| name | age | sex |+----------+-----+-----+| zhangsan | 0 | 男 |+----------+-----+-----+

注意：只有设置了default的列，才可以在插入值时对列进行省略。

3. 列描述

列描述（comment）没有实际含义，专门用来描述字段。它会根据表创建语句保存，以便程序员或数据库管理员（DBA）进行了解。通过desc查看不到注释信息。

例如：

mysql> create table tt12 ( -> name varchar(20) not null comment \'姓名\', -> age tinyint unsigned default 0 comment \'年龄\', -> sex char(2) default \'男\' comment \'性别\' -> );

注意：not null和default一般不需要同时出现，因为default本身有默认值，不会为空。

通过show可以看到注释信息：

mysql> show create table tt12\\G*************************** 1. row ***************************Table: tt12Create Table: CREATE TABLE `tt12` ( `name` varchar(20) NOT NULL COMMENT \'姓名\', `age` tinyint(3) unsigned DEFAULT \'0\' COMMENT \'年龄\', `sex` char(2) DEFAULT \'男\' COMMENT \'性别\') ENGINE=MyISAM DEFAULT CHARSET=gbk1 row in set (0.00 sec)

4. zerofill

刚开始学习数据库时，很多人对数字类型后面的长度感到困惑。例如，通过show查看tt3表的建表语句，可以看到int(10)，但这并不表示整型的存储字节，而是一个显示宽度。如果没有zerofill属性，括号内的数字是毫无意义的。例如：

mysql> create table tt3 ( -> a int(10) unsigned DEFAULT NULL, -> b int(10) unsigned DEFAULT NULL -> );Query OK, 0 rows affected (0.00 sec)mysql> insert into tt3 values(1,2);Query OK, 1 row affected (0.00 sec)mysql> select * from tt3;+------+------+ | a | b |+------+------+ | 1 | 2 |+------+------+ 

但如果对列添加了zerofill属性，显示的结果会有所不同。例如，修改tt3表的a列属性：

mysql> alter table tt3 change a a int(5) unsigned zerofill;mysql> show create table tt3\\G*************************** 1. row ***************************Table: tt3Create Table: CREATE TABLE `tt3` ( `a` int(5) unsigned zerofill DEFAULT NULL, -- 具有了zerofill `b` int(10) unsigned DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=gbk1 row in set (0.00 sec)mysql> select * from tt3;+-------+------+| a | b |+-------+------+| 00001 | 2 |+-------+------+

可以看到，a的值由原来的1变成了00001。这就是zerofill属性的作用：如果宽度小于设定的宽度（这里设置的是5），会自动填充0。但需要注意的是，这只是显示结果的格式化输出，实际存储的仍然是1。可以通过hex函数来证明：

mysql> select a, hex(a) from tt3;+-------+--------+| a | hex(a) |+-------+--------+| 00001 | 1 |+-------+--------+

5. 主键

主键（primary key）用于唯一约束字段中的数据，要求字段值不能重复且不能为空。一张表中最多只能有一个主键，主键所在的列通常是整数类型。

例如，在创建表时可以直接在字段上指定主键：

mysql> create table tt13 ( -> id int unsigned primary key comment \'学号不能为空\', -> name varchar(20) not null);Query OK, 0 rows affected (0.00 sec)mysql> desc tt13;+-------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id | int(10) unsigned | NO | PRI | NULL | | <= key 中 pri表示该字段是主键| name | varchar(20) | NO | | NULL | |+-------+------------------+------+-----+---------+-------+

主键约束要求主键对应的字段中不能重复，一旦重复，操作会失败。例如：

mysql> insert into tt13 values(1, \'aaa\');Query OK, 1 row affected (0.00 sec)mysql> insert into tt13 values(1, \'aaa\');ERROR 1062 (23000): Duplicate entry \'1\' for key \'PRIMARY\'

当表创建好但没有主键时，可以再次追加主键，也可以删除主键：

mysql> alter table tt13 drop primary key;mysql> desc tt13;+-------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id | int(10) unsigned | NO | | NULL | || name | varchar(20) | NO | | NULL | |+-------+------------------+------+-----+---------+-------+

此外，还可以使用复合主键。在创建表时，在所有字段之后，使用primary key(主键字段列表)来创建主键。如果有多个字段作为主键，可以使用复合主键。例如：

mysql> create table tt14( -> id int unsigned, -> course char(10) comment \'课程代码\', -> score tinyint unsigned default 60 comment \'成绩\', -> primary key(id, course) -- id和course为复合主键 -> );Query OK, 0 rows affected (0.01 sec)mysql> desc tt14;+--------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+-------+| id | int(10) unsigned | NO | PRI | 0 | | <= 这两列合成主键| course | char(10) | NO | PRI | | || score | tinyint(3) unsigned | YES | | 60 | |+--------+---------------------+------+-----+---------+-------+

插入数据时，如果主键冲突，会报错：

sql

复制

mysql> insert into tt14 (id,course)values(1, \'123\');Query OK, 1 row affected (0.02 sec)mysql> insert into tt14 (id,course)values(1, \'123\');ERROR 1062 (23000): Duplicate entry \'1-123\' for key \'PRIMARY\' -- 主键冲突

6. 自增长

自增长（auto_increment）是指当对应的字段未给值时，系统会自动从当前字段中已有的最大值加1，生成一个新的不同值。通常与主键搭配使用，作为逻辑主键。

自增长的特点包括：

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）。

• 自增长字段必须是整数。

• 一张表最多只能有一个自增长字段。

例如：

mysql> create table tt21( -> id int unsigned primary key auto_increment, -> name varchar(10) not null default \'\' -> );mysql> insert into tt21(name) values(\'a\');mysql> insert into tt21(name) values(\'b\');mysql> select * from tt21;+----+------+| id | name |+----+------+| 1 | a || 2 | b |+----+------+

在插入后，可以通过last_insert_id()函数获取上次插入的AUTO_INCREMENT的值（批量插入获取的是第一个值）：

mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 1 |+------------------+

7. 唯一键

一张表中往往有很多字段需要唯一性约束，但一张表中只能有一个主键。唯一键（unique key）可以解决表中有多个字段需要唯一性约束的问题。唯一键的本质和主键差不多，但唯一键允许为空，且可以有多个为空的值，空字段不做唯一性比较。

关于唯一键和主键的区别，可以简单理解为：主键更多的是标识唯一性，而唯一键更多的是保证在业务上不要和别的信息出现重复。例如：

mysql> create table student ( -> id char(10) unique comment \'学号，不能重复，但可以为空\', -> name varchar(10) -> );Query OK, 0 rows affected (0.01 sec)mysql> insert into student(id, name) values(\'01\', \'aaa\');Query OK, 1 row affected (0.00 sec)mysql> insert into student(id, name) values(\'01\', \'bbb\'); -- 唯一约束不能重复ERROR 1062 (23000): Duplicate entry \'01\' for key \'id\'mysql> insert into student(id, name) values(null, \'bbb\'); -- 但可以为空Query OK, 1 row affected (0.00 sec)mysql> select * from student;+------+------+| id | name |+------+------+| 01 | aaa || NULL | bbb |+------+------+

8. 外键

外键用于定义主表和从表之间的关系。外键约束主要定义在从表上，主表则必须有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列中存在或为null。

语法：

foreign key (字段名) references 主表(列)

例如，假设一个场景（当然，具体可能并不是这样，仅仅为了帮助理解）：

在公司中，需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息：一个身份证号码和一个员工工号。可以选择身份证号码作为主键。而设计员工工号时，需要一种约束，即所有员工工号都不能重复。这里的约束是业务上的，不能重复，因此可以将员工工号设计为唯一键。

一般而言，建议将主键设计为与当前业务无关的字段，这样当业务调整时，可以尽量避免对主键进行大的调整。

例如：

mysql> create table myclass ( -> id int primary key, -> name varchar(30) not null comment\'班级名\' -> );mysql> create table stu ( -> id int primary key, -> name varchar(30) not null comment \'学生名\', -> class_id int, -> foreign key (class_id) references myclass(id) -> );mysql> insert into myclass values(10, \'C++大牛班\'),(20, \'java大神班\');Query OK, 2 rows affected (0.03 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into stu values(100, \'张三\', 10),(101, \'李四\',20);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into stu values(102, \'wangwu\',30);ERROR 1452 (23000): Cannot add or update a child row:a foreign key constraint fails (mytest.stu, CONSTRAINT stu_ibfk_1FOREIGN KEY (class_id) REFERENCES myclass (id))mysql> insert into stu values(102, \'wangwu\', null);Query OK, 1 row affected (0.00 sec)

9. 综合案例 - 阅读

有一个商店的数据，记录客户及购物情况，由以下三个表组成：

• 商品表goods（商品编号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider）

• 客户表customer（客户号customer_id，姓名name，住址address，邮箱email，性别sex，身份证card_id）

• 购买表purchase（购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums）

要求：

• 每个表的主外键关系。

• 客户的姓名不能为空值。

• 邮箱不能重复。

• 客户的性别只能是“男”或“女”。

SQL代码如下：

-- 创建数据库create database if not exists bit32malldefault character set utf8 ;-- 选择数据库use bit32mall;-- 创建数据库表-- 商品表create table if not exists goods(goods_id int primary key auto_increment comment \'商品编号\',goods_name varchar(32) not null comment \'商品名称\',unitprice int not null default 0 comment \'单价，单位分\',category varchar(12) comment \'商品分类\',provider varchar(64) not null comment \'供应商名称\');-- 客户表create table if not exists customer(customer_id int primary key auto_increment comment \'客户编号\',name varchar(32) not null comment \'客户姓名\',address varchar(256) comment \'客户地址\',email varchar(64) unique key comment \'电子邮箱\',sex enum(\'男\',\'女\') not null comment \'性别\',card_id char(18) unique key comment \'身份证\');-- 购买表create table if not exists purchase(order_id int primary key auto_increment comment \'订单号\',customer_id int comment \'客户编号\',goods_id int comment \'商品编号\',nums int default 0 comment \'购买数量\',foreign key (customer_id) references customer(customer_id),foreign key (goods_id) references goods(goods_id));

分析

理论上，如果不创建外键约束，直接建立学生表和班级表，虽然表中所需的字段都有，但在实际使用中可能会出现问题。例如，可能会出现插入的学生信息中包含一个具体的班级，但该班级在班级表中并不存在的情况。这显然是不符合业务逻辑的。

因为此时两张表在业务上是相关的，但没有建立约束关系，所以可能会出现不符合业务逻辑的数据。解决方案是通过外键来完成。建立外键的本质是将相关性交给MySQL去审核，提前告诉MySQL表之间的约束关系。这样，当用户尝试插入不符合业务逻辑的数据时，MySQL会阻止插入操作。