搞数据库开发这几年我见过太多因为建表时偷懒、后面拿命填坑的案例。聊天记录能重复到令人崩溃商品价格能不填就为0删除状态和唯一键打架业务代码里写一堆if判断拦截脏数据结果接口被绕过直接怼进表里。数据库约束这个东西说穿了就是给表结构立规矩让数据库自己在写入时把关比你写在业务代码里的校验要硬得多。上一篇我们从概念上梳理了约束是什么、为什么要用这篇接着往下聊重点落在六种约束的实际写法、组合方式和真实业务里最容易踩的坑上。看完这篇你至少能把自己手头那些散漫的表结构重新审视一遍。1. 给表上锁之前先把锁的类型理清楚约束就是在建表或改表时对字段施加的规则好在写入时拦截非法数据。MySQL里常用的有六种非空、唯一、主键、默认值、检查、外键。别看它们都是约束适用场景和踩坑方式完全不同。先放一张速查表后面我们逐个展开约束类型关键字作用范围核心作用最常见的坑非空NOT NULL单字段拒绝NULL值写入和“默认值”混用导致语义混乱唯一UNIQUE单字段或字段组合防止重复值NULL不参与唯一校验容易放行重复空值主键PRIMARY KEY单字段或字段组合唯一标识每行且自带非空过度设计复合主键后期关联麻烦默认值DEFAULT单字段不填值时用默认值填充只给默认值不设非空脏数据照样进检查CHECK单字段或整行自定义条件校验低版本MySQL不生效写错条件直接锁表外键FOREIGN KEY表之间维护引用完整性互联网高并发场景滥用导致死锁可能有人会说非空和唯一不都很简单吗有什么好讲的。实际上约束之间是有协作关系的组合起来才发挥最大价值。常见组合包括“非空默认值”“唯一非空”“主键自增”等等下面逐一拆开讲。还有一点值得提前说透约束最好在建表时一次性设计到位而不是等数据刷了几十万行再回头补。MySQL 8.0里加一个唯一约束如果目标列本身有大量重复值会直接报错即使没有重复值对大表执行ALTER TABLE也需要重建整张表几千万行的表重建一次的时间够你喝好几杯咖啡。所以想清楚再建表改表永远是最后的选择。2. 别把NOT NULL当成摆设非空、默认值、唯一约束的协同用法很多人对非空约束的理解就是“字段不能为空”实际使用中远没有这么简单。MySQL里的NULL和空字符串是两种完全不同的东西NULL代表“没值”空字符串代表“有值但内容是空的”。这个区别在后端开发里天天引发问题。2.1 非空约束的正确打开方式非空约束直接写在字段类型后面用NOT NULL声明。举个例子CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, nickname VARCHAR(50) NOT NULL );这里phone和nickname都不允许为NULL。但你有没有想过一个问题如果不小心插入了一条记录phone没填SQL直接报错你的业务代码就得兜住这个错误否则用户看到的可能是一段Raw SQL报错信息。所以非空约束不是替你把数据校验做好而是把“该谁校验”的问题摊在明面上——数据库把违规数据挡在门外业务层负责把这个错误翻译成用户能理解的提示。2.2 默认值绝不是用来偷懒的DEFAULT约束解决的是“用户没传值时填什么”。它经常和NOT NULL一起出现比如状态字段status TINYINT NOT NULL DEFAULT 0这个设计很常见0代表待处理1代表已处理。但有个细节需要注意只写DEFAULT不写NOT NULL插入时没传status字段MySQL会用0填充可如果你显式往里面插入NULL它照样接受。结果你的表里既有NULL又有0查询时还得专门处理这种“隐形的脏数据”。所以凡是业务上必须有值的字段一律“NOT NULL DEFAULT”双写少写任何一个都是埋雷。2.3 唯一约束和NULL的暧昧关系唯一约束的坑主要在NULL。先看例子CREATE TABLE user ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );这个表里两次插入email为NULL的记录MySQL并不会报错。因为在MySQL的规则里NULL和NULL不算重复唯一约束管不住NULL。这在真实业务中非常致命——你本来想保证每个用户邮箱唯一结果注册时没传邮箱的账号全部是NULL可以无限插入。解决办法是给email字段加上NOT NULL从物理层面堵死NULL入口。或者如果你业务上确实允许邮箱为空那唯一约束的意义就要重新考虑了因为NULL的存在本身就在破坏“唯一”的语义。2.4 三个约束组合起来的典型表结构把上面三块放一起一个比较稳的用户表至少长这样CREATE TABLE app_user ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键, phone VARCHAR(20) NOT NULL UNIQUE COMMENT 手机号唯一, password_hash CHAR(64) NOT NULL COMMENT 密码哈希不允许为空, nickname VARCHAR(50) NOT NULL DEFAULT 新用户 COMMENT 昵称默认给一个, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0正常 1禁用 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT应用用户表;每个字段的约束都不是随便加的。phone既不能空也不能重复password_hash必须存在nickname可以缺省但不能是空值status必须总是有值。这一张表如果是让一个新人来设计大概率会砍掉一半约束然后等到线上出现重复用户注册的时候再回来加——那时候你已经需要先写脚本清理脏数据了。3. 主键设计的混乱现场单主键、复合主键和自增列的陷阱主键约束是唯一约束和非空约束的合体一张表只能有一个主键主键列默认非空且唯一。听起来很简单但实际设计时经常被搞出各种幺蛾子。3.1 单字段主键怎么选最常见的做法就是自增ID或者雪花算法的分布式ID作为逻辑主键不参与任何业务逻辑。这种做法适合绝大多数业务场景既能保证唯一性又不会因为业务数据变化导致主键失效。不过自增ID有个隐性坑事务回滚后ID会跳号。比如你开启事务插入一条记录事务回滚了但自增计数器的值已经消耗掉不会回退。于是表里的ID会出现空洞。很多业务对ID连续有执念觉得ID跳号是“数据脏了”实际上这是InnoDB的设计行为不需要也做不到完全避免。如果你业务上真的不能接受ID断层可以考虑改用业务主键比如订单号或者彻底放弃“ID连续”这种不合理的需求。雪花ID这类分布式主键在单表场景下也能用但它不是数据库自动生成的需要应用层生成后再插入。好处是全局唯一、不依赖数据库自增分库分表时不会撞车坏处是长度较长索引体积变大而且生成的ID没法像自增那样直观地反映插入顺序。3.2 复合主键什么时候用复合主键指多个字段共同组成主键比如课程选课表CREATE TABLE course_selection ( student_id INT NOT NULL, course_id INT NOT NULL, selected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (student_id, course_id) );这种设计的核心作用是防重一个学生选同一门课由数据库层面直接拒绝重复插入。它的意义在于业务代码不用先查一遍再决定是否插入数据库就保证了唯一性也省去了唯一约束的额外声明。但复合主键也有麻烦最直接的就是关联查询。别的表想引用这门选课记录就得同时带student_id和course_id两个字段外键和JOIN都会变得更啰嗦。更严重的是InnoDB的聚簇索引会按照复合主键的字段顺序来组织数据如果你经常按course_id单独查这张表索引效果会大打折扣需要额外再建辅助索引。所以我对复合主键的建议是仅在“这个组合本身有业务防重需求”时使用比如选课、点赞、关注这类多对多关系表否则老老实实用自增ID或全局ID防重交给唯一约束去处理。3.3 自增主键和AUTO_INCREMENT的参数细节MySQL 8.0之后自增值是持久化的重启不会丢但有些老项目从5.x升级到8.x之后如果没注意innodb_autoinc_lock_mode参数的变化批量插入时的自增行为会有差异。常规情况下直接用默认值就行不需要手动调。需要注意的反而是显式指定自增ID的问题。在开发环境中为了“省事”有人会手动插入一条带ID的记录把ID直接插到1000那么后面自增就从1001开始。这种操作偶尔一次没问题但如果在有数据的表上频繁手动指定ID等ID撞到已有的值插入就会报错。别问我是怎么知道的。4. 检查约束让数据库替你拒绝不合理的值CHECK约束在MySQL 8.0开始才真正执行之前创建表时写了CHECK会被解析但被忽略。很多从5.7时代走过来的人对它有误解觉得“这东西没什么用MySQL不支持”实际上8.0已经支持并强制执行了。如果你还在用5.7建议赶紧升级不然后面的默认约束都是摆设。4.1 CHECK到底怎么用毫不夸张地说CHECK约束是数据库防脏数据的一道硬防线。它可以在字段级别或表级别定义一个条件不满足条件的插入或更新直接被拒绝。CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, quantity INT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 0, CHECK (price 0), CHECK (quantity 0), CHECK (status IN (0, 1, 2)) );这里三个CHECK分别约束价格不能为负、库存不能为负、状态只能在0/1/2里取。这些条件如果在业务代码里挨个写既啰嗦又容易漏放在数据库里不管谁往里写数据规则统一生效。有人可能会问这些校验不是应该由后端接口做掉吗对接口校验必不可少但数据库约束属于“最后一道防线”。后端接口可能只开放给前端可维护脚本、数据回刷、临时SQL改数都会绕过业务代码直连数据库。如果没有CHECK兜底一条糟糕的UPDATE就能把整张表的价格改成负数。4.2 表级CHECK和跨字段校验CHECK也可以写在字段外面校验整行数据的关系比如结束时间必须晚于开始时间CREATE TABLE schedule ( id INT PRIMARY KEY AUTO_INCREMENT, start_time DATETIME NOT NULL, end_time DATETIME NOT NULL, CONSTRAINT chk_schedule_time CHECK (end_time start_time) );这里给约束起了个名字chk_schedule_time以后要删除或者修改它时可以直接按名字操作。如果不命名系统会自动生成一个后续维护起来只能靠猜。所以在建表时给CHECK命名是个好习惯。4.3 CHECK约束的坑版本、表达式和长度首先是版本问题。MySQL 8.0.16以后CHECK才真正强制生效如果你的环境在8.0.16之前写了等于白写。上线前用SELECT VERSION()确认一下别在低版本上白忙活。其次CHECK表达式中不能使用函数和子查询只能用当前行的字段比较。想实现“某个字段不能大于同表另一个字段”这类规则也得确保条件写法在MySQL支持范围内。跨表校验是CHECK做不了的那种需求只能走触发器或者应用层。最后给已有大表加CHECK之前要想清楚。MySQL 8.0新增CHECK约束时需要对表进行校验如果表里面有历史脏数据操作可能会失败。实际经验是先写一条查询把不满足条件的数据捞出来清理干净或者和业务方确认后再加约束不然就等着半夜收到报警。5. 外键约束与“现实世界”的妥协外键约束是约束里最“重”的一种。它定义了表与表之间的父子关系保证子表里引用的父表记录一定存在。比如订单表里的user_id如果加了外键约束指向user表的id那么插入一个不存在的user_id数据库直接拒绝。5.1 基础写法与两种主要策略CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL UNIQUE, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES app_user (id) );这样定义外键之后插入订单时如果user_id对应不上app_user表的id写入就会失败。同时如果试图删除被订单引用的用户默认行为也会受限。MySQL外键的引用动作支持两种策略常用的有ON DELETE RESTRICT默认行为被引用时禁止删除父记录ON DELETE CASCADE删除父记录时自动删除关联子记录ON UPDATE CASCADE父表主键更新时自动同步子表外键。CASCADE听起来很方便但实际业务中要非常克制。一个用户删除时连带把订单、积分、日志全删了万一审计需要这些数据哭都来不及。我见过有些项目为了“省事”把外键ON DELETE CASCADE全表铺开结果一次误删用户直接引起大面积数据丢失。安全第一删除类操作尽量用逻辑删除物理删除配CASCADE属于高危操作。5.2 外键在高并发场景下的争议很多互联网公司的生产环境故意不用外键让关联关系只存在于应用层。原因很现实分库分表之后外键基本失效高频写入场景下外键约束需要额外的行锁检查会影响写入性能大促期间做数据迁移和扩容时外键会让批量操作变得极其繁琐。但这不代表外键没用。在后台管理系统、财务系统、内容管理等数据一致性要求高、并发量不大的场景外键是保证数据不错乱的最省心的办法。特别是那些经常被维护脚本直接操作的表没有外键约束的话insert一条订单时引用了不存在的用户几天后对账时才暴露问题定位成本比写外键高得多。我个人的经验是核心链路表之间可以不用外键但所有做过“脏数据清洗”的表都值得重新评估外键。有些程序员对“数据一致性”没概念直到被脏数据坑过一次才会回头补约束补的时候又要处理历史数据非常痛苦。6. 约束组合实战从一张用户表到订单关联的完整设计前面已经把六种约束逐个说透了现在把它们组合起来落到一个模拟业务里完整跑一遍。这个例子非常适合拿来做练习建议你照着在本地MySQL 8.0环境敲一次顺便观察约束生效时的报错行为。6.1 先建父表用户表和商品表CREATE TABLE app_user ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL UNIQUE COMMENT 手机号唯一且非空, nickname VARCHAR(50) NOT NULL DEFAULT 新用户 COMMENT 昵称, age TINYINT NULL COMMENT 年龄允许为空, status TINYINT NOT NULL DEFAULT 0 COMMENT 用户状态 0正常 1禁用, CONSTRAINT chk_user_age CHECK (age 0 AND age 120), CONSTRAINT chk_user_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里age故意没有写NOT NULL因为有些业务允许用户不填年龄。但CHECK里还是限制了范围防止业务方传入一个200岁的“活化石”。status用CHECK限定取值保证状态机不会出现非法的第三态。CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品ID, name VARCHAR(100) NOT NULL COMMENT 商品名称, price DECIMAL(10,2) NOT NULL COMMENT 售价, stock INT NOT NULL DEFAULT 0 COMMENT 库存, CONSTRAINT chk_product_price CHECK (price 0), CONSTRAINT chk_product_stock CHECK (stock 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;商品表的两条CHECK分别拦住负价格和负库存。一旦线上活动运营或者脚本把库存刷成负数数据库层面直接报错不会等到仓库实际发货时才发现数量对不上。6.2 再建子表订单表和订单明细表CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) NOT NULL UNIQUE COMMENT 订单编号, user_id INT NOT NULL COMMENT 下单用户ID, total_amount DECIMAL(10,2) NOT NULL COMMENT 订单总金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES app_user (id), CONSTRAINT chk_orders_status CHECK (status IN (0, 1, 2)), CONSTRAINT chk_orders_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; CREATE TABLE order_item ( order_id INT NOT NULL COMMENT 订单ID, product_id INT NOT NULL COMMENT 商品ID, quantity INT NOT NULL COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, PRIMARY KEY (order_id, product_id), CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (id), CONSTRAINT chk_item_quantity CHECK (quantity 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;订单明细表用了复合主键防止同一订单里重复添加同一个商品。外键分别指向订单表和商品表ORDER_ITEM自动获得了两层引用保护。这里如果把quantity的CHECK写成大于0而不是大于等于0能顺手把“买了0个商品”这种异常单挡在外面。6.3 动手验证约束效果的SQL脚本把下面的脚本在本地跑一遍每一步都会触发对应的约束报错建议逐条执行感受一下报错内容-- 正常插入用户 INSERT INTO app_user (phone, nickname, age) VALUES (13800000001, 张三, 28); -- 期望失败手机号重复 INSERT INTO app_user (phone, nickname, age) VALUES (13800000001, 李四, 20); -- 期望失败age超过120 INSERT INTO app_user (phone, nickname, age) VALUES (13800000002, 王五, 130); -- 期望失败status非法写成3 INSERT INTO app_user (phone, nickname, age, status) VALUES (13800000003, 赵六, 30, 3); -- 正常插入商品 INSERT INTO product (name, price, stock) VALUES (机械键盘, 399.00, 100); -- 期望失败价格为负 INSERT INTO product (name, price, stock) VALUES (负价格商品, -1.00, 10); -- 正常下订单 INSERT INTO orders (order_no, user_id, total_amount) VALUES (ORD20240101001, 1, 399.00); -- 期望失败user_id不存在 INSERT INTO orders (order_no, user_id, total_amount) VALUES (ORD20240101002, 999, 399.00); -- 期望失败订单状态非法 INSERT INTO orders (order_no, user_id, total_amount, status) VALUES (ORD20240101003, 1, 399.00, 9);这几条SQL执行完你会发现数据库在处理非法数据时毫不留情报错信息清清楚楚。这就是约束的价值你不需要在业务代码里处处提防数据库已经帮你把不可信数据挡在了门外。6.4 继续深挖当约束挡住正常需求怎么办表建好、约束也生效了但真实业务有时还会跟约束“不对付”。比如用户想换绑手机号结果旧手机号唯一新手机号又唯一更新时容易遭遇短暂冲突又比如订单表想删除一个已存在的商品外键直接挡着不允许删。这些不是约束的错误而是业务方案需要调整。常见办法是给用户表加一个is_deleted逻辑删除标记手机号唯一约束变成(phone, is_deleted)的组合形式这样换绑和删除都能平滑处理。另一种更干净的方案是只保留逻辑删除标记不做真正删除用户所有历史数据都可追溯。手机号这一列通过一个状态字段区分有效和回收查询时过滤状态即可。不要为了解决约束冲突直接去掉约束那是在还债的路上越走越远。7. 约束选用速查什么场景上什么约束最后把前面所有内容浓缩成一张决策表平时建表拿不准的时候直接对着查业务诉求推荐约束方案注意事项字段必须有值NOT NULL必要时加DEFAULT别把NULL和空字符串搞混字段不能重复UNIQUE推荐配套NOT NULL多个NULL不会被判重每行需要唯一标识PRIMARY KEY优先单字段自增慎用复合主键字段值必须在指定范围CHECK确认MySQL版本已支持关联表必须引用有效数据FOREIGN KEY高并发下谨慎逻辑删除优先防止重复选课/重复点赞复合主键或UNIQUE组合组合字段都带NOT NULL状态字段规范化CHECK NOT NULL DEFAULT三个组合一起上缺一不可这套速查表是我在实践中反复调整出来的组合方案基本覆盖了90%的业务建表需求。很多初学者建表只想着把所有字段塞进去完全不管约束设计等代码写复杂了才发现数据的正确性根本没法保证。与其在业务代码里猜来猜去不如在建表时花二十分钟把这些约定固化下来。另外补充一句真心话约束不是越多越好。每个约束都有维护成本过度设计会让业务代码在处理失败时频繁踩到数据库报错影响用户感知。合理的做法是“业务上绝不允许出现的情况用数据库约束兜底常规流程的校验留到应用层”。这样既保证底线又留了灵活度。数据库约束这东西看起来是基础中的基础但绝大部分线上数据问题追根溯源都是建表时少写了一个约束。下一篇我会继续深入约束聊一聊索引和约束之间那些容易让人混淆的关系。