尧图网页设计 
1. 约束为什么是数据合法性的最后防线1.1 没有约束时脏数据能从哪里混进来MySQL数据库基础系列更新到第六篇了前面聊过建库建表、字段类型、增删改查今天正儿八经聊一个容易被低估的话题表的约束。说它容易被低估是因为大多数初学者都能背出主键、非空、唯一这几个名词但真到了设计表结构的时候却经常能省则省。我在早期接手过一个后台项目用户表里没有唯一约束没有主键策略连手机号这种业务上必须唯一的字段都裸奔。结果运营在导数据时重复录入了同一个手机号导致用户登录时程序取到两条记录直接抛异常。更麻烦的是这个问题在测试环境完全复现不出来因为测试数据量小概率低。当时排查了很久最后发现根因就是表结构没约束。脏数据通常从三条路径混进来第一应用层代码漏校验或校验逻辑写错前端传什么后端就存什么第二业务人员或运维直接执行INSERT、UPDATE语句手工改数据绕过了程序里的校验逻辑第三数据迁移、定时脚本跑批时源数据本身就有问题导入时没有过滤干净。这三条路径的共同点是应用层的防线被绕过或失效时如果数据库层面没有兜底垃圾数据就永久落地了。约束就是数据库的安检门数据进来之前先验明正身不符合规则的一律拦截。明白这一点你就知道约束的定位不是锦上添花而是数据合法性的最后防线。1.2 约束与索引、触发器的职能边界很多读者会把约束和索引混在一起。确实主键约束和唯一约束在InnoDB里会自动创建索引这是它们的附带效果。但二者定位完全不同索引是为查询提速服务的约束是为数据合法性服务的。一个人可以有多个索引但主键约束一个表只能有一个唯一约束管的是重复索引本身完全不管数据是否重复。搞混这两者设计表的时候就会犯一个典型错误——为了给某字段加索引顺手把它设成UNIQUE结果业务上允许重复的数据全被拦下了。还有一个容易混淆的是触发器。触发器也能做数据校验在INSERT或UPDATE之前用NEW.xxx判断要不要抛异常。但从我的经验来看触发器的隐式行为太多排查问题时要额外翻触发器定义还会增加每次DML的执行开销。更重要的是触发器的校验逻辑和表结构是分离的换一个环境部署时容易漏掉。声明式约束写在表定义里跟着DDL走迁移、备份、重建表都不会丢。所以我的结论是能用约束表达的数据规则坚决不用触发器约束表达不了的复杂逻辑优先放到应用层而不是堆到数据库里。2. 六种约束逐一拆解语法、行为和应用场景2.1 NOT NULL与DEFAULT非空与兜底成对出现更稳NOT NULL是入门级约束含义简单到不需要解释这一列不能为空。但实际设计里有个细节值得单独说——NOT NULL最好和DEFAULT一起出现。为什么因为一个字段定义了NOT NULL却不给默认值INSERT语句一旦没传这个字段数据库就会报错。这是严格但很多时候会给业务方添乱。比如用户表的status字段语义上是用户状态默认正常你既希望它不能为空又想省掉应用层每次都要显式传值那就在定义时写成status TINYINT NOT NULL DEFAULT 1。用DEFAULT兜底INSERT时忘记传它也不会报错还不会出现NULL值。DEFAULT本身当然也可以独立使用允许为空、但默认给一个值。不过那种用法要小心允许NULL的列配合DEFAULT实际插入NULL时数据库并不会用默认值替换NULL因为NULL是显式赋值DEFAULT只在INSERT语句省略该列时才生效。这一点很多人踩过坑——以为插入NULL会自动变成默认值结果查出来还是NULL。想彻底杜绝NULL只能NOT NULL加DEFAULT一起上。另外MySQL 8.0开始支持表达式默认值DEFAULT (UUID())这种写法也合法但常规场景还是用常量或CURRENT_TIMESTAMP最省心。2.2 UNIQUE与PRIMARY KEY唯一与主键别混为一谈UNIQUE约束声明这一列或这几列的组合不能有重复值。它和PRIMARY KEY有三点关键差异第一一张表只能有一个主键但可以有多个唯一约束第二主键隐含NOT NULL唯一约束却允许NULL而且在MySQL里唯一约束的NULL值是可以出现多次的——因为MySQL把NULL视为未知不参与重复判断。这一点在业务上经常造成意外你给手机号设置了UNIQUE但老系统里的空手机号记录可以存在无数条因为空值被写成了NULL而不是空字符串。想彻底堵住这个口子要么把列同时设为NOT NULL要么把空串作为标识而不是NULL。第三点差异是语义上的主键代表实体身份是其他表引用这张表的锚点唯一约束只是防重复。所以选主键时不要图省事直接拿业务字段比如手机号、身份证号当主键而要用与业务无关的自增ID或UUID。业务字段会变哪怕概率极小一旦变化所有引用它的外键都要改代价很高。自增ID加上主键约束就是给每行数据发了一张永久的身份证。2.3 FOREIGN KEY表间契约的代价与价值外键约束定义的是表与表之间的父子关系它保证子表里的某个字段值一定能在父表中找到对应记录。比如订单表里的user_id如果有外键指向用户表的id那么插入订单时MySQL会先检查用户表里是否存在这个id不存在就直接拒绝。这保证了数据在跨表维度上的完整性是数据合法性里最难在应用层做全面的那部分——因为你得在每一条写路径上都去查一遍父表而外键把这些逻辑交给了数据库统一托管。不过外键的代价也很明显。InnoDB在维护外键时会在父表上加共享锁高并发写场景下容易放大锁竞争分库分表后外键无法跨实例工作数据归档、批量导入时外键会让操作顺序变得绑手绑脚。这也是为什么很多互联网团队在生产库里很少用物理外键而是靠应用层保证引用关系表结构里只建普通索引。这个权衡没有绝对对错如果你的系统是中小规模、并发不高、规范性强物理外键能省掉大量脏数据如果是高并发、大规模、频繁做分库分表的场景逻辑外键更现实。2.4 CHECK8.0.16之后真正能用的规则守卫CHECK约束允许你自定义任意合法条件比如age BETWEEN 0 AND 120、stock 0、status IN (0, 1)。很多老教程会说MySQL的CHECK约束不生效那是历史遗留问题——5.7及更早版本确实会忽略它。但从MySQL 8.0.16开始CHECK约束已经被强制执行了这是一个很多人没跟上版本变化的知识点。CHECK的正确打开方式是把简单、稳定、与业务强相关的取值规则放在数据库里。举例来说订单状态的取值范围待支付、已支付、已发货、已完成、已取消通常不会经常变用CHECK (status IN (0,1,2,3,4))就能避免程序某个分支把状态写成5或-1。但要注意CHECK里别写子查询、别调用存储函数、别依赖时间函数比如NOW()这些操作在CHECK里受限或会产生不可预测的行为。复杂跨表校验请回到应用层。3. 建表阶段一份能直接抄作业的约束定义清单3.1 列级约束与表级约束的取舍逻辑约束的定义位置分为两种列级约束写在字段定义后面表级约束写在所有字段定义完之后。列级约束最常用的是NOT NULL、DEFAULT、AUTO_INCREMENT以及单个字段上的UNIQUE或PRIMARY KEY。表级约束则用来定义联合主键、联合唯一、外键以及命名更清晰的CHECK约束。一个经验是只要能明确命名优先用表级约束。列级约束系统会自动生成名字比如PRIMARY、uk_列名这种后期定位问题或删除约束时不够直观表级约束可以写成CONSTRAINT uk_order_user_product UNIQUE (user_id, product_id)这种一眼能看懂的形式。表级约束还有一个不可替代的场景——联合唯一。比如一个订单明细表里关心的是同一个订单不能重复记录同一个商品这约束的是(order_id, product_id)这个组合而不是单独某一列。这种需求只能用表级UNIQUE列级约束表达不了。3.2 完整建表示例用户表、商品表、订单表、订单明细表理论说再多不如直接看一套完整的建表语句。下面这个例子是一个精简版电商核心表我把这一节讲到的约束都用上了你可以直接保存下来当模板。-- 用户表 CREATE TABLE user ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(11) NOT NULL UNIQUE COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, CONSTRAINT chk_user_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; -- 商品表 CREATE TABLE product ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 商品ID, name VARCHAR(128) NOT NULL COMMENT 商品名称, price DECIMAL(10,2) NOT NULL COMMENT 单价单位元, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 库存, status TINYINT NOT NULL DEFAULT 1 COMMENT 上架状态1上架 0下架, CONSTRAINT chk_product_price CHECK (price 0), CONSTRAINT chk_product_stock CHECK (stock 0), CONSTRAINT chk_product_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表; -- 订单表 CREATE TABLE orders ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID, user_id INT UNSIGNED NOT NULL COMMENT 下单用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 订单总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id), CONSTRAINT chk_orders_status CHECK (status IN (0, 1, 2, 3, 4)), CONSTRAINT chk_orders_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; -- 订单明细表 CREATE TABLE order_item ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 明细ID, order_id INT UNSIGNED NOT NULL COMMENT 订单ID, product_id INT UNSIGNED NOT NULL COMMENT 商品ID, quantity INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价快照, 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), CONSTRAINT chk_item_price CHECK (price 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这套建表语句有几个细节值得品味。第一订单明细表的price字段我单独存了成交时的单价快照不直接引用商品表的当前价因为商品价格会变订单历史不能跟着变。第二每个外键都建立了父子关系删用户时如果有订单会被外键拦住这保证统计报表不会突然出现悬挂数据。第三CHECK后面的命名都带着表名和字段名以后排查报错一眼就能看出是哪个规则被违反。3.3 约束命名规范三个月后你还能看懂的约束命名命名这件事前期不重视、后期火葬场。MySQL默认的约束名像PRIMARY、列名这种还好但外键和CHECK如果不显式命名系统自动生成的名字是一长串随机字符。等到你执行ALTER TABLE DROP FOREIGN KEY的时候得先去information_schema查名字非常痛苦。我一般遵循这套命名规则约束类型命名格式示例主键PRIMARY系统默认为PRIMARY唯一约束uk_表名_字段名uk_user_phone外键fk_子表名_父表名fk_orders_userCHECKchk_表名_字段名chk_user_status这套规则的好处是别人读你的建表语句时看到chk_product_price不用查定义就能猜到是商品价格上的检查规则。更重要的是约束名在information_schema.TABLE_CONSTRAINTS里可以模糊搜索团队排障时能快速定位。4. 后期维护ALTER TABLE修改约束与高频报错排查链4.1 添加、删除、修改约束的标准操作约束不是一锤子买卖业务迭代时经常要对已有表补约束。这里给你整理一张速查表操作目标标准SQL添加唯一约束ALTER TABLE user ADD CONSTRAINT uk_user_phone UNIQUE (phone);添加外键ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id);添加CHECKALTER TABLE orders ADD CONSTRAINT chk_orders_status CHECK (status IN (0,1,2,3,4));删除主键ALTER TABLE user DROP PRIMARY KEY;删除唯一约束ALTER TABLE user DROP INDEX uk_user_phone;删除外键ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;删除CHECKALTER TABLE orders DROP CHECK chk_orders_status;修改字段为空性ALTER TABLE user MODIFY COLUMN phone VARCHAR(11) NOT NULL;修改默认值ALTER TABLE user ALTER COLUMN status SET DEFAULT 1;删除默认值ALTER TABLE user ALTER COLUMN status DROP DEFAULT;有几个坑单独提醒。第一删除唯一约束的语法是DROP INDEX而不是DROP CONSTRAINT很多人第一次会写错。第二给已有数据的大表加CHECK约束时MySQL会对全表做一遍校验几百万行的表会锁住最好在业务低峰期操作。第三修改字段为空性时MODIFY COLUMN必须把字段的完整定义重新写一遍比如VARCHAR(11)漏掉就会把类型或长度改了出问题连排查方向都没有。第四给已有数据的表加唯一约束如果数据本身已经重复DDL会直接报错不会帮你清洗数据。4.2 从报错信息反推约束类型五类高频错误排查约束的报错信息基本都带错误码记住常见的几个排障速度会快很多。错误码典型报错内容约束类型含义1062Duplicate entry xxx for key uk_user_phoneUNIQUE插入重复值1048Column phone cannot be nullNOT NULL插入了NULL1452Cannot add or update a child row: a foreign key constraint failsFOREIGN KEY外键引用的父记录不存在3819Check constraint chk_product_price is violatedCHECK不满足CHECK条件1364Field phone doesnt have a default valueNOT NULLDEFAULT缺失INSERT时省略了该字段且无默认值排查思路上我的习惯是先在应用程序日志里找到报错片段把错误码记住然后到数据库端用SHOW ENGINE INNODB STATUS或information_schema去查具体约束名。外键错误1452有时会带出详细的嵌套信息因为InnoDB会把外键链路上的约束名都打印出来顺着它查就能知道是哪张表和哪张表产生了断链。最隐蔽的是1048这一类程序正常跑了好几个月突然有一天某条数据插入报错查了半天发现是上游接口把字段值传成了NULL。这时你要做的不是去掉NOT NULL约束而是回头改上游传参约束只是把问题暴露出来而已。4.3 为什么生产环境我很少启用物理外键这个话题在前面提过一嘴这里展开说。我待过的团队里确实有把外键禁用的传统新来的开发甚至会被规则约束掉。禁用的核心原因是性能与扩展性问题。InnoDB的外键机制在每次INSERT、UPDATE时都要去父表检查引用关系并且按一定规则加锁这在并发写入量大的表上会放大锁等待。更麻烦的是做分库分表后订单和用户可能落到了不同的数据库实例物理外键根本没法跨实例工作只能靠应用层在写入时先查一次用户表。但这不意味着外键一无是处。如果你的业务是后台管理系统、企业内部系统、低并发场景物理外键带来的完整性收益远大于性能消耗。我见过不少系统因为不用外键在删除用户时忘了清理关联数据导致订单表出现孤儿记录统计报表对不上账。数据完整性这种东西不出问题就算了一出问题就是连环事故。所以我的建议不是永远不用外键而是评估完数据分布和并发量后再决定用物理外键还是逻辑外键。小系统用物理外键真出问题也好查大系统用逻辑外键靠规范流程和数据巡检兜底。5. 实战验证造一条非法数据看约束怎么拦截5.1 数据插入验证每一条非法数据都被精准拦下建完上面的表我们把各种非法数据挨个插一遍看看约束是不是真的在工作。-- 场景1手机号填NULL违反NOT NULL INSERT INTO user (phone) VALUES (NULL); -- 报错Column phone cannot be null -- 场景2手机号重复违反UNIQUE INSERT INTO user (phone, email) VALUES (13800138000, atest.com); INSERT INTO user (phone, email) VALUES (13800138000, btest.com); -- 报错Duplicate entry 13800138000 for key user.uk_user_phone -- 场景3商品价格为负数违反CHECK INSERT INTO product (name, price, stock) VALUES (测试商品, -10, 100); -- 报错Check constraint chk_product_price is violated -- 场景4订单引用不存在的用户违反FOREIGN KEY INSERT INTO orders (user_id, total_amount) VALUES (9999, 100.00); -- 报错Cannot add or update a child row: a foreign key constraint fails -- 场景5订单明细数量为0违反CHECK INSERT INTO order_item (order_id, product_id, quantity, price) VALUES (1, 1, 0, 10.00); -- 报错Check constraint chk_item_quantity is violated每个错误码都指向明确的约束规则而且报错信息里直接写着约束名。这意味着你的日志告警系统可以直接拿约束名做聚合统计看哪些规则被频繁触发反向推动解决上游数据质量问题。5.2 组合约束在真实业务中的配置套路从上面的例子能看出约束经常是多个一起作用在同一张表上。实战中还有一些组合套路值得借鉴。一个是业务状态机模式订单状态除了用CHECK限制取值范围还用外键确保订单归属某个真实用户再用DEFAULT给初始状态兜底。三件事分别由三种约束负责而不是全部塞到应用层。另一个是金额与数量模式所有涉及钱和数量的字段统一用DECIMAL加CHECK(字段 0)。好处是哪怕某个业务分支漏了判断数据库也会拒绝负数的出现。我曾经在报表里发现过负数的退款金额排查后是程序月结脚本的SQL写错导致扣成了负数这类问题全靠CHECK拦截。还有一个是快照模式订单明细保存了quantity和price的快照不随商品表变化。这时不需要外键指向商品表但要建立逻辑映射关系一般会加一个INDEX(product_id)方便查询但物理外键要看场景。5.3 给初学者的三条约束设计心法聊了这么多最后给刚开始接触MySQL数据库基础的读者提炼三条心法。第一能声明就不要手写。数据合法性规则能声明为约束的优先声明。约束写在DDL里跟随表结构走天然具备可迁移、可追溯的特性比藏在业务代码深处的if判断要靠谱得多。第二约束宁严勿松但上线前一定要评估存量数据。给一个已经跑了两年的表加NOT NULL或CHECK务必先查一遍全表有没有历史脏数据否则DDL执行到一半失败表还被锁住。安全的做法是先备份再在低峰期操作。第三主键和唯一约束是底线。不管团队规范多松散主键必须有唯一约束按业务语义来。哪怕你公司没有DBA哪怕代码只有几千行只要用到了MySQL这两条就是最便宜的保险。我见过太多系统问题不是出在高深架构上而是出在表结构最初那十几行CREATE TABLE语句没有认真写。约束这种东西建表时多花五分钟开发时可以少熬夜两小时。希望这篇文章能帮你把MySQL表的约束真正用起来而不是停留在会写语法却不知道何时该用的阶段。 MySQL表约束详解：六类约束守护数据完整性与合法性
1. 约束为什么是数据合法性的最后防线1.1 没有约束时脏数据能从哪里混进来MySQL数据库基础系列更新到第六篇了前面聊过建库建表、字段类型、增删改查今天正儿八经聊一个容易被低估的话题表的约束。说它容易被低估是因为大多数初学者都能背出主键、非空、唯一这几个名词但真到了设计表结构的时候却经常能省则省。我在早期接手过一个后台项目用户表里没有唯一约束没有主键策略连手机号这种业务上必须唯一的字段都裸奔。结果运营在导数据时重复录入了同一个手机号导致用户登录时程序取到两条记录直接抛异常。更麻烦的是这个问题在测试环境完全复现不出来因为测试数据量小概率低。当时排查了很久最后发现根因就是表结构没约束。脏数据通常从三条路径混进来第一应用层代码漏校验或校验逻辑写错前端传什么后端就存什么第二业务人员或运维直接执行INSERT、UPDATE语句手工改数据绕过了程序里的校验逻辑第三数据迁移、定时脚本跑批时源数据本身就有问题导入时没有过滤干净。这三条路径的共同点是应用层的防线被绕过或失效时如果数据库层面没有兜底垃圾数据就永久落地了。约束就是数据库的安检门数据进来之前先验明正身不符合规则的一律拦截。明白这一点你就知道约束的定位不是锦上添花而是数据合法性的最后防线。1.2 约束与索引、触发器的职能边界很多读者会把约束和索引混在一起。确实主键约束和唯一约束在InnoDB里会自动创建索引这是它们的附带效果。但二者定位完全不同索引是为查询提速服务的约束是为数据合法性服务的。一个人可以有多个索引但主键约束一个表只能有一个唯一约束管的是重复索引本身完全不管数据是否重复。搞混这两者设计表的时候就会犯一个典型错误——为了给某字段加索引顺手把它设成UNIQUE结果业务上允许重复的数据全被拦下了。还有一个容易混淆的是触发器。触发器也能做数据校验在INSERT或UPDATE之前用NEW.xxx判断要不要抛异常。但从我的经验来看触发器的隐式行为太多排查问题时要额外翻触发器定义还会增加每次DML的执行开销。更重要的是触发器的校验逻辑和表结构是分离的换一个环境部署时容易漏掉。声明式约束写在表定义里跟着DDL走迁移、备份、重建表都不会丢。所以我的结论是能用约束表达的数据规则坚决不用触发器约束表达不了的复杂逻辑优先放到应用层而不是堆到数据库里。2. 六种约束逐一拆解语法、行为和应用场景2.1 NOT NULL与DEFAULT非空与兜底成对出现更稳NOT NULL是入门级约束含义简单到不需要解释这一列不能为空。但实际设计里有个细节值得单独说——NOT NULL最好和DEFAULT一起出现。为什么因为一个字段定义了NOT NULL却不给默认值INSERT语句一旦没传这个字段数据库就会报错。这是严格但很多时候会给业务方添乱。比如用户表的status字段语义上是用户状态默认正常你既希望它不能为空又想省掉应用层每次都要显式传值那就在定义时写成status TINYINT NOT NULL DEFAULT 1。用DEFAULT兜底INSERT时忘记传它也不会报错还不会出现NULL值。DEFAULT本身当然也可以独立使用允许为空、但默认给一个值。不过那种用法要小心允许NULL的列配合DEFAULT实际插入NULL时数据库并不会用默认值替换NULL因为NULL是显式赋值DEFAULT只在INSERT语句省略该列时才生效。这一点很多人踩过坑——以为插入NULL会自动变成默认值结果查出来还是NULL。想彻底杜绝NULL只能NOT NULL加DEFAULT一起上。另外MySQL 8.0开始支持表达式默认值DEFAULT (UUID())这种写法也合法但常规场景还是用常量或CURRENT_TIMESTAMP最省心。2.2 UNIQUE与PRIMARY KEY唯一与主键别混为一谈UNIQUE约束声明这一列或这几列的组合不能有重复值。它和PRIMARY KEY有三点关键差异第一一张表只能有一个主键但可以有多个唯一约束第二主键隐含NOT NULL唯一约束却允许NULL而且在MySQL里唯一约束的NULL值是可以出现多次的——因为MySQL把NULL视为未知不参与重复判断。这一点在业务上经常造成意外你给手机号设置了UNIQUE但老系统里的空手机号记录可以存在无数条因为空值被写成了NULL而不是空字符串。想彻底堵住这个口子要么把列同时设为NOT NULL要么把空串作为标识而不是NULL。第三点差异是语义上的主键代表实体身份是其他表引用这张表的锚点唯一约束只是防重复。所以选主键时不要图省事直接拿业务字段比如手机号、身份证号当主键而要用与业务无关的自增ID或UUID。业务字段会变哪怕概率极小一旦变化所有引用它的外键都要改代价很高。自增ID加上主键约束就是给每行数据发了一张永久的身份证。2.3 FOREIGN KEY表间契约的代价与价值外键约束定义的是表与表之间的父子关系它保证子表里的某个字段值一定能在父表中找到对应记录。比如订单表里的user_id如果有外键指向用户表的id那么插入订单时MySQL会先检查用户表里是否存在这个id不存在就直接拒绝。这保证了数据在跨表维度上的完整性是数据合法性里最难在应用层做全面的那部分——因为你得在每一条写路径上都去查一遍父表而外键把这些逻辑交给了数据库统一托管。不过外键的代价也很明显。InnoDB在维护外键时会在父表上加共享锁高并发写场景下容易放大锁竞争分库分表后外键无法跨实例工作数据归档、批量导入时外键会让操作顺序变得绑手绑脚。这也是为什么很多互联网团队在生产库里很少用物理外键而是靠应用层保证引用关系表结构里只建普通索引。这个权衡没有绝对对错如果你的系统是中小规模、并发不高、规范性强物理外键能省掉大量脏数据如果是高并发、大规模、频繁做分库分表的场景逻辑外键更现实。2.4 CHECK8.0.16之后真正能用的规则守卫CHECK约束允许你自定义任意合法条件比如age BETWEEN 0 AND 120、stock 0、status IN (0, 1)。很多老教程会说MySQL的CHECK约束不生效那是历史遗留问题——5.7及更早版本确实会忽略它。但从MySQL 8.0.16开始CHECK约束已经被强制执行了这是一个很多人没跟上版本变化的知识点。CHECK的正确打开方式是把简单、稳定、与业务强相关的取值规则放在数据库里。举例来说订单状态的取值范围待支付、已支付、已发货、已完成、已取消通常不会经常变用CHECK (status IN (0,1,2,3,4))就能避免程序某个分支把状态写成5或-1。但要注意CHECK里别写子查询、别调用存储函数、别依赖时间函数比如NOW()这些操作在CHECK里受限或会产生不可预测的行为。复杂跨表校验请回到应用层。3. 建表阶段一份能直接抄作业的约束定义清单3.1 列级约束与表级约束的取舍逻辑约束的定义位置分为两种列级约束写在字段定义后面表级约束写在所有字段定义完之后。列级约束最常用的是NOT NULL、DEFAULT、AUTO_INCREMENT以及单个字段上的UNIQUE或PRIMARY KEY。表级约束则用来定义联合主键、联合唯一、外键以及命名更清晰的CHECK约束。一个经验是只要能明确命名优先用表级约束。列级约束系统会自动生成名字比如PRIMARY、uk_列名这种后期定位问题或删除约束时不够直观表级约束可以写成CONSTRAINT uk_order_user_product UNIQUE (user_id, product_id)这种一眼能看懂的形式。表级约束还有一个不可替代的场景——联合唯一。比如一个订单明细表里关心的是同一个订单不能重复记录同一个商品这约束的是(order_id, product_id)这个组合而不是单独某一列。这种需求只能用表级UNIQUE列级约束表达不了。3.2 完整建表示例用户表、商品表、订单表、订单明细表理论说再多不如直接看一套完整的建表语句。下面这个例子是一个精简版电商核心表我把这一节讲到的约束都用上了你可以直接保存下来当模板。-- 用户表 CREATE TABLE user ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(11) NOT NULL UNIQUE COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, CONSTRAINT chk_user_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; -- 商品表 CREATE TABLE product ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 商品ID, name VARCHAR(128) NOT NULL COMMENT 商品名称, price DECIMAL(10,2) NOT NULL COMMENT 单价单位元, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 库存, status TINYINT NOT NULL DEFAULT 1 COMMENT 上架状态1上架 0下架, CONSTRAINT chk_product_price CHECK (price 0), CONSTRAINT chk_product_stock CHECK (stock 0), CONSTRAINT chk_product_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表; -- 订单表 CREATE TABLE orders ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID, user_id INT UNSIGNED NOT NULL COMMENT 下单用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 订单总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id), CONSTRAINT chk_orders_status CHECK (status IN (0, 1, 2, 3, 4)), CONSTRAINT chk_orders_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; -- 订单明细表 CREATE TABLE order_item ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 明细ID, order_id INT UNSIGNED NOT NULL COMMENT 订单ID, product_id INT UNSIGNED NOT NULL COMMENT 商品ID, quantity INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价快照, 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), CONSTRAINT chk_item_price CHECK (price 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这套建表语句有几个细节值得品味。第一订单明细表的price字段我单独存了成交时的单价快照不直接引用商品表的当前价因为商品价格会变订单历史不能跟着变。第二每个外键都建立了父子关系删用户时如果有订单会被外键拦住这保证统计报表不会突然出现悬挂数据。第三CHECK后面的命名都带着表名和字段名以后排查报错一眼就能看出是哪个规则被违反。3.3 约束命名规范三个月后你还能看懂的约束命名命名这件事前期不重视、后期火葬场。MySQL默认的约束名像PRIMARY、列名这种还好但外键和CHECK如果不显式命名系统自动生成的名字是一长串随机字符。等到你执行ALTER TABLE DROP FOREIGN KEY的时候得先去information_schema查名字非常痛苦。我一般遵循这套命名规则约束类型命名格式示例主键PRIMARY系统默认为PRIMARY唯一约束uk_表名_字段名uk_user_phone外键fk_子表名_父表名fk_orders_userCHECKchk_表名_字段名chk_user_status这套规则的好处是别人读你的建表语句时看到chk_product_price不用查定义就能猜到是商品价格上的检查规则。更重要的是约束名在information_schema.TABLE_CONSTRAINTS里可以模糊搜索团队排障时能快速定位。4. 后期维护ALTER TABLE修改约束与高频报错排查链4.1 添加、删除、修改约束的标准操作约束不是一锤子买卖业务迭代时经常要对已有表补约束。这里给你整理一张速查表操作目标标准SQL添加唯一约束ALTER TABLE user ADD CONSTRAINT uk_user_phone UNIQUE (phone);添加外键ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id);添加CHECKALTER TABLE orders ADD CONSTRAINT chk_orders_status CHECK (status IN (0,1,2,3,4));删除主键ALTER TABLE user DROP PRIMARY KEY;删除唯一约束ALTER TABLE user DROP INDEX uk_user_phone;删除外键ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;删除CHECKALTER TABLE orders DROP CHECK chk_orders_status;修改字段为空性ALTER TABLE user MODIFY COLUMN phone VARCHAR(11) NOT NULL;修改默认值ALTER TABLE user ALTER COLUMN status SET DEFAULT 1;删除默认值ALTER TABLE user ALTER COLUMN status DROP DEFAULT;有几个坑单独提醒。第一删除唯一约束的语法是DROP INDEX而不是DROP CONSTRAINT很多人第一次会写错。第二给已有数据的大表加CHECK约束时MySQL会对全表做一遍校验几百万行的表会锁住最好在业务低峰期操作。第三修改字段为空性时MODIFY COLUMN必须把字段的完整定义重新写一遍比如VARCHAR(11)漏掉就会把类型或长度改了出问题连排查方向都没有。第四给已有数据的表加唯一约束如果数据本身已经重复DDL会直接报错不会帮你清洗数据。4.2 从报错信息反推约束类型五类高频错误排查约束的报错信息基本都带错误码记住常见的几个排障速度会快很多。错误码典型报错内容约束类型含义1062Duplicate entry xxx for key uk_user_phoneUNIQUE插入重复值1048Column phone cannot be nullNOT NULL插入了NULL1452Cannot add or update a child row: a foreign key constraint failsFOREIGN KEY外键引用的父记录不存在3819Check constraint chk_product_price is violatedCHECK不满足CHECK条件1364Field phone doesnt have a default valueNOT NULLDEFAULT缺失INSERT时省略了该字段且无默认值排查思路上我的习惯是先在应用程序日志里找到报错片段把错误码记住然后到数据库端用SHOW ENGINE INNODB STATUS或information_schema去查具体约束名。外键错误1452有时会带出详细的嵌套信息因为InnoDB会把外键链路上的约束名都打印出来顺着它查就能知道是哪张表和哪张表产生了断链。最隐蔽的是1048这一类程序正常跑了好几个月突然有一天某条数据插入报错查了半天发现是上游接口把字段值传成了NULL。这时你要做的不是去掉NOT NULL约束而是回头改上游传参约束只是把问题暴露出来而已。4.3 为什么生产环境我很少启用物理外键这个话题在前面提过一嘴这里展开说。我待过的团队里确实有把外键禁用的传统新来的开发甚至会被规则约束掉。禁用的核心原因是性能与扩展性问题。InnoDB的外键机制在每次INSERT、UPDATE时都要去父表检查引用关系并且按一定规则加锁这在并发写入量大的表上会放大锁等待。更麻烦的是做分库分表后订单和用户可能落到了不同的数据库实例物理外键根本没法跨实例工作只能靠应用层在写入时先查一次用户表。但这不意味着外键一无是处。如果你的业务是后台管理系统、企业内部系统、低并发场景物理外键带来的完整性收益远大于性能消耗。我见过不少系统因为不用外键在删除用户时忘了清理关联数据导致订单表出现孤儿记录统计报表对不上账。数据完整性这种东西不出问题就算了一出问题就是连环事故。所以我的建议不是永远不用外键而是评估完数据分布和并发量后再决定用物理外键还是逻辑外键。小系统用物理外键真出问题也好查大系统用逻辑外键靠规范流程和数据巡检兜底。5. 实战验证造一条非法数据看约束怎么拦截5.1 数据插入验证每一条非法数据都被精准拦下建完上面的表我们把各种非法数据挨个插一遍看看约束是不是真的在工作。-- 场景1手机号填NULL违反NOT NULL INSERT INTO user (phone) VALUES (NULL); -- 报错Column phone cannot be null -- 场景2手机号重复违反UNIQUE INSERT INTO user (phone, email) VALUES (13800138000, atest.com); INSERT INTO user (phone, email) VALUES (13800138000, btest.com); -- 报错Duplicate entry 13800138000 for key user.uk_user_phone -- 场景3商品价格为负数违反CHECK INSERT INTO product (name, price, stock) VALUES (测试商品, -10, 100); -- 报错Check constraint chk_product_price is violated -- 场景4订单引用不存在的用户违反FOREIGN KEY INSERT INTO orders (user_id, total_amount) VALUES (9999, 100.00); -- 报错Cannot add or update a child row: a foreign key constraint fails -- 场景5订单明细数量为0违反CHECK INSERT INTO order_item (order_id, product_id, quantity, price) VALUES (1, 1, 0, 10.00); -- 报错Check constraint chk_item_quantity is violated每个错误码都指向明确的约束规则而且报错信息里直接写着约束名。这意味着你的日志告警系统可以直接拿约束名做聚合统计看哪些规则被频繁触发反向推动解决上游数据质量问题。5.2 组合约束在真实业务中的配置套路从上面的例子能看出约束经常是多个一起作用在同一张表上。实战中还有一些组合套路值得借鉴。一个是业务状态机模式订单状态除了用CHECK限制取值范围还用外键确保订单归属某个真实用户再用DEFAULT给初始状态兜底。三件事分别由三种约束负责而不是全部塞到应用层。另一个是金额与数量模式所有涉及钱和数量的字段统一用DECIMAL加CHECK(字段 0)。好处是哪怕某个业务分支漏了判断数据库也会拒绝负数的出现。我曾经在报表里发现过负数的退款金额排查后是程序月结脚本的SQL写错导致扣成了负数这类问题全靠CHECK拦截。还有一个是快照模式订单明细保存了quantity和price的快照不随商品表变化。这时不需要外键指向商品表但要建立逻辑映射关系一般会加一个INDEX(product_id)方便查询但物理外键要看场景。5.3 给初学者的三条约束设计心法聊了这么多最后给刚开始接触MySQL数据库基础的读者提炼三条心法。第一能声明就不要手写。数据合法性规则能声明为约束的优先声明。约束写在DDL里跟随表结构走天然具备可迁移、可追溯的特性比藏在业务代码深处的if判断要靠谱得多。第二约束宁严勿松但上线前一定要评估存量数据。给一个已经跑了两年的表加NOT NULL或CHECK务必先查一遍全表有没有历史脏数据否则DDL执行到一半失败表还被锁住。安全的做法是先备份再在低峰期操作。第三主键和唯一约束是底线。不管团队规范多松散主键必须有唯一约束按业务语义来。哪怕你公司没有DBA哪怕代码只有几千行只要用到了MySQL这两条就是最便宜的保险。我见过太多系统问题不是出在高深架构上而是出在表结构最初那十几行CREATE TABLE语句没有认真写。约束这种东西建表时多花五分钟开发时可以少熬夜两小时。希望这篇文章能帮你把MySQL表的约束真正用起来而不是停留在会写语法却不知道何时该用的阶段。