一文吃透 MySQL 表约束：类型、用法、示例与最佳实践_Mysql

非空约束 not null

create table myclass(
    class_name varchar(20) not null,
    class_room varchar(20) not null
);

补充几个新手容易懵的点：

not null 只表示“不能是 null”，空字符串 '' 不算 null，所以 '' 可以插进去。
如果这一列既 not null 又没有 default，插入时不给它值，mysql 会直接翻脸报错。
建表后也能加：

alter table myclass modify class_name varchar(20) not null;

default 约束

create table tt10(
    name varchar(20) not null,
    age tinyint unsigned default 18,
    gender char(1) default '男'
);

create table t11(
    name varchar(20) not null,
    age tinyint default 18,
    gender char(1) not null default '男'
);

补充：

insert into t11(name) values('张三'); 时，age 用 18，gender 用 '男'。
如果 insert into t11() values();，会失败，因为 name 是 not null 又没默认值。
default 和 not null 不是敌人，是搭档：一个管“不能不填”，一个管“不填时填啥”。

列描述 comment

create table t12(
    name varchar(20) not null comment '这个是用户的用户名',
    age tinyint unsigned default 18 comment '这个是用户年龄',
    gender char(1) default '男' comment '这个是用户性别'
);

补充：

comment 不是约束，删了也不影响数据，它更像是给管理员和未来的自己看的备注。
除了 show create table t12;，还可以：

show full columns from t12;

zerofill 约束

create table test_zerofill (
    id int(6) zerofill primary key,
    num tinyint(4) zerofill
);

补充：

int(6) 的 6 不是存储上限，只是显示宽度，而且只有配 zerofill 才明显有用。
插入 1，可能显示成 000001。
zerofill 会隐式变成 unsigned，所以不能存负数。
mysql 8.0.17 后官方已经不推荐 zerofill，新项目别用，但面试题里偶尔还会冒出来吓人。

主键约束

create table student (
    id int primary key,
    name varchar(20)
);

一句话：primary key = unique + not null。

补充：

一张表最多一个主键，但主键可以是多个字段一起组成，叫联合主键。
innodb 里，主键就是聚簇索引。
如果没有主键，innodb 会找第一个非空唯一索引当主键；找不到就偷偷生成一个隐藏主键。
业务字段如手机号、身份证号，尽量别做主键，又长又可能变，推荐自增 int。

建表后加主键：

alter table student add primary key(id);

删除主键时注意：如果主键带 auto_increment，要先去掉自增，不然可能报错。

alter table student modify id int;
alter table student drop primary key;

自增主键常用写法：

create table student (
    id int primary key auto_increment,
    name varchar(20)
);

补充：自增不保证连续，删掉几行后不会自动回填。可以指定起始值：

alter table student auto_increment = 100;

联合主键：

create table score (
    student_id int,
    course_id int,
    score int,
    primary key (student_id, course_id)
);

唯一键 unique

-- 列级定义
create table user(
    id int primary key auto_increment,
    email varchar(50) unique,
    phone varchar(20)
);
-- 表级定义，支持复合唯一键
create table user(
    id int primary key auto_increment,
    username varchar(30),
    phone varchar(20),
    constraint uk_name_phone unique (username, phone)
);
-- 已存在的表加唯一约束
alter table user add constraint uk_email unique(email);
-- 删除唯一约束，本质是删对应索引
alter table user drop index uk_email;

补充：

唯一键可以有多个。
唯一键允许 null，而且多个 null 不冲突，因为 null 不等于 null。
底层就是唯一索引，所以 show index from user; 能看到。

特性	主键 primary key	唯一键 unique
数量	一张表只能 1 个	可以多个
null	不允许	允许，可多条 null
索引	innodb 聚簇索引	普通唯一二级索引
作用	唯一标识一行	保证业务字段不重复

外键 foreign key

外键就是两张表之间的“绳子”：子表引用主表的主键或唯一键。

create table department(
    dept_id int primary key auto_increment,
    dept_name varchar(30) not null
);
create table employee(
    emp_id int primary key auto_increment,
    emp_name varchar(30) not null,
    dept_id int,
    constraint fk_emp_dept foreign key(dept_id) references department(dept_id)
);

规则补充：

被引用的列必须是主键或唯一键。
类型要匹配，int 对 int，varchar 长度尽量一致。
mysql 里只有 innodb 支持外键，myisam 不支持。
插入：先插主表，再插子表。
删除：先删子表，再删主表。
可以加级联操作：

constraint fk_emp_dept foreign key(dept_id) references department(dept_id)
on delete cascade
on update cascade

意思就是：主表删，子表跟着删；主表改，子表跟着改。默认是 restrict，不让乱删。

删除外键：

alter table employee drop foreign key fk_emp_dept;

忘名字就：

show create table employee;

实际开发小提示：互联网项目经常不建物理外键，靠代码层保证，因为外键有性能和管理开销。学习阶段先把原理搞懂，面试能说清楚就行。

补一个：check 约束

mysql 8.0.16 之后开始真正支持：

create table person(
    age int check (age >= 0 and age <= 150)
);

8.0.16 之前写是能写，但会被忽略，属于“写了白写”。

最后一句总结

数据库约束就像家规：

not null：这里不许空。
default：你不填，我给你兜底。
comment：给管理员看的备注。
zerofill：显示补零，老版本玩具，新项目别碰。
primary key：身份证，唯一且非空。
unique：业务唯一，可多个，可 null。
foreign key：两张表的绳子，先主后子。
check：条件必须满足。

刚开始记不住很正常，写多了就会形成条件反射：哦，这里不能空；哦，这里不能重复；哦，删主表前得先看子表。一年正是踩坑长记性的黄金期，稳住。

到此这篇关于一文吃透 mysql 表约束：类型、用法、示例与最佳实践的文章就介绍到这了,更多相关mysql表的约束内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

一文吃透 MySQL 表约束：类型、用法、示例与最佳实践

非空约束 not null

default 约束

列描述 comment

zerofill 约束

主键约束

唯一键 unique

外键 foreign key

补一个：check 约束

最后一句总结

推荐阅读

MySQL DCL、函数与约束详解( 安全、效率、完整性的三板斧)

MySQL批量更新多条记录不同值的最佳实践教学

MySQL通关指南之从索引到优化的硬核知识

MySQL复合查询深入解析及实战案例

MySQL数据库批量清理前置防御的完整指南

MySQL连接报错1045的原因排查与解决方法详解

猜你喜欢

发表评论