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,不是 1NULL 参与算术、比较、字符串拼接,结果基本都是 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 value1364 说的不是“你传了空字符串”,而是“这一列既不许 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,不是 0age 允许 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 | 1hex(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,不是 2MySQL 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。没有外键时,这种孤儿数据只能靠代码检查;有了外键,数据库在插入时就拒绝:

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 default | MySQL 解析但 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 看一眼,约束名都在;后面要删外键时,靠的就是这些名字。
还没有评论 — 第一条由你来留。