干数据库这一行建表的时候偷懒一时后面填坑就填一年。很多新手学MySQLCRUD写得很溜一到约束就犯迷糊什么时候用UNIQUE什么时候用主键NULL和空字符串到底差在哪为什么把手机号设成唯一约束之后还是能插进重复的NULL……今天这篇专门聊MySQL里最常用的四类约束非空、唯一、默认值和主键。这是约束系列的第一篇外键和CHECK我会放到下一篇展开。先说明白这篇不是让你背语法的而是让你看完之后能直接在自己的项目里用对、用稳。文章会围绕一张用户表从头建到尾每一步都会解释为什么这么设计表结构已经存在的话怎么补约束以及实际踩坑之后怎么排查。无论是刚入门的学生还是写了两三年业务开发的工程师这几页东西都值得过一遍。1. 约束到底是什么数据完整性从哪来约束说白了就是数据库层面的“规矩”。你不想让用户填一个空手机号注册不想让订单号出现两条一模一样的记录不想让员工年龄填个负数——业务代码里当然可以写一堆if判断但代码是人写的总会有漏网之鱼。把规则直接钉在表结构上数据库会帮你在写入的那一刻把关不合格的数据根本进不去。这个“把关”的背后有一个概念叫数据完整性。完整性分三类实体完整性、域完整性、引用完整性。实体完整性要求每行数据能被唯一标识靠主键解决域完整性要求某列的值必须落在合法范围内靠非空、默认值、CHECK解决引用完整性要求表与表之间的关联关系必须成立靠外键解决。这三类基本把所有约束都装进去了。MySQL里实际能用的约束就六种约束类型关键字核心作用非空约束NOT NULL值不能为空唯一约束UNIQUE值不能重复主键约束PRIMARY KEY每行的唯一标识自带非空唯一默认值约束DEFAULT不传值时用指定默认值外键约束FOREIGN KEY保证子表引用父表的完整性检查约束CHECK值必须满足指定条件8.0.16后才真正生效今天重点讲前四种因为它们的出现频率最高也是所有表设计的基础。外键牵扯到父子表、级联操作、性能取舍CHECK在8.0版本前后的行为差异又很大单独开一篇更合适。2. 从一张用户表开始四条约束的建表实战我习惯用一个贯穿到底的例子。假设我们正在设计用户模块的表业务需求大概是用户名必填且不能重复手机号和邮箱可以暂时不填但一旦填了就不能重复年龄不传默认18账号状态默认正常每条记录需要一个自增主键。对应的建表语句如下CREATE TABLE users ( id INT AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone CHAR(11) COMMENT 手机号, email VARCHAR(100) COMMENT 邮箱, age TINYINT UNSIGNED DEFAULT 18 COMMENT 年龄, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这张表里既有列级约束写在列定义里的NOT NULL、DEFAULT也有表级约束写在最后面的PRIMARY KEY、UNIQUE KEY。列级约束只管当前这一列表级约束可以横跨多列比如联合唯一、联合主键这种写法后面会讲到。建好之后立刻验证一下约束真的在起作用。插入一条正常数据INSERT INTO users (username, phone, email) VALUES (user_a, 13800138000, aexample.com);username、phone、email都传了age和status没传于是DEFAULT生效age变成18status变成1。再插入一条违反非空约束的数据INSERT INTO users (phone, email) VALUES (13900139000, bexample.com);执行会报错Field username doesnt have a default value。因为username有NOT NULL又没有DEFAULT不传就只能收到一个错误这条脏数据根本落不了库。再试试重复手机号INSERT INTO users (username, phone, email) VALUES (user_c, 13800138000, cexample.com);手机号还是13800138000唯一约束直接拦截报Duplicate entry 13800138000 for key users.uk_phone。整个过程可以看到约束不是在应用层靠if判断拦截的而是数据库在提交阶段就把数据挡住了。2.1 非空约束别把NULL和空字符串搞混非空约束的语法最简单列后面加NOT NULL就是CREATE TABLE t1 ( id INT NOT NULL, name VARCHAR(20) NOT NULL );但很多人忽略了一个关键细节NULL和空字符串完全是两个东西。NULL表示“没有值”空字符串表示“有一个值这个值是空的”。在NOT NULL约束下插入空字符串可以成功插入NULL才会报错。INSERT INTO t1 (id, name) VALUES (1, ); -- 成功 INSERT INTO t1 (id, name) VALUES (2, NULL); -- 报错业务里经常出现“用户填了个空值不报错”的困惑十有八九就是空字符串和NULL的语义没分清。设计表的时候想清楚这列的“没有值”用NULL表示还是不存在的状态比如用户的备注字段NULL和空字符串都无所谓但手机号这种必须明确的字段最好用NOT NULL加上默认的空串来兜底。2.2 唯一约束防止重复的坑中坑唯一约束保证列值不重复语法有两种写法。单列唯一可以直接放在列定义里CREATE TABLE t2 ( email VARCHAR(100) UNIQUE );也可以独立声明并给约束起名方便后续删除CREATE TABLE t2 ( email VARCHAR(100), UNIQUE KEY uk_email (email) );需要多个列联合不许重复的时候必须用表级写法CREATE TABLE follow ( user_id INT, follow_id INT, UNIQUE KEY uk_user_follow (user_id, follow_id) );这句的意思是同一个user_id不能重复关注同一个follow_id但是一个用户仍然可以关注很多不同的人。联合唯一在社交关注、购物车、订单明细这些场景里非常常见。唯一约束最大的坑在NULL上。MySQL默认允许唯一约束的列存在多条NULL记录因为NULL不等于任何值NULL也不等于NULL。所以上面那张users表phone加了唯一约束但你可以插一千行phone为NULL的数据一条都不会报错。业务上如果“手机号不能为NULL而且只能有一个NULL都不行”那就得额外用默认值兜底或者直接设定手机号既NOT NULL又UNIQUE再配合默认空字符串。2.3 默认值约束让偷懒也变得安全DEFAULT的作用是在插入时不显式给值的情况下自动填入指定内容。它最常用的搭配对象是状态位和时间字段status TINYINT NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP注意CURRENT_TIMESTAMP是MySQL专门给时间字段的默认值函数可以把记录的创建时间自动填进去。8.0开始还支持DEFAULT (表达式)比如用UUID()生成随机值作为默认值灵活性更大。用DEFAULT有个容易混淆的点如果你插入时显式写了NULL默认值不会生效除非这列同时允许NULL。换句话说默认值只在你“不写这一列”或者“显式写DEFAULT关键字”的时候才会填进去INSERT INTO users (username, phone, email, age) VALUES (user_d, 13700137000, dexample.com, DEFAULT);这种隐式逻辑不踩一次很难记住我在带新人的时候发现普遍会搞错以为写了DEFAULT就会自动替代NULL其实正好相反。3. 主键约束每一张表的定海神针3.1 为什么要选自增整数主键主键的本质是给每一行数据一个身份证号。它同时继承了非空和唯一两条约束而且一张表只能有一个主键。很多人一开始纠结主键和唯一约束都能保证不重复那两者有什么区别第一个区别是主键必须非空唯一约束允许NULL。第二个区别是主键承载的意义不一样它不仅是约束更是InnoDB组织数据的方式。InnoDB是索引组织表数据按照主键索引的顺序物理存储通过主键访问数据是最快的路径。第三个区别是唯一约束可以建多个主键只能建一个。主键选型上我强烈建议业务无关的自增整数ID。不要用手机号做主键手机号可能会被注销、换号不要用邮箱做主键邮箱可能变更更不要用UUID做主键UUID随机性强写入时会造成大量页分裂性能会很差。自增ID稳定、有序、占用空间小是最省心的选择。3.2 创建主键的三种姿势第一种列级约束CREATE TABLE t3 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) );第二种表级约束并命名CREATE TABLE t3 ( id INT AUTO_INCREMENT, name VARCHAR(50), CONSTRAINT pk_id PRIMARY KEY (id) );第三种联合主键CREATE TABLE t3 ( order_id INT, item_id INT, PRIMARY KEY (order_id, item_id) );联合主键的意思是两列组合起来才能唯一确定一行。不过日常开发中联合主键用得越来越少除非是纯粹的关系表比如订单明细、标签关联。大多数场景我更推荐代理主键加联合唯一约束原因后面讲索引的时候会提到多用一张表的主键索引开销和更新逻辑都会复杂很多。3.3 自增主键的三个细节AUTO_INCREMENT必须搭配索引使用一般直接放在主键上。它有三个容易踩的细节要记牢第一个InnoDB的自增值不会因为删除最大ID而回退。假设当前最大ID是100删掉第100行再插入新数据新ID是101而不是100。这个行为在MySQL 8.0之前由内存维护重启后可能会有变化8.0开始自增值持久化到redo log和磁盘重启后也是101。别惊讶这是设计如此。第二个TRUNCATE和DELETE重置自增的行为完全不同。TRUNCATE清空表之后自增从头开始DELETE删除全部数据之后自增继续保持原值TRUNCATE TABLE users; -- 自增重置为1 DELETE FROM users; -- 自增仍延续原来的值第三个可以手动改自增的起始值适合做分库分表时避免ID冲突ALTER TABLE users AUTO_INCREMENT 10000;4. 约束的修改与管理建完表之后怎么补实际开发中很少能一锤定音建出完美的表需求一变约束就得跟着改。约束的管理全部围绕ALTER TABLE展开我把常见的操作整理成一张速查表操作SQL语句添加非空约束ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;删除非空约束ALTER TABLE users MODIFY email VARCHAR(100) NULL;添加唯一约束ALTER TABLE users ADD UNIQUE KEY uk_email (email);删除唯一约束ALTER TABLE users DROP INDEX uk_email;添加默认值ALTER TABLE users ALTER status SET DEFAULT 1;删除默认值ALTER TABLE users ALTER status DROP DEFAULT;添加主键ALTER TABLE users ADD PRIMARY KEY (id);删除主键ALTER TABLE users DROP PRIMARY KEY;注意几个容易翻车的细节。用MODIFY添加非空约束时等于是把整列重定义了一遍必须把原来的数据类型和长度完整写出来同时把想保留的属性比如DEFAULT、注释都带上。少写一个DEFAULT默认值就丢了少写一个COMMENT注释也没了。这种“顺手把别的约束弄丢”的问题我见过太多次-- 原列定义email VARCHAR(100) DEFAULT NULL -- 现在只想加非空却不小心丢掉DEFAULT和COMMENT ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;正确做法是把完整定义写全ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL DEFAULT COMMENT 邮箱;删除主键之前如果主键列还挂着AUTO_INCREMENT会直接报错ERROR 1075 (42000): Incorrect table definition。得先去掉自增属性再删主键ALTER TABLE demo MODIFY id INT; -- 去掉自增注意类型带上 ALTER TABLE demo DROP PRIMARY KEY;最后分享一个习惯每次改完表结构习惯性地跑一下SHOW CREATE TABLE users;看一眼最终列定义。这比猜约束有没有生效靠谱得多改表之前也建议先看一眼这个输出确认当前结构再动手。5. 常见问题与排查技巧实录建约束的时候大家遇到的问题其实高度重复我把这几年带新人遇到的典型问题整理出来按“症状、原因、解法”的方式列一下方便你直接对号入座。症状给手机号加了UNIQUE成功插入两条phone为NULL的数据没有报错。 原因MySQL认为NULL不等于任何值多个NULL之间互不冲突。 解法业务上如果绝对不能接受NULL就把列改成NOT NULL DEFAULT 用空字符串兜底再配合UNIQUE。症状给一张已有数据的表加UNIQUE报错Duplicate entry。 原因表里已经有了重复值约束校验失败。 解法先查重、清理或合并重复数据再添加约束SELECT phone, COUNT(*) FROM users GROUP BY phone HAVING COUNT(*) 1;症状执行ALTER TABLE加非空约束报错Invalid use of NULL value。 原因当前列里已经存在NULL给整列加NOT NULL时校验不过。 解法先用UPDATE把NULL补齐例如UPDATE users SET email WHERE email IS NULL; ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL DEFAULT ;症状MODIFY改完列之后发现之前建的索引或注释丢了。 原因MODIFY是整列重定义不是局部修改。 解法重写完整列定义索引如果需要保留也一并重建。症状删除主键报错ERROR 1075。 原因主键列还带着AUTO_INCREMENT。 解法先去掉自增属性再删除主键顺序不能反。症状插入数据时设置了一个违反CHECK的负数结果没有被拦截。 原因MySQL 8.0之前CHECK是“只解析不执行”的8.0.16之后才真正强制校验。 解法升级到8.0.16以上或者用触发器兜底。这些坑看起来零散背后其实是一条主线索约束的行为和列定义的状态是紧紧绑在一起的改列的时候不看现状直接拼SQL几乎必然出事。6. 约束命名规范老手都在坚持的原则最后聊一个很多人根本不在意的点——约束命名。用上面讲的方法直接写UNIQUE KEY而不显式命名的话MySQL会自动生成一个名字。这个名字通常是列名加上一串随机后缀比如email_2、phone_3这类。乍一看能用等你想删这个约束的时候就会卡住ALTER TABLE users DROP INDEX email_2;你根本猜不到自动名字到底是什么还得先SHOW CREATE TABLE查一遍。所以我建议从头就养成手动命名的习惯CONSTRAINT uk_email UNIQUE (email), CONSTRAINT uk_phone UNIQUE (phone), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)命名规则不用花哨前缀清晰就行主键用pk_唯一约束用uk_外键用fk_检查约束用ck_后面跟着表名和列名一眼就能猜个八九不离十。删约束的时候直接DROP INDEX uk_email再也不用到系统表里翻名字。我在实际工作中还养成了一个习惯新表设计完之后SQL先不用急着执行脑子里过一遍这几件事——主键是不是自增、有没有业务字段被误设成唯一、必填字段有没有全部加上NOT NULL、时间字段有没有默认值、有没有靠代码逻辑才能保证的完整性。这一遍过完再落库后面基本能少踩一半的坑。说到底约束是数据质量的最后一道防线。它不复杂但很琐碎值得你认认真真对待。下一篇我会把外键约束和CHECK约束展开讲包括外键的级联策略选择以及什么时候宁可不用外键也要保证性能感兴趣的话咱们下篇见。
