企业官网建设流程全解析

5.MySQL表的约束（上）

上一篇笔记确立了一个贯穿全库的认识：列的类型本身就是约束，越界的数据进不来。但类型约束只管"值符不符合类型格式"，管不了"这个值合不合业务规则"——学号列可以存下 QQ 号，email 列可以出现两份相同的邮箱，数据类型对此一概不拦。

本篇正式展开约束体系：先讲清楚约束为什么存在，再逐个讲解空属性（null/not null）、默认值（default）、列描述（comment）、zerofill、主键（含复合主键）五种表级约束。自增长（auto_increment）、唯一键（unique key）与外键（foreign key）属于下一篇，本篇结尾只作预告。

一、为什么需要约束

上篇验证过"类型即约束"：tinyint 列插 128 报 ERROR 1264，char(2) 存 3 个字符报 Data too long——数据类型拦截了格式非法与范围越界的数据。但数据类型给出的约束很单一：它只能保证"存得下、格式对"，保证不了数据的业务合法性。学号是 varchar 列，QQ 号、邮箱字符串一样存得进去；性别是 varchar 列，身高、体重、籍贯的文本同样来者不拒。把数据写成什么、是不是业务上允许的值，类型约束看不到，需要额外的表级约束来把关。

约束是什么：把"写数据"从自由变成有规则

类比写代码：编译器会在语法层拦住写错的代码，语法不过就不允许编译通过，倒逼程序员写出语法正确的程序。数据库的表约束扮演同样的角色——约束的本质，是 MySQL 通过技术手段限定某一列允许出现的数据，让不合规则的数据根本插不进去：

1. 约束的对象是"想插入数据的人"。用户插数据时要么插合法的数据，要么插非法数据；约束保证非法数据一律被拦截，只有合法数据能进表。
2. 约束的效果是可预期性。声明了 tinyint unsigned 的列，未来插进库里的数据一定落在 0 到 255 之间；声明了主键的列，未来一定不会出现重复值。表结构设计者可以在任何数据插入之前先定好规则，之后入库的每一行都必然符合这些规则。
3. 库里的数据因此完整、可信。

约束总览

MySQL 常见表级约束共八种，本篇讲解前五种：

二、空属性约束：null 与 not null

先厘清 NULL 在 MySQL 中的含义。在 C/C++ 里 null 表示零或空指针，在 MySQL 里 NULL 表示"没有、不存在"；它与空字符串是两回事——空串 ‘’ 是一个长度为 0 的字符串，属于真实存在的数据，而 NULL 是什么都没有。MySQL 里字符串用单引号或双引号都可以，习惯上写单引号，''就是合法的空串数据。

NULL 一般不参与运算，任何数据与 NULL 运算的结果还是 NULL：

mysql> select null; +------+ | NULL | +------+ | NULL | +------+ mysql> select 1+null; +--------+ | 1+null | +--------+ | NULL | +--------+

数据库默认允许字段为空（Nullable），但实际开发时尽可能让字段 not null，因为数据为空就无法参与运算，业务上难以处理。空属性约束就是给某一列声明"允许为空（null，默认）“或"不允许为空（not null）”：

1. 不写约束，默认允许为空，插入时可以省略该列或显式插 NULL。
2. 声明 not null 后，该列必须给出实际数据，省略或插 NULL 都会被拦。

典型的业务场景：班级表里"班级名称"与"教室"都不该为空——班级没名字就不知道自己在哪个班，教室为空就不知道去哪上课，这两列必须建表时就锁死（myclass 表）：

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

desc 输出中Null 一栏就是空属性的直接体现：NO 表示该列不允许为空。插入时只给班级名、省略教室这一列，MySQL 直接拒绝：

mysql> insert into myclass(class_name) values('class1'); ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

注意这条报错的口径是"没有默认值"而不是"不能为空"——省略列与显式插 NULL 是两种不同的违规，报错也不同，下一节专门对比。若显式插入 NULL，拦截报错则是 cannot be null 一类（ERROR 1048）。只要声明了 not null，任何人向这两列插入空值的企图都会被 MySQL 拦住；想成功插入，就必须把班级名称和教室都填上。

三、默认值约束：default

default 约束给某一列指定一个默认值：插入时用户给了值就用用户的值，省略该列就用默认值填充。它解决的是"某个值经常固定出现、不该每次重复输入"的问题——比如注册表单里性别默认设为"男"，用户填了就按用户填的存，没填就存默认值。

案例：age 默认 0，sex 默认 ‘男’，只有 name 是必填的：

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> insert into tt10(name) values('zhangsan'); -- 只给 name，省略 age 与 sex Query OK, 1 row affected (0.00 sec) mysql> select * from tt10; +----------+------+------+ | name | age | sex | +----------+------+------+ | zhangsan | 0 | 男 | +----------+------+------+ mysql> show create table tt10\G; *************************** 1. row *************************** Table: tt10 Create Table: CREATE TABLE `tt10` ( `name` varchar(20) NOT NULL, `age` tinyint unsigned DEFAULT '0', `sex` char(2) DEFAULT '男' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec)

省略某列时，MySQL 看的是这一列有没有"可用的默认值"：设置了 default 的列，省略时自动用默认值补上；没设 default 但可空的列，MySQL 会自动补上 DEFAULT NULL，省略时存 NULL、不报错。反过来，没有 default 又声明了 not null 的列（上节的 class_room），省略即报 ERROR 1364。

default 与 not null：不冲突，各管一段

用户对某一列的插入只有两种形态：显式给出值，或省略该列。两种约束分别看守这两种形态，不冲突、互相补充：

1. not null 管"显式给值"：用户给的值不能是 NULL，必须是合法数据（哪怕是空串 ‘’ 也算合法数据，因为空串是真实存在的值）。
2. default 管"省略该列"（准确说，管的是省略时有没有可用的默认值）：设置了 default，省略时用默认值填充；没设 default 的可空列被 MySQL 自动补上 DEFAULT NULL，省略时存 NULL、不报错；只有 not null 且无 default 的列，省略才报 ERROR 1364（Field doesn’t have a default value）。
3. 两种约束可以同时设置：显式插入时 NULL 照样被 not null 拦下；省略该列时 default 值不为空，插入照常成功。建表时只写 not null 不写 default，MySQL 不会替该列补 default——会被自动补 DEFAULT NULL 的只有可空列。

注：以上报错行为以 MySQL 默认的严格模式（sql_mode 含 STRICT_TRANS_TABLES，5.7/8.0 默认开启）为前提；若关闭严格模式，not null 且无 default 的列被省略或显式插 NULL 都不报错，而是回退为该类型的隐式默认值（数值列补 0、字符串列补空串 ‘’）并产生一条 warning。

回到上一节的报错就豁然开朗：myclass 的 class_room 是 not null 且无 default，用户省略该列，落到了 default 的管辖范围——没有默认值可用，报错"doesn’t have a default value"；若用户显式插 NULL，则落到 not null 的管辖范围，报错"cannot be null"。两种报错对应两种不同的违规，看报错文案即可区分是哪个约束在拦截。

四、列描述：comment

comment 是加在列定义后面的注释文字，用来描述这一列的语义，专供 DBA(Database Administrator，数据库管理员) 与维护表的程序员阅读。它不参与任何数据校验——数据不符合 comment 描述的语义也不会被拦截，因此被称为"软约束"。作用相当于代码注释：性别列写上 comment 后，后来人一看便知这里只能放男或女。

tt12 案例，建表时给每列挂上注释：

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

comment 不会出现在 desc 输出里，desc 只看得到列名、类型、是否为空这些结构信息：

mysql> desc tt12; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | 0 | | | sex | char(2) | YES | | 男 | | +-------+------------------+------+-----+---------+-------+

注释信息保存在建表语句里，要用 show create table 才能看到：

mysql> show create table tt12\G *************************** 1. row *************************** Table: tt12 Create Table: CREATE TABLE `tt12` ( `name` varchar(20) NOT NULL COMMENT '姓名', `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄', `sex` char(2) DEFAULT '男' COMMENT '性别' ) -- 其余建表参数从略

建表时给每个字段写一句 comment 是好习惯：表结构越复杂、字段含义越不直观，注释的价值越大；desc 看不见它，但表结构导出、迁移、交接时它都跟着走。

五、zerofill：数字显示宽度的补零

上篇遗留过一个疑问：desc 与 show create table 里整型列显示成 int(11)、int(10) 一类带圆括号的样式，这个数字是什么？先看事实——建表时不写这个数字，MySQL 会自动补一个（tt3 表，a、b 都是无符号 int）：

mysql> create table tt3 (a int unsigned, b int unsigned); Query OK, 0 rows affected (0.00 sec) mysql> show create table tt3\G *************************** 1. row *************************** Create Table: CREATE TABLE `tt3` ( `a` int(10) unsigned DEFAULT NULL, `b` int(10) unsigned DEFAULT NULL ) -- 其余建表参数从略 mysql> insert into tt3 values(1, 2); mysql> select * from tt3; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+

圆括号里的数字是"显示宽度"，与取值范围无关——int 就是 4 字节、范围由有无符号决定，括号里的 10 不改变存储。没有 zerofill 属性时，这个宽度数字毫无意义，显示的只是数据本身。默认宽度是这样定的：int 能表示的最大值十进制是 10 位（无符号 4294967295），有符号还要为负号预留 1 位，所以无符号默认补 int(10)，有符号默认补 int(11)。

给列加上 zerofill 属性后，显示宽度才开始起作用（把 a 改为宽度 5 的无符号 zerofill 列）：

mysql> alter table tt3 change a a int(5) unsigned zerofill; Query OK, 0 rows affected (0.00 sec) mysql> show create table tt3\G Create Table: CREATE TABLE `tt3` ( `a` int(5) unsigned zerofill DEFAULT NULL, `b` int(10) unsigned DEFAULT NULL ) mysql> select * from tt3; +-------+------+ | a | b | +-------+------+ | 00001 | 2 | +-------+------+

zerofill 的含义：显示时若数值位数不足声明宽度，前面自动补 0 到满宽；位数超过声明宽度则按实际数值原样显示。原来的 1 变成了 00001。这一约束的典型场景是编号列：全校 100 个班要显示成三位编号，就声明 int(3) zerofill，让 001、002 一路排到 100。

必须强调：补零只发生在显示层，存储与计算的值不变。证明方法是用 hex 函数把列值按十六进制打出来：

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

内部存的还是 1，00001 只是格式化输出；同样，用where b=200这类数值条件筛选 zerofill 列，比较的仍是数值本身。zerofill 的显示是"等宽"的，代价仅是展示层格式化，不影响任何运算与查询。

六、主键：primary key

主键是表中用来唯一标识一行记录的列：它的值不能重复、不能为空。生活中对应的概念是学号——每个学生一个学号，靠学号能定位到这个学生的全部信息，学号不会与任何人冲突。主键所在列通常是整数类型，方便后续配合自增长使用（下篇内容）。

数据库场景里，主键的意义与 C++ 关联容器中 key 的意义一致：key 唯一，按 key 可以快速定位到对应的记录，便于对某一行做精准的增删查改，具体可对照 C++ 关联式容器 map、set 详解 中 map 的按键查找理解。

单列主键的用法

建表时直接在列定义后面跟 primary key 关键字（tt13 表，id 即主键）：

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 | | | name | varchar(20) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+

desc 输出里Key 一栏的 PRI 标明主键列。注意 id 明明只写了 primary key、没写 not null，Null 一栏却是 NO——主键约束隐含"不能为空"，MySQL 会自动为主键列补上 not null。

插入数据验证主键的唯一性：

mysql> insert into tt13 values(1, 'aaa'); Query OK, 1 row affected (0.00 sec) mysql> insert into tt13 values(1, 'aaa'); -- 主键值 1 已存在，重复插入 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

主键冲突时 MySQL 直接拦截，报 ERROR 1062 Duplicate entry。重复的主键数据永远进不了表，库中每一行的主键值必然互不相同。

表建好之后追加或删除主键

主键不一定要在建表时指定，之后仍可调整：

1. 追加主键：alter table 表名 add primary key(字段列表)，括号里写明要把哪一列设为主键。前提是这一列现有数据没有重复——若表里已有重复值，追加时报同样的 Duplicate entry 错误，必须先清理重复数据再设主键。
2. 删除主键：alter table 表名 drop primary key。因为一张表只能有一个主键，删除时不用告诉 MySQL 删哪一列，写 drop primary key 即可。

主键最好在建表时就定好。表使用一段时间、塞满了数据之后才想起来加主键，若目标列存在重复记录，就面临"删哪一行都不合适"的取舍，删除任何用户数据都是代价。

复合主键

注意措辞区分：一张表只能有一个主键，不代表主键只能由一列构成；多列合起来充当一个主键时，称为复合主键。

选课场景是复合主键的典型：一张选课记录表保存"哪个学生选了哪门课、考了多少分"，同一个学生可以选多门课、同一门课可以被多个学生选，但(学生, 课程)这个组合不能重复出现——同一个学生把同一门课选两次就是重复记录。这种"单列允许重复、组合必须唯一"的约束，单列主键做不到，复合主键正合适（tt14 表，id 与 course 合起来当主键）：

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.00 sec) mysql> desc tt14; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | course | char(10) | NO | PRI | NULL | | | score | tinyint(3) unsigned | YES | | 60 | | +--------+---------------------+------+-----+---------+-------+

desc 输出里 id 与 course 的 Key 栏都是 PRI，两个 PRI 并不代表两个主键，而是"两者都是同一个主键的组成部分"。验证约束行为：

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'); -- 组合 (1, '123') 已存在 ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 报错把组合值连成整体显示

复合主键把多列值视为一个整体来比较唯一性：只要组合与历史记录不完全相同，就能插入；组合整体与历史记录完全相同时才触发主键冲突。

总结

本篇回答了三个层次的问题。

为什么需要约束：类型约束只保证格式合法，业务合法性要靠表级约束把关，数据库是数据入库前的最后一道防线，约束越严格，库中数据越完整、可预期。

单个列的插入规则：not null 看守显式插入的值（NULL 进不来，报 cannot be null），default 看守省略的列（有默认值就填充；无默认值时，可空列省略存 NULL，not null 列省略才报 doesn’t have a default value），两者各管一段、互不冲突；comment 不拦数据，只是写给 DBA 和维护者看的列说明；zerofill 只改显示不改存储，声明宽度不足补零、超过原样，默认宽度无符号为 10、有符号为 11（预留负号位）。

行级别的约束：主键唯一标识一行，隐含非空，冲突报 ERROR 1062，一张表至多一个主键但可以由多列合成，复合主键把组合值当整体比较唯一性，适合"单列可重复、组合必须唯一"的业务。

下一篇继续约束体系的后半程：自增长 auto_increment（主键的子话题，让主键值自动递增）、唯一键 unique key（允许为空的多列唯一约束）与外键 foreign key（表与表之间的引用关系）。