varchar(20) 装得下手机号,但它不保证手机号不重复;int 装得下年龄,但它不保证年龄不是负数。数据类型约束的是值的范围,约束(constraint)约束的是值在业务上是否成立。MySQL 能加在表和列上的约束不多:

约束写在哪管什么
not null列定义这一列不许出现 NULL
default列定义插入时没写这一列,用什么值补
comment列定义只保存说明文字,不参与校验
zerofill列定义只改变整数查询结果的显示宽度
primary key列定义或表定义唯一且非空,一张表最多一个
auto_increment列定义不赋值时由计数器生成整数
unique [key]列定义或表定义值唯一,但允许多个 NULL
foreign key表定义子表的引用值必须在父表存在
check列定义或表定义本行内满足一个布尔表达式

下面这些示例都在同一个演示库里执行:

create database if not exists constraint_demo default character set utf8mb4;
use constraint_demo;

空值与默认值:not null 拒绝 NULL,default 只在省略时补位

列不写 not null 就允许 NULL,这是 MySQL 的默认。NULL 不是 0,也不是空字符串,它表示“没有值”:

select null;        -- NULL
select 1 + null;    -- NULL
select null = null; -- NULL,不是 1

NULL 参与算术、比较、字符串拼接,结果基本都是 NULL,这一点会一路影响后面的查询、聚合和唯一性检查。

课件用班级表说明非空的意义:班级没有名字,不知道是哪个班;教室没填,不知道在哪上课。两列都写 not null 之后,只插入班级名会被拒绝:

create table classroom (
    class_name varchar(20) not null,
    class_room varchar(10) not null
);
 
insert into classroom (class_name) values ('class1');
-- ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

1364 说的不是“你传了空字符串”,而是“这一列既不许 NULL,又没给默认值,你又没提供,没法替你补”。如果显式写 values ('class1', null),报的是另一条:

ERROR 1048 (23000): Column 'class_room' cannot be null

两条错误值得分开记:1364 是“缺值且无默认”,1048 是“明确给了 NULL”。另外,默认的严格模式(STRICT_TRANS_TABLES,5.7 起默认开启)才会让缺值直接报错;关掉严格模式后,MySQL 会补一个隐式默认值(数值补 0、字符串补空串)并只给警告,数据是插进去了,但不是你想要的。

default 解决的是另一件事:某个值经常出现,可以在定义时写死,插入时省掉这一列:

create table tt10 (
    name varchar(20) not null,
    age  tinyint unsigned default 0,
    sex  char(2) default '男'
);
 
insert into tt10 (name) values ('zhangsan');
select * from tt10;
-- zhangsan | 0 | 男

default 的触发条件只有一个:INSERT 的列清单里没有这一列。如果列在清单里,即使给的是 NULL,默认值也不会出来:

insert into tt10 (name, age) values ('lisi', null);
-- age 存进去就是 NULL,不是 0

age 允许 NULL,所以 MySQL 接受。要把“省略时补 0、写了 NULL 也不行”两件事同时做到,定义成 age tinyint unsigned not null default 0。

课件这里有两句话需要改。

第一句是“只有设置了 default 的列,才可以在插入值的时候,对列进行省略”。不对。可空列即使没有 default 也能省略,MySQL 会隐式填 NULL;真正不能省略的是“not null 且没有 default”的列,省略就报 1364。这条规则在 MySQL 手册的“Implicit Defaults”一节写得很清楚:可空列缺省时取 NULL,非空列缺省时取数据类型的隐式默认值,严格模式下则报错。

第二句是“not null 和 default 一般不需要同时出现”。恰好相反。状态列、计数列最常见的就是 not null default 0:非空保证这一列永远有值,默认值保证调用方不写也不会失败。只写 default 不写 not null,别人仍然可以塞进一个 NULL。

default 不只能跟常量。datetime 列可以写 default current_timestamp,从 8.0.13 起还允许把表达式放进括号,例如 default (uuid())。

comment 和 zerofill:一个给建表语句看,一个给查询结果看

comment 不参与任何校验,只是跟着建表语句一起存下来:

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

desc tt12 看不到注释,show create table tt12\G 才能看到。列注释上限是 1024 个字符,够写清单位、范围和业务含义;“单价,单位分”这五个字能省掉后面无数次 199 和 19900 的争论。

zerofill 更容易误会。int(10) 里的 10 不是字节数,int 恒定 4 字节,这个数字是显示宽度:

create table tt3 (a int unsigned, b int unsigned);
insert into tt3 values (1, 2);
 
alter table tt3 change a a int(5) unsigned zerofill;
select * from tt3;
-- 00001 | 2
select a, hex(a) from tt3;
-- 00001 | 1

hex(a) 读出来还是 1,说明库里存的就是 1,00001 只是格式化后的显示。注意 zerofill 会顺带把列变成无符号,负数不能再存。

从 MySQL 8.0.17 起,整数类型的显示宽度和 ZEROFILL 都被标记为废弃。新表不该再用它凑固定位数;要 00001 这种编号,存字符串、在应用层补零,语义更干净。

主键与自增:唯一非空,计数器只往前

primary key 同时做两件事:值不能重复,值不能为 NULL。即使列定义里没写 not null,desc 出来的 Null 列也是 NO:

create table tt13 (
    id   int unsigned primary key comment '学号不能为空',
    name varchar(20) not null
);
 
insert into tt13 values (1, 'aaa');
insert into tt13 values (1, 'aaa');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当表创建好以后但是没有主键的时候,可以事后追加主键,也可以删掉。下面用一张单独的表演示,避免和 tt13 已有的主键冲突:

create table tt13_late (
    id   int unsigned not null,
    name varchar(20) not null
);
 
alter table tt13_late add primary key (id);
alter table tt13_late drop primary key;

“一张表最多一个主键”里的“一个”,可以是一组列。在字段列表之后写 primary key(列, 列) 就是复合主键:

create table tt14 (
    id     int unsigned,
    course char(10) comment '课程代码',
    score  tinyint unsigned default 60 comment '成绩',
    primary key (id, course)
);
 
insert into tt14 (id, course) values (1, '123');
insert into tt14 (id, course) values (1, '123');
-- ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY'

判重看的是组合。(1, '123') 重复,(1, '456') 和 (2, '123') 都能进。复合主键的每一列都隐式非空,(1, null) 插不进来。

InnoDB 的主键还有一层物理含义:表数据按主键组织成聚簇索引(clustered index),主键值直接决定行的存放位置。所以主键最好选与业务无关、短且稳定的列;用身份证号、邮箱做主键,业务一变就要动整张表的组织方式。课件建议“主键设计成和当前业务无关的字段”,说的就是这个。

自增通常和主键搭配,作为行的逻辑编号:

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

自增有四个前提:列必须是整数,必须被索引(key 一栏有值),一张表最多一个自增列,而且不能带 default。少一条,建表就报错。

课件在这里顺带解释了索引:索引是按一列或多列的值排好序的独立存储结构,条目里存着指向表中数据页的指针,作用类似图书目录,能按值快速定位行。自增列必须被索引,是 MySQL 对自增字段的硬性要求;索引本身解决的是“按值找行”的速度。

课件把自增描述成“取当前字段中已经有的最大值 +1”。这只在从没删过行、也没手动插过大值的时候成立。InnoDB 维护的是一个独立计数器,删行不会让它回退:

delete from tt21 where id = 2;
insert into tt21 (name) values ('c');   -- 拿到 3,不是 2

MySQL 8.0 起这个计数器还会持久化到磁盘,重启也不回退。手动插入一个更大的 id 会把计数器顶上去,之后再自增就从那个值往后走;把 auto_increment 调小到当前值以下会被忽略,目的就是不发重复编号。

插入之后要拿编号,用 last_insert_id():

-- 如果紧接着插入 a、b 执行
select last_insert_id();   -- 2

它绑定当前连接,别的连接插多少都不影响;一次插入多行时返回的是这一批的第一条。课件在两次单行插入之后显示 last_insert_id() 为 1,按语义应该是 2——第二次插入生成的自增值才是最近的那个。

唯一键:可以有多个,可以多个 NULL

一张表只能有一个主键,但需要唯一的列往往不止一个,剩下的交给 unique:

create table student (
    id   char(10) unique comment '学号,不能重复,但可以为空',
    name varchar(10)
);
 
insert into student values ('01', 'aaa');
insert into student values ('01', 'bbb');
-- ERROR 1062 (23000): Duplicate entry '01' for key 'id'
insert into student values (null, 'bbb');   -- 成功

唯一键允许 NULL,而且允许多个 NULL。原因是唯一性检查建立在“两个值相等”上,而 NULL = NULL 的结果是 NULL,不是真,检查就跳过了。这也意味着“手机号必须唯一、但注册时可以先不填”这种需求,唯一键正好;换成主键就要求每一行都得有手机号。

唯一键底下就是一个唯一索引,show index from student 里 Non_unique = 0 的那一行就是它。并发注册时,“先查再插”会在两步之间留出空档,直接插、让其中一个请求拿到 1062,检查和写入合成一步,反而更稳。

主键和唯一键的定位,课件用员工表解释过:身份证号做主键,员工工号做唯一键。身份证号标识“这个人”,工号是公司内部编号;两者都要求不重复,但工号会随公司和制度变化,身份证号不会。主键选稳定的那个,唯一键管业务字段,区别不在“能不能为空”这一条,而在谁是行的身份。

外键:把两张表的关系交给数据库审

学生表的 class_id 写了 102,而班级表里只有 100 和 101。没有外键时,这种孤儿数据只能靠代码检查;有了外键,数据库在插入时就拒绝:

学生表 stu 的 class_id 与班级表 myclass 的 id 一一对应;图下的文字说明把班级信息重复写进每个学生行会造成冗余,外键把这条关系抽成一次引用

create table myclass (
    id   int primary key,
    name varchar(30) not null comment '班级名'
);
 
create table stu (
    id       int primary key,
    name     varchar(30) not null comment '学生名',
    class_id int,
    constraint fk_stu_class foreign key (class_id) references myclass(id)
);
 
insert into myclass values (10, 'C++大牛班'), (20, 'java大神班');
insert into stu values (100, '张三', 10), (101, '李四', 20);
insert into stu values (102, 'wangwu', 30);
-- ERROR 1452 (23000): Cannot add or update a child row:
-- a foreign key constraint fails
insert into stu values (102, 'wangwu', null);   -- 成功,表示暂未分班

四条规则:

  • 被引用的父列必须先有索引,最常见的索引就是主键或唯一键;没有索引时建外键报 1215 Cannot add foreign key constraint,数据库没法高效判断“这个值存不存在”。
  • 子表的外键列可以为 NULL,表示“暂时没有引用”,NULL 不拿去父表比对。
  • 父列和子列的类型、字符集要匹配,不匹配连建表都过不去。
  • 只有 InnoDB 真正检查外键。MyISAM 接受语法,然后当没看见,这也是老库换引擎后突然冒出一堆 1452 的原因。

外键还可以规定父行被删、被改时子表怎么办:

动作删父行改父键
restrict / no action拒绝,报 1451拒绝
cascade连带删掉子行子列跟着改成新值
set null子列置 NULL,列必须可空子列置 NULL
set defaultMySQL 解析但 InnoDB 不支持同左

默认是 restrict。级联很方便,也危险:删一个班级会连带删掉整班学生,报表上不会提示任何异常。入门阶段先把默认的 restrict 用熟,确实需要级联时再显式写 on delete cascade 或 on delete set null。

给外键起名字不是为了好看。不写名字时 MySQL 会生成 stu_ibfk_1 这种默认名,报错日志里出现它,得回去查建表语句才知道是哪条关系;写成 constraint fk_stu_class foreign key (class_id) references myclass(id),名字和关系一眼能对上,删除时也不用去猜。

check 与约束的增删:8.0.16 是分界线

非空、唯一、外键都只表达某一类规则,“购买数量不能为负”没有现成约束可用,得写 check:

create table nums_demo (
    id   int primary key auto_increment,
    nums int not null default 0,
    constraint chk_nonneg check (nums >= 0)
);
 
insert into nums_demo (nums) values (-1);

版本分界线在这里:MySQL 8.0.16 之前,CREATE TABLE 里的 CHECK 只是被解析然后丢掉,写了等于没写;8.0.16 起才真正对所有存储引擎生效,违反时报:

ERROR 3819 (HY000): Check constraint 'chk_nonneg' is violated.

check 的判定按 SQL 三值逻辑走:表达式结果为 TRUE 或 UNKNOWN 都算通过,只有 FALSE 才拒绝。在允许 NULL 的列上,nums >= 0 对 NULL 得到 UNKNOWN,这一行能插进去;本表配了 not null,所以先把 NULL 挡在门外。

它的能力边界也很清楚:不能引用别的表,不能带子查询,不能用 now() 这类非确定性函数,只认本行自己的列。跨表的规则还是外键,或者应用层的事务。8.0.16 还允许在 check 后声明 NOT ENFORCED,写了但不检查,给迁移期留了个口子。

约束不是一次写死的,可以后续增删。加约束基本都能用 alter table:

create table stu2 (
    id       int primary key,
    class_id int
);
 
alter table student add unique key uk_name (name);
alter table stu2 add constraint fk_stu2_class
    foreign key (class_id) references myclass(id);
alter table nums_demo add constraint chk_upper check (nums <= 1000);

删的时候,不同约束走不同子句,不能一律 drop constraint:

alter table stu2 drop foreign key fk_stu2_class;  -- 必须给约束名,不是列名
alter table student drop index uk_name;           -- 唯一键按索引名删
alter table tt14 drop primary key;                -- 主键不用名字
alter table nums_demo drop check chk_upper;       -- 8.0.19 起也可写 drop constraint

唯一键的索引名默认和列名相同;建表时写了 constraint uk_name unique (name),删除时用的仍是 uk_name。外键的约束名和外键列名经常不一样,drop foreign key class_id 会直接报错。

改已有列的约束用 modify 或 change,它会替换整列定义,没写全的属性会回到默认;只改默认值可以用 alter column 列名 set default 值 和 alter column 列名 drop default:

alter table tt12 modify age tinyint unsigned not null default 0;
alter table tt12 alter column sex set default '女';
alter table tt12 alter column sex drop default;

给老表补约束的顺序永远是“先洗数据,再加规则”。列里已有 NULL 还去加非空,报 1138 Invalid use of NULL value;已有重复值还去加唯一键,报 1062;已有孤儿行还去加外键,报 1452。约束检查不分新数据老数据,表里躺着什么,它就得先过一遍。大表上 alter table 还可能重建表,挑低峰做,比事后回滚便宜。

一份能跑的商店库

课件末尾的综合案例把前面这些约束拼在一起。下面这份可以直接执行,商品单价按“分”存:

create database if not exists bitmall default character set utf8mb4;
use bitmall;
 
create table 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 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 purchase (
    order_id    int primary key auto_increment comment '订单号',
    customer_id int comment '客户编号',
    goods_id    int comment '商品编号',
    nums        int default 0 comment '购买数量',
    constraint fk_purchase_customer
        foreign key (customer_id) references customer(customer_id)
        on delete restrict on update cascade,
    constraint fk_purchase_goods
        foreign key (goods_id) references goods(goods_id)
        on delete restrict on update cascade,
    constraint chk_nums check (nums >= 0)
);
 
insert into goods (goods_name, unitprice, category, provider) values
('机械键盘', 29900, '外设', '比特外设'),
('显示器',   89900, '显示', '比特显示');
 
insert into customer (name, address, email, sex, card_id) values
('张三', '北京', 'zhangsan@example.com', '男', '110101199001011234'),
('李四', '上海', 'lisi@example.com',     '女', '310101199202022345');
 
insert into purchase (customer_id, goods_id, nums) values
(1, 1, 2),
(2, 2, 1);

purchase 上的三个约束各自挡一类错误:客户号不存在报 1452,商品号不存在报 1452,数量为负报 3819。建完用 show create table purchase\G 看一眼,约束名都在;后面要删外键时,靠的就是这些名字。