尧图网站建设 
1. 先搞清楚MySQL表的约束到底是干嘛的很多人学MySQL时把约束当成建表语句里可有可无的“装饰品”觉得“反正我代码里控制了数据格式数据库约束写不写无所谓”。这个想法害了很多人。数据从应用层进来的时候往往已经过了一层校验但应用层不等于数据库层——总有人直连数据库改数据总有历史数据导入出问题总有没有走你接口的定时任务在偷偷写库。这个时候约束就是数据库层唯一的“守门员”。简单点讲表的约束是一组声明式的规则告诉MySQL“这个字段能存什么、不能存什么、和其他字段或表有什么关系”。它干的事情不是给你加索引提速而是保证数据的正确性、完整性和一致性。说人话就是不合格的数据插入时直接被你拦在门外根本进不了表。MySQL里面常见的约束主要有六种非空约束NOT NULL、默认值约束DEFAULT、唯一约束UNIQUE、主键约束PRIMARY KEY、检查约束CHECK、外键约束FOREIGN KEY。再加上一个经常一起出现的自增属性AUTO_INCREMENT基本就把一张表该有的“规矩”说全了。这篇文章我从设计者的视角把这六类约束逐一拆开讲。每个约束我会说清楚三件事它到底管什么、底层是怎么实现的、实际建表时怎么用才不踩坑。最后附上我在实际项目中遇到的几个典型案例和排查过程。看完你再去建表应该能明显感觉到底气不一样了。2. 约束的整体设计思路不是越多越好而是“该卡的卡死该放的放开”在设计一张表的约束时很多新手容易走两个极端要么完全不加约束表建得跟记事本一样随便要么满屏约束每一个字段都又是非空又是默认值又是唯一的结果插数据的时候报错报到你怀疑人生。2.1 约束的本质把业务规则下沉到数据层约束在数据库里的角色很像你租房时签的合同。合同上写清楚“能不能养宠物、能不能改造墙体、租金哪天交”房东不用每天过来盯你你也不会因为“不知道规矩”而犯错。数据库约束就是这个合同把业务规则固化在结构里而不是依赖每个人写代码时都自觉。我一直强烈建议凡是“绝对不可违反”的规则尽量用数据库约束来做而不是放在应用层用if-else判断。比如用户注册时邮箱不能重复这属于硬规则。应用层判断有并发问题两个请求同时查到“不存在”就会同时插入一前一后死锁概率虽然低但不为0。而数据库唯一约束在底层就是唯一索引并发插入时InnoDB存储引擎会做冲突检测这个能力你应用层要自己写代价高得多。2.2 约束的三种类型表内、表间、数据库行为层面的兜底我自己习惯把约束分成三个维度来理解这样遇到实际业务问题能快速定位该加哪种约束。第一层表内单列约束。管的是“这个字段本身的数据形态”比如非空约束、默认值、自增、唯一、主键、检查约束。这一层负责把单个字段的底线画清楚简单粗暴也是用得最多的一层。第二层表间引用约束。管的是表和表之间的关系最常见的就是外键。比如订单表里的用户ID必须存在于用户表里否则就是“孤儿订单”。这个规则如果靠应用层保证不管并发还是删除场景都很容易漏。第三层行为兜底约束。像非空、默认值这类约束本质上是数据库帮你做最后一道兜底你漏传了字段数据库就自己给你填一个默认值或者直接报错拒绝插入。这种“宁可麻烦一点不能悄悄出错”的思路才是约束真正值钱的地方。2.3 设计约束时的三个原则第一个原则主键尽量选择无业务含义的整数自增列或UUID不要拿身份证号、手机号这种业务字段当主键。为什么业务字段可能变比如手机号可能注销换绑一旦变了所有外键引用的地方都要跟着改你会被牵连得很惨。第二个原则唯一约束要谨慎加。唯一约束是“有索引成本”的约束每一个唯一约束都会自动生成一个索引。一张表上加了七八个唯一约束写入性能会明显下降。所以只在真正需要“唯一性保证”的字段上加别顺手什么都搞个唯一。第三个原则不能靠约束去兜底所有异常。约束能保证数据不进坏数据但保证不了“业务上该有的数据都有”。比如一个订单必须有至少一个明细这是跨表的多行完整性约束MySQL单表约束能力管不了这种场景得靠事务和业务逻辑来配合。约束是安全网不是保险柜。3. 逐类拆解六大约束的底层原理与实操要点这一块是全文的主菜每一种约束我都配合具体的建表语句和实际业务场景来讲。3.1 非空约束NOT NULL最容易被忽略却最影响查询效率非空约束的语法很简单CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL );它的意思很直白这个字段不允许为NULL插入或更新时如果该字段为NULL直接报错。很多人以为非空约束只是数据完整性问题其实它严重影响查询效率。MySQL的索引对NULL值处理是有额外开销的。如果你允许一个字段为NULL索引中就需要额外存储一个“是否为NULL”的标识位。InnoDB官方文档里也说了索引列允许NULL的话每一行要多一个bit标记ROW_FORMAT紧凑模式下这个额外标记还会让索引体积变大。数据量大了之后索引扫描的范围会变大查询就慢了。所以我在实际建表时几乎都对字段加上NOT NULL。你说一个用户表的昵称字段用户没填昵称时你存NULL和存空字符串对业务来说效果差不多但NULL会带来额外的索引负担。所以能不用NULL就不用NULL实在需要“未填写”这个状态就搞一个默认值。3.2 默认值约束DEFAULT数据库帮你兜底漏传字段默认值约束和非空约束配合使用是绝佳搭档CREATE TABLE order_info ( id BIGINT PRIMARY KEY, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );这段建表语句里status字段不传值时会自动填0create_time不传值时会自动填当前时间。这带来的好处是应用层只需要关注业务字段状态、时间这种基础设施字段的初始化让数据库自己搞定。这里有一个我踩过的大坑DEFAULT子句后面能不能用函数要看你用的什么版本。MySQL 8.0.13之前DEFAULT只能接常量8.0.13开始支持把表达式作为默认值。我在老项目里看到有人写DEFAULT (UUID())那是因为在MySQL 5.7.17以后支持的括号写法但不是每个版本都能直接跑。最简单的判断标准就是在建表后执行一次INSERT测试看看能否成功。另外默认值约束里还有一个常见误会它并不会自动填充NULL。你显式指定插入NULL时约束不会拦你除非这个字段同时设置了NOT NULL。想明白这一点很重要MySQL的默认值只在“没给这个列传值”的时候生效而不是“传了NULL的时候帮你补一个默认值”。3.3 唯一约束UNIQUE防重名、防重复提交的终极武器唯一约束确保一张表中的某个字段或某几个字段的组合不能出现重复值。CREATE TABLE user ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );也可以把多个字段组合成一个联合唯一约束CREATE TABLE user_tag ( user_id INT NOT NULL, tag_id INT NOT NULL, UNIQUE KEY uk_user_tag (user_id, tag_id) );这里要强调一个反直觉的点唯一约束允许出现多个NULL值。如果你在email字段上建立了唯一约束两个用户注册时不填邮箱插入两行空的email不会触发唯一冲突。原因很简单MySQL认为NULL和NULL是不相等的NULL不是值是“未知”两个未知没法判断是否相同。这个特性有好有坏。好处是逻辑上说得通坏处是你想用唯一约束保证“email不能重复”时空值会让你的防重失效。解决方案很简单结合NOT NULL一起用——如果email不允许为空那加上NOT NULL后再加唯一约束才能真正保证“非空且唯一”双重保险。还有一个细节唯一约束在MySQL里是通过唯一索引实现的。所以你执行SHOW INDEX FROM user;能看到唯一约束对应的索引记录。这也意味着每个唯一约束都有索引开销别滥用。3.4 主键约束PRIMARY KEY一张表只能有一个但它最值得你花心思主键约束从性质上说是非空唯一的组合但它又有自己独特的地位一张表只能有一个主键而且InnoDB默认会拿主键来组织数据存储。InnoDB是索引组织表IOT数据行本身就是以主键为Key的B树叶子节点。主键的形态直接影响数据文件的物理组织方式。如果你建表时没显式指定主键InnoDB会自己偷偷选一个“隐式主键”来组织数据。这个隐式主键你看不到也没法控制顺序会产生不必要的空间浪费。所以每张表必须有主键这不是规范问题是InnoDB存储引擎的结构要求。主键的选择上我的建议是三个备选方案自增整数主键适用绝大多数场景写入性能最优。UUID或雪花ID主键适合分布式系统、不需要数据库生成ID的场景。但要注意UUID是随机字符串作为主键时B树插入会产生大量页分裂写性能比自增整数差很多。业务键主键比如仅有一张“系统配置表”每条记录天然有唯一标识比如配置名。这种表可以用业务字段做主键但别用在会频繁修改业务键值的场景。自增整数主键是最省心的方案但有一个隐患别忘了自增ID在MySQL重启后可能回退特别是老版本5.7之前的场景如果你在业务里把ID拿来当订单号用会出大事。自增ID只应该当主键用不要去兼当业务编号。3.5 检查约束CHECKMySQL 8.0.16之后才是真正能用的检查约束是很多教材里都会写但在MySQL里属于“身世曲折”的一个约束。在MySQL 8.0.15及之前CHECK约束的语法虽然能解析但完全不强制。什么意思你写了CHECK (age 0)插一条age-5的数据照样能进去MySQL完全不鸟你。这个历史坑导致很多老程序员根本不在MySQL里用CHECK约束全部用触发器或应用层判断替代。到了MySQL 8.0.16官方才真正实现了CHECK约束的强制检查。所以如果你用的是MySQL 8.0.16或更高版本可以放心使用CREATE TABLE student ( id INT PRIMARY KEY, age INT, sex CHAR(1), CONSTRAINT chk_student_age CHECK (age BETWEEN 0 AND 150), CONSTRAINT chk_student_sex CHECK (sex IN (M, F)) );如果你还在用5.7或者8.0.16之前的老版本CHECK约束就形同虚设。这时候要么靠应用层要么用触发器要么就用ENUM枚举来约束取值范围。所以先查一下你的版本再决定要不要依赖CHECK。另外即使MySQL 8.0.16支持CHECK了也别把太复杂的校验逻辑塞进去。比如“性别只能是M或F”、“年龄在0到150之间”这种简单范围判断很适合CHECK。但如果你的规则里动不动就要查别的表、做子查询、算函数那还是老实用应用层写吧CHECK不适合干这种重活。3.6 外键约束FOREIGN KEY保证表间引用的完整性但用之前要想清楚外键约束让表之间产生关联保证子表里的引用一定存在于父表中CREATE TABLE order_detail ( id INT PRIMARY KEY, order_id INT NOT NULL, product_name VARCHAR(100) NOT NULL, CONSTRAINT fk_order_detail_order FOREIGN KEY (order_id) REFERENCES order_info(id) ON DELETE CASCADE ON UPDATE CASCADE );外键约束在InnoDB引擎下会自动在子表的外键列上建立索引这个索引的主要作用是加速外键检查时的查询。所以如果你在MySQL中用了外键不需要手动再给外键字段加索引加了也是多余。这里有两件事要说清楚第一ON DELETE和ON UPDATE的连锁策略。CASCADE父表删除时子表跟着删更新时跟着更新。适合“订单删了订单详情也删”的场景。SET NULL父表删除时子表的外键列置为NULL。前提是外键列允许NULL。RESTRICT/NO ACTION父表有子记录引用时禁止删除或更新。选策略时要想清楚业务逻辑。我有一次把订单详情的外键设置为ON DELETE CASCADE后来做数据清理时误删了一个订单结果这个订单下所有售后单都被级联删了差点出生产事故。ON DELETE CASCADE必须慎用上线前一定要想清楚级联范围。第二外键到底该不该用业界一直有争论。高性能架构这边很多团队刻意不用外键理由是外键检查有额外开销高并发写入时会影响性能数据迁移、分库分表时外键会带来一堆麻烦多数据源或微服务架构里表根本不在同一个库里外键也没法跨库生效。但如果你做的是中小型项目、内部管理系统或者对数据一致性要求很高那我建议该用外键就用。数据库外键把“不能引用不存在的用户”这种规则固化在底层比你写一百行应用层校验靠谱得多。至少我在做管理后台这种读写不高、一致性要求高的系统时从来不会刻意回避外键。4. 完整实操从零开始设计一张带全套约束的用户订单表前面把每种约束单独讲了一遍这一节我们实战演练。假设我们要设计一个电商系统的核心表用户表、订单表、订单明细表。重点演示约束的完整建表写法以及各种情况下的约束效果表现。4.1 建表前的设计准备先列业务规则再写SQL我建表有个习惯先在纸上把业务规则写清楚再翻译成建表语句。不要上来就写SQL很容易漏约束。用户表的业务规则先列一下用户ID必须唯一作为主键手机号必填且不能重复注册时校验过邮箱允许不填但填了就不能和别人重复昵称必填允许重复用户状态默认是1正常订单表的规则订单号唯一订单必须属于一个存在的用户订单金额必须大于0订单状态默认0待支付下单时间由数据库自动填订单明细表的规则明细ID主键明细必须属于订单商品名称必填数量必须大于0订单删除时明细跟着删4.2 建表语句与约束设计逐行解读把上面的规则落到MySQL 8.0建表语句里-- 用户表 CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, nickname VARCHAR(50) NOT NULL COMMENT 昵称, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;-- 订单表 CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(64) NOT NULL COMMENT 订单号, user_id INT UNSIGNED 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 下单时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT chk_order_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;-- 订单明细表 CREATE TABLE order_detail ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 明细ID, order_id BIGINT UNSIGNED NOT NULL COMMENT 所属订单ID, product_name VARCHAR(200) NOT NULL COMMENT 商品名称, quantity INT UNSIGNED NOT NULL COMMENT 数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_order_id (order_id), CONSTRAINT fk_detail_order FOREIGN KEY (order_id) REFERENCES order_info (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT chk_detail_quantity CHECK (quantity 0), CONSTRAINT chk_detail_price CHECK (price 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这三个表把前面讲的六种约束基本都覆盖了有几个设计点单独说下主键类型的选择。用户表用INT UNSIGNED自增订单表和明细表用BIGINT UNSIGNED自增。为什么订单表用BIGINT因为订单量通常比用户量增长快INT最大约21亿看似够用但已经有不少公司踩过“订单表主键逼近INT上限”的坑。与其到时候改表不如一开始就上BIGINT成本几乎为零。外键的删除策略。订单表对用户表的外键ON DELETE用了RESTRICT意思是一个用户有订单时不允许直接删除用户。这符合业务逻辑下单了的人不能说删就删。订单明细对订单的ON DELETE用了CASCADE订单删了明细跟着删这符合“订单不存在了明细没意义”的业务逻辑。DECIMAL别用FLOAT。金额字段必须用DECIMAL(10,2)这个和约束无关但是一个非常重要的小经验。用FLOAT存金额0.1 0.2会算出0.30000000000000004这种值做金额计算迟早翻车。4.3 约束生效演示插非法数据试试看表建好后来点实际的验证。下面这些SQL都会报错报错信息会告诉你到底是哪条约束把数据拦下了。先往用户表插一条正常数据INSERT INTO user (phone, email, nickname) VALUES (13800138000, testexample.com, 张三); -- 执行成功接着插一条手机号重复的INSERT INTO user (phone, email, nickname) VALUES (13800138000, otherexample.com, 李四); -- ERROR 1062 (23000): Duplicate entry 13800138000 for key user.uk_phone报错里清楚写出了冲突的索引名uk_phone。这个报错信息的格式你最好熟悉下排查问题的时候第一步就是看是哪个索引/约束报的错。再试试邮箱不填的情况插两行NULL邮箱的用户INSERT INTO user (phone, nickname) VALUES (13800138001, 王五); INSERT INTO user (phone, nickname) VALUES (13800138002, 赵六); -- 都能成功看吧唯一约束uk_email没有拦住NULL。这就是前面讲过的NULL之间不算重复。现在给订单表插一条引用不存在用户的订单INSERT INTO order_info (order_no, user_id, total_amount) VALUES (202501010001, 9999, 100.00); -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails外键约束生效因为用户表里没有ID9999的用户。再试试违反金额检查约束INSERT INTO order_info (order_no, user_id, total_amount) VALUES (202501010002, 1, -50.00); -- ERROR 3819 (HY000): Check constraint chk_order_amount is violated.MySQL 8.0.16以后CHECK约束的报错长这样清晰得让人感动。你要是用5.7这条-50的订单就真的进去了这也是为什么我一直强调版本的重要性。最后试一下外键的级联删除-- 先给用户1创建一个订单和明细 INSERT INTO order_info (order_no, user_id, total_amount) VALUES (202501010003, 1, 200.00); INSERT INTO order_detail (order_id, product_name, quantity, price) VALUES (LAST_INSERT_ID(), 手机壳, 2, 99.90); -- 删除这个订单 DELETE FROM order_info WHERE id LAST_INSERT_ID(); -- 明细被级联删掉了 SELECT * FROM order_detail WHERE order_id LAST_INSERT_ID(); -- 查询结果为空CASCADE生效了明细自动跟着订单走了。5. 常见问题与排查技巧实录这一段我从实际工作里挑出几个高频踩坑场景都是真实案例整理成速查表供你参考。5.1 问题速查表报错信息与解决方案对照报错信息触发器约束解决方案ERROR 1062 (23000): Duplicate entry xxx for key uk_xxx唯一约束或主键冲突先查已存在的值判断是业务重复还是历史数据问题再决定是修改数据还是删除约束ERROR 1048 (23000): Column xxx cannot be null非空约束检查插入语句中该字段是否没传值或传了NULLERROR 1364 (HY000): Field xxx doesnt have a default value非空字段未传值且无默认值要么插入时提供值要么给字段加DEFAULTERROR 1452 (23000): Cannot add or update a child row外键约束检查子表中外键字段的值是否存在于父表ERROR 1451 (23000): Cannot delete or update a parent row外键的RESTRICT策略先处理子表引用数据再操作父表ERROR 3819 (HY000): Check constraint chk_xxx is violated检查约束校验字段值是否满足CHECK条件这些报错提醒收藏一下出问题时按图索骥能省不少排查时间。5.2 坑一本来是唯一约束却放进来一堆NULL和空字符串我在一个用户系统里见过这么一张表email字段建了唯一约束但字段本身允许NULL。结果运营从Excel批量导入用户数据时一堆用户没填邮箱全部存成NULL。数据倒是进来了但后面要做一个“给没填邮箱的用户发提醒”的功能写SQL时发现NULL判断怎么写都别扭还得用COALESCE函数。更严重的是有些导入工具会把Excel空单元格转成空字符串而不是NULL。空字符串和NULL不一样空字符串是“值”多个空字符串会被唯一约束拦截掉导致批量导入失败一半。处理办法就一句话实在无法保证一个字段总有值就给它设计一个业务上有意义的默认值而不是用NULL表示“没有”。比如邮箱没填业务上可以让它存一个“未填写”的占位字符串再加唯一约束就不会出幺蛾子了。5.3 坑二大表加CHECK约束的版本兼容性这是我帮朋友排查过的一个生产事故。他们的项目跑在MySQL 5.7上有人为了提高数据准确性在ALTER TABLE语句里加了一个CHECK约束。执行成功大家很开心以为数据从此有了保障。结果第二天发现插入的负数金额照样进去了。原因就是前面反复强调的MySQL 5.7根本不强制CHECK约束你写了它也不管你。所以排查问题的时候一定先确认版本SELECT VERSION();8.0.16之前别依赖CHECK约束8.0.16开始可以正常使用。但要注意即使8.0.16以后CHECK里的表达式也有限制比如不能引用其他表的字段、不能用存储函数等。真遇到复杂校验还是用触发器或者应用层判断更稳。5.4 坑三外键导致的分库分表和迁移噩梦外键在高并发、分库分表场景下非常麻烦。分库分表后A表的数据在库1B表的数据在库2MySQL的跨库外键根本没法用。这也是为什么很多大厂规范明确规定“线上业务表不要使用外键”。我自己在一次工单系统的拆分改造中体会过这种痛。原来一张订单表和用户表有外键关联拆分后用户表被挪到另一个库订单表里的user_id就失去了外键约束。数据一致性只能靠应用层补偿。好在拆分前我们做了数据完整性检查没有产生孤儿数据。我的经验是做项目架构时提前想清楚未来会不会分库分表。如果是简单的单库项目、内部系统外键随便用方便省心。如果项目规划体量很大一开始就别建外键用应用层保证一致性省的以后拆库时把建表语句改个底朝天。5.5 坑四修改约束的正确姿势很多人以为约束建好了就不能动了实际上MySQL是完全支持ALTER TABLE修改约束的。这里列出几个常用语法-- 添加唯一约束 ALTER TABLE user ADD UNIQUE KEY uk_phone (phone); -- 删除唯一约束 ALTER TABLE user DROP INDEX uk_phone; -- 添加检查约束 ALTER TABLE order_info ADD CONSTRAINT chk_amount CHECK (total_amount 0); -- 删除检查约束 ALTER TABLE order_info DROP CHECK chk_amount; -- 删除主键约束慎用 ALTER TABLE user DROP PRIMARY KEY; -- 修改默认值 ALTER TABLE user ALTER COLUMN status SET DEFAULT 1;有几个坑提醒一下第一给已有大量数据的表加约束前先检查数据是否符合约束条件。比如你给phone字段加唯一约束结果表里已经有两行重复的手机号ALTER table直接失败。先查重再添加。SELECT phone, COUNT(*) FROM user GROUP BY phone HAVING COUNT(*) 1;第二删主键前先看看有没有外键引用它。有外键引用时你要先把外键约束也一起清理掉否则会报错。第三约束命名要规范。我习惯用“约束类型_表名_字段名”的格式uk_user_phone、fk_order_user、chk_order_amount。这样报错信息一眼就知道是哪里出了问题。没名字或者名字随意的约束出问题时看报错根本不知道是谁。6. 外键到底用不用我的决策方法论关于外键前面讲了一些利弊这里系统地说说我的决策思路算是一套个人化的判断框架。以下情况我推荐用外键项目体量不大单库部署并发写入量不高。数据一致性要求极高比如财务系统、订单系统、工单系统。团队开发水平参差不齐用数据库硬约束可以挡住粗心的代码。数据经常被外部工具直接操作比如DBA直连修数据、数据迁移导入这时外键能防止“改完父表忘了子表”的人为失误。以下情况我建议不用外键系统需要高频写入外键检查的每一次插入/更新都要去父表做存在性检查这个额外查询在极高并发下会被放大。规划中要做分库分表跨库外键根本不可用。表结构经常变更比如频繁添加字段、重命名表外键会拖累DDL操作。在不用外键的情况下业务完整性怎么保证我的做法是应用层在事务里先查父表记录是否存在再由代码逻辑保证数据一致。虽然多写几行代码但换来了高并发下的写入自由度。外键用不用没有标准答案。选型时最怕的是不清楚自己的场景盲目模仿大厂。大厂说“不用外键”是因为他的量级和架构决定了外键不可用你一个日活几千的管理系统跟着不用外键只会白白损失一层数据保障。反过来说你要是做一个高并发的秒杀系统还坚持每个表都挂外键那可能连压测都过不了。场景不同结论就不同。7. 索引和约束的关系很多人分不清的隐性知识点最后补一个非常容易混淆的知识点约束和索引到底什么关系一句话总结主键约束、唯一约束在MySQL中都是通过索引来实现的。你建了一个主键就建了一个主键索引聚簇索引你建了一个唯一约束就建了一个唯一索引。外键约束则会自动在子表外键列上建一个普通索引。所以你会发现SHOW INDEX FROM 你的表;查出来的索引里有些并不是你自己建的而是约束“附赠”的。清晰理解这种关系有助于你判断当表已经在某些字段上有唯一约束时不需要再为它单独建立一个普通索引因为唯一索引本身就能加速该字段的等值查询。非空约束、默认值约束和检查约束则不走索引它们纯粹是逻辑层面的检查不生成任何物理结构。这也能解释一个经验法则一张表上没必要看到字段就加索引很多字段的索引需求其实是约束已经帮你覆盖了的。仔细梳理一遍现有表的约束和索引能帮你砍掉不少冗余索引写入性能立马能感觉到改善。我个人在实际操作中的体会是约束和索引是MySQL里最容易“相爱相杀”的一对概念。真正理解了“约束决定索引、索引服务约束”的关系你看到一张表的SHOW CREATE TABLE输出就能大概推测出它的查询热点和写入代价。最后再分享一个小技巧拿到任何一张别人写的表第一件事就是执行SHOW CREATE TABLE 表名;看它的约束设计。从约束设计的严谨程度基本能判断出写这个表的人对业务的理解深度和工程素养。数据库约束设计得好后面所有写代码的人都会省心设计得烂各种数据异常就会层出不穷。表结构是编码的灵魂约束是表结构的骨架。骨架正了上面的业务逻辑才立得住。 MySQL表约束全解析：六大约束的原理、应用与避坑指南
1. 先搞清楚MySQL表的约束到底是干嘛的很多人学MySQL时把约束当成建表语句里可有可无的“装饰品”觉得“反正我代码里控制了数据格式数据库约束写不写无所谓”。这个想法害了很多人。数据从应用层进来的时候往往已经过了一层校验但应用层不等于数据库层——总有人直连数据库改数据总有历史数据导入出问题总有没有走你接口的定时任务在偷偷写库。这个时候约束就是数据库层唯一的“守门员”。简单点讲表的约束是一组声明式的规则告诉MySQL“这个字段能存什么、不能存什么、和其他字段或表有什么关系”。它干的事情不是给你加索引提速而是保证数据的正确性、完整性和一致性。说人话就是不合格的数据插入时直接被你拦在门外根本进不了表。MySQL里面常见的约束主要有六种非空约束NOT NULL、默认值约束DEFAULT、唯一约束UNIQUE、主键约束PRIMARY KEY、检查约束CHECK、外键约束FOREIGN KEY。再加上一个经常一起出现的自增属性AUTO_INCREMENT基本就把一张表该有的“规矩”说全了。这篇文章我从设计者的视角把这六类约束逐一拆开讲。每个约束我会说清楚三件事它到底管什么、底层是怎么实现的、实际建表时怎么用才不踩坑。最后附上我在实际项目中遇到的几个典型案例和排查过程。看完你再去建表应该能明显感觉到底气不一样了。2. 约束的整体设计思路不是越多越好而是“该卡的卡死该放的放开”在设计一张表的约束时很多新手容易走两个极端要么完全不加约束表建得跟记事本一样随便要么满屏约束每一个字段都又是非空又是默认值又是唯一的结果插数据的时候报错报到你怀疑人生。2.1 约束的本质把业务规则下沉到数据层约束在数据库里的角色很像你租房时签的合同。合同上写清楚“能不能养宠物、能不能改造墙体、租金哪天交”房东不用每天过来盯你你也不会因为“不知道规矩”而犯错。数据库约束就是这个合同把业务规则固化在结构里而不是依赖每个人写代码时都自觉。我一直强烈建议凡是“绝对不可违反”的规则尽量用数据库约束来做而不是放在应用层用if-else判断。比如用户注册时邮箱不能重复这属于硬规则。应用层判断有并发问题两个请求同时查到“不存在”就会同时插入一前一后死锁概率虽然低但不为0。而数据库唯一约束在底层就是唯一索引并发插入时InnoDB存储引擎会做冲突检测这个能力你应用层要自己写代价高得多。2.2 约束的三种类型表内、表间、数据库行为层面的兜底我自己习惯把约束分成三个维度来理解这样遇到实际业务问题能快速定位该加哪种约束。第一层表内单列约束。管的是“这个字段本身的数据形态”比如非空约束、默认值、自增、唯一、主键、检查约束。这一层负责把单个字段的底线画清楚简单粗暴也是用得最多的一层。第二层表间引用约束。管的是表和表之间的关系最常见的就是外键。比如订单表里的用户ID必须存在于用户表里否则就是“孤儿订单”。这个规则如果靠应用层保证不管并发还是删除场景都很容易漏。第三层行为兜底约束。像非空、默认值这类约束本质上是数据库帮你做最后一道兜底你漏传了字段数据库就自己给你填一个默认值或者直接报错拒绝插入。这种“宁可麻烦一点不能悄悄出错”的思路才是约束真正值钱的地方。2.3 设计约束时的三个原则第一个原则主键尽量选择无业务含义的整数自增列或UUID不要拿身份证号、手机号这种业务字段当主键。为什么业务字段可能变比如手机号可能注销换绑一旦变了所有外键引用的地方都要跟着改你会被牵连得很惨。第二个原则唯一约束要谨慎加。唯一约束是“有索引成本”的约束每一个唯一约束都会自动生成一个索引。一张表上加了七八个唯一约束写入性能会明显下降。所以只在真正需要“唯一性保证”的字段上加别顺手什么都搞个唯一。第三个原则不能靠约束去兜底所有异常。约束能保证数据不进坏数据但保证不了“业务上该有的数据都有”。比如一个订单必须有至少一个明细这是跨表的多行完整性约束MySQL单表约束能力管不了这种场景得靠事务和业务逻辑来配合。约束是安全网不是保险柜。3. 逐类拆解六大约束的底层原理与实操要点这一块是全文的主菜每一种约束我都配合具体的建表语句和实际业务场景来讲。3.1 非空约束NOT NULL最容易被忽略却最影响查询效率非空约束的语法很简单CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL );它的意思很直白这个字段不允许为NULL插入或更新时如果该字段为NULL直接报错。很多人以为非空约束只是数据完整性问题其实它严重影响查询效率。MySQL的索引对NULL值处理是有额外开销的。如果你允许一个字段为NULL索引中就需要额外存储一个“是否为NULL”的标识位。InnoDB官方文档里也说了索引列允许NULL的话每一行要多一个bit标记ROW_FORMAT紧凑模式下这个额外标记还会让索引体积变大。数据量大了之后索引扫描的范围会变大查询就慢了。所以我在实际建表时几乎都对字段加上NOT NULL。你说一个用户表的昵称字段用户没填昵称时你存NULL和存空字符串对业务来说效果差不多但NULL会带来额外的索引负担。所以能不用NULL就不用NULL实在需要“未填写”这个状态就搞一个默认值。3.2 默认值约束DEFAULT数据库帮你兜底漏传字段默认值约束和非空约束配合使用是绝佳搭档CREATE TABLE order_info ( id BIGINT PRIMARY KEY, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );这段建表语句里status字段不传值时会自动填0create_time不传值时会自动填当前时间。这带来的好处是应用层只需要关注业务字段状态、时间这种基础设施字段的初始化让数据库自己搞定。这里有一个我踩过的大坑DEFAULT子句后面能不能用函数要看你用的什么版本。MySQL 8.0.13之前DEFAULT只能接常量8.0.13开始支持把表达式作为默认值。我在老项目里看到有人写DEFAULT (UUID())那是因为在MySQL 5.7.17以后支持的括号写法但不是每个版本都能直接跑。最简单的判断标准就是在建表后执行一次INSERT测试看看能否成功。另外默认值约束里还有一个常见误会它并不会自动填充NULL。你显式指定插入NULL时约束不会拦你除非这个字段同时设置了NOT NULL。想明白这一点很重要MySQL的默认值只在“没给这个列传值”的时候生效而不是“传了NULL的时候帮你补一个默认值”。3.3 唯一约束UNIQUE防重名、防重复提交的终极武器唯一约束确保一张表中的某个字段或某几个字段的组合不能出现重复值。CREATE TABLE user ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );也可以把多个字段组合成一个联合唯一约束CREATE TABLE user_tag ( user_id INT NOT NULL, tag_id INT NOT NULL, UNIQUE KEY uk_user_tag (user_id, tag_id) );这里要强调一个反直觉的点唯一约束允许出现多个NULL值。如果你在email字段上建立了唯一约束两个用户注册时不填邮箱插入两行空的email不会触发唯一冲突。原因很简单MySQL认为NULL和NULL是不相等的NULL不是值是“未知”两个未知没法判断是否相同。这个特性有好有坏。好处是逻辑上说得通坏处是你想用唯一约束保证“email不能重复”时空值会让你的防重失效。解决方案很简单结合NOT NULL一起用——如果email不允许为空那加上NOT NULL后再加唯一约束才能真正保证“非空且唯一”双重保险。还有一个细节唯一约束在MySQL里是通过唯一索引实现的。所以你执行SHOW INDEX FROM user;能看到唯一约束对应的索引记录。这也意味着每个唯一约束都有索引开销别滥用。3.4 主键约束PRIMARY KEY一张表只能有一个但它最值得你花心思主键约束从性质上说是非空唯一的组合但它又有自己独特的地位一张表只能有一个主键而且InnoDB默认会拿主键来组织数据存储。InnoDB是索引组织表IOT数据行本身就是以主键为Key的B树叶子节点。主键的形态直接影响数据文件的物理组织方式。如果你建表时没显式指定主键InnoDB会自己偷偷选一个“隐式主键”来组织数据。这个隐式主键你看不到也没法控制顺序会产生不必要的空间浪费。所以每张表必须有主键这不是规范问题是InnoDB存储引擎的结构要求。主键的选择上我的建议是三个备选方案自增整数主键适用绝大多数场景写入性能最优。UUID或雪花ID主键适合分布式系统、不需要数据库生成ID的场景。但要注意UUID是随机字符串作为主键时B树插入会产生大量页分裂写性能比自增整数差很多。业务键主键比如仅有一张“系统配置表”每条记录天然有唯一标识比如配置名。这种表可以用业务字段做主键但别用在会频繁修改业务键值的场景。自增整数主键是最省心的方案但有一个隐患别忘了自增ID在MySQL重启后可能回退特别是老版本5.7之前的场景如果你在业务里把ID拿来当订单号用会出大事。自增ID只应该当主键用不要去兼当业务编号。3.5 检查约束CHECKMySQL 8.0.16之后才是真正能用的检查约束是很多教材里都会写但在MySQL里属于“身世曲折”的一个约束。在MySQL 8.0.15及之前CHECK约束的语法虽然能解析但完全不强制。什么意思你写了CHECK (age 0)插一条age-5的数据照样能进去MySQL完全不鸟你。这个历史坑导致很多老程序员根本不在MySQL里用CHECK约束全部用触发器或应用层判断替代。到了MySQL 8.0.16官方才真正实现了CHECK约束的强制检查。所以如果你用的是MySQL 8.0.16或更高版本可以放心使用CREATE TABLE student ( id INT PRIMARY KEY, age INT, sex CHAR(1), CONSTRAINT chk_student_age CHECK (age BETWEEN 0 AND 150), CONSTRAINT chk_student_sex CHECK (sex IN (M, F)) );如果你还在用5.7或者8.0.16之前的老版本CHECK约束就形同虚设。这时候要么靠应用层要么用触发器要么就用ENUM枚举来约束取值范围。所以先查一下你的版本再决定要不要依赖CHECK。另外即使MySQL 8.0.16支持CHECK了也别把太复杂的校验逻辑塞进去。比如“性别只能是M或F”、“年龄在0到150之间”这种简单范围判断很适合CHECK。但如果你的规则里动不动就要查别的表、做子查询、算函数那还是老实用应用层写吧CHECK不适合干这种重活。3.6 外键约束FOREIGN KEY保证表间引用的完整性但用之前要想清楚外键约束让表之间产生关联保证子表里的引用一定存在于父表中CREATE TABLE order_detail ( id INT PRIMARY KEY, order_id INT NOT NULL, product_name VARCHAR(100) NOT NULL, CONSTRAINT fk_order_detail_order FOREIGN KEY (order_id) REFERENCES order_info(id) ON DELETE CASCADE ON UPDATE CASCADE );外键约束在InnoDB引擎下会自动在子表的外键列上建立索引这个索引的主要作用是加速外键检查时的查询。所以如果你在MySQL中用了外键不需要手动再给外键字段加索引加了也是多余。这里有两件事要说清楚第一ON DELETE和ON UPDATE的连锁策略。CASCADE父表删除时子表跟着删更新时跟着更新。适合“订单删了订单详情也删”的场景。SET NULL父表删除时子表的外键列置为NULL。前提是外键列允许NULL。RESTRICT/NO ACTION父表有子记录引用时禁止删除或更新。选策略时要想清楚业务逻辑。我有一次把订单详情的外键设置为ON DELETE CASCADE后来做数据清理时误删了一个订单结果这个订单下所有售后单都被级联删了差点出生产事故。ON DELETE CASCADE必须慎用上线前一定要想清楚级联范围。第二外键到底该不该用业界一直有争论。高性能架构这边很多团队刻意不用外键理由是外键检查有额外开销高并发写入时会影响性能数据迁移、分库分表时外键会带来一堆麻烦多数据源或微服务架构里表根本不在同一个库里外键也没法跨库生效。但如果你做的是中小型项目、内部管理系统或者对数据一致性要求很高那我建议该用外键就用。数据库外键把“不能引用不存在的用户”这种规则固化在底层比你写一百行应用层校验靠谱得多。至少我在做管理后台这种读写不高、一致性要求高的系统时从来不会刻意回避外键。4. 完整实操从零开始设计一张带全套约束的用户订单表前面把每种约束单独讲了一遍这一节我们实战演练。假设我们要设计一个电商系统的核心表用户表、订单表、订单明细表。重点演示约束的完整建表写法以及各种情况下的约束效果表现。4.1 建表前的设计准备先列业务规则再写SQL我建表有个习惯先在纸上把业务规则写清楚再翻译成建表语句。不要上来就写SQL很容易漏约束。用户表的业务规则先列一下用户ID必须唯一作为主键手机号必填且不能重复注册时校验过邮箱允许不填但填了就不能和别人重复昵称必填允许重复用户状态默认是1正常订单表的规则订单号唯一订单必须属于一个存在的用户订单金额必须大于0订单状态默认0待支付下单时间由数据库自动填订单明细表的规则明细ID主键明细必须属于订单商品名称必填数量必须大于0订单删除时明细跟着删4.2 建表语句与约束设计逐行解读把上面的规则落到MySQL 8.0建表语句里-- 用户表 CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, nickname VARCHAR(50) NOT NULL COMMENT 昵称, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;-- 订单表 CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(64) NOT NULL COMMENT 订单号, user_id INT UNSIGNED 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 下单时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT chk_order_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;-- 订单明细表 CREATE TABLE order_detail ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 明细ID, order_id BIGINT UNSIGNED NOT NULL COMMENT 所属订单ID, product_name VARCHAR(200) NOT NULL COMMENT 商品名称, quantity INT UNSIGNED NOT NULL COMMENT 数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_order_id (order_id), CONSTRAINT fk_detail_order FOREIGN KEY (order_id) REFERENCES order_info (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT chk_detail_quantity CHECK (quantity 0), CONSTRAINT chk_detail_price CHECK (price 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这三个表把前面讲的六种约束基本都覆盖了有几个设计点单独说下主键类型的选择。用户表用INT UNSIGNED自增订单表和明细表用BIGINT UNSIGNED自增。为什么订单表用BIGINT因为订单量通常比用户量增长快INT最大约21亿看似够用但已经有不少公司踩过“订单表主键逼近INT上限”的坑。与其到时候改表不如一开始就上BIGINT成本几乎为零。外键的删除策略。订单表对用户表的外键ON DELETE用了RESTRICT意思是一个用户有订单时不允许直接删除用户。这符合业务逻辑下单了的人不能说删就删。订单明细对订单的ON DELETE用了CASCADE订单删了明细跟着删这符合“订单不存在了明细没意义”的业务逻辑。DECIMAL别用FLOAT。金额字段必须用DECIMAL(10,2)这个和约束无关但是一个非常重要的小经验。用FLOAT存金额0.1 0.2会算出0.30000000000000004这种值做金额计算迟早翻车。4.3 约束生效演示插非法数据试试看表建好后来点实际的验证。下面这些SQL都会报错报错信息会告诉你到底是哪条约束把数据拦下了。先往用户表插一条正常数据INSERT INTO user (phone, email, nickname) VALUES (13800138000, testexample.com, 张三); -- 执行成功接着插一条手机号重复的INSERT INTO user (phone, email, nickname) VALUES (13800138000, otherexample.com, 李四); -- ERROR 1062 (23000): Duplicate entry 13800138000 for key user.uk_phone报错里清楚写出了冲突的索引名uk_phone。这个报错信息的格式你最好熟悉下排查问题的时候第一步就是看是哪个索引/约束报的错。再试试邮箱不填的情况插两行NULL邮箱的用户INSERT INTO user (phone, nickname) VALUES (13800138001, 王五); INSERT INTO user (phone, nickname) VALUES (13800138002, 赵六); -- 都能成功看吧唯一约束uk_email没有拦住NULL。这就是前面讲过的NULL之间不算重复。现在给订单表插一条引用不存在用户的订单INSERT INTO order_info (order_no, user_id, total_amount) VALUES (202501010001, 9999, 100.00); -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails外键约束生效因为用户表里没有ID9999的用户。再试试违反金额检查约束INSERT INTO order_info (order_no, user_id, total_amount) VALUES (202501010002, 1, -50.00); -- ERROR 3819 (HY000): Check constraint chk_order_amount is violated.MySQL 8.0.16以后CHECK约束的报错长这样清晰得让人感动。你要是用5.7这条-50的订单就真的进去了这也是为什么我一直强调版本的重要性。最后试一下外键的级联删除-- 先给用户1创建一个订单和明细 INSERT INTO order_info (order_no, user_id, total_amount) VALUES (202501010003, 1, 200.00); INSERT INTO order_detail (order_id, product_name, quantity, price) VALUES (LAST_INSERT_ID(), 手机壳, 2, 99.90); -- 删除这个订单 DELETE FROM order_info WHERE id LAST_INSERT_ID(); -- 明细被级联删掉了 SELECT * FROM order_detail WHERE order_id LAST_INSERT_ID(); -- 查询结果为空CASCADE生效了明细自动跟着订单走了。5. 常见问题与排查技巧实录这一段我从实际工作里挑出几个高频踩坑场景都是真实案例整理成速查表供你参考。5.1 问题速查表报错信息与解决方案对照报错信息触发器约束解决方案ERROR 1062 (23000): Duplicate entry xxx for key uk_xxx唯一约束或主键冲突先查已存在的值判断是业务重复还是历史数据问题再决定是修改数据还是删除约束ERROR 1048 (23000): Column xxx cannot be null非空约束检查插入语句中该字段是否没传值或传了NULLERROR 1364 (HY000): Field xxx doesnt have a default value非空字段未传值且无默认值要么插入时提供值要么给字段加DEFAULTERROR 1452 (23000): Cannot add or update a child row外键约束检查子表中外键字段的值是否存在于父表ERROR 1451 (23000): Cannot delete or update a parent row外键的RESTRICT策略先处理子表引用数据再操作父表ERROR 3819 (HY000): Check constraint chk_xxx is violated检查约束校验字段值是否满足CHECK条件这些报错提醒收藏一下出问题时按图索骥能省不少排查时间。5.2 坑一本来是唯一约束却放进来一堆NULL和空字符串我在一个用户系统里见过这么一张表email字段建了唯一约束但字段本身允许NULL。结果运营从Excel批量导入用户数据时一堆用户没填邮箱全部存成NULL。数据倒是进来了但后面要做一个“给没填邮箱的用户发提醒”的功能写SQL时发现NULL判断怎么写都别扭还得用COALESCE函数。更严重的是有些导入工具会把Excel空单元格转成空字符串而不是NULL。空字符串和NULL不一样空字符串是“值”多个空字符串会被唯一约束拦截掉导致批量导入失败一半。处理办法就一句话实在无法保证一个字段总有值就给它设计一个业务上有意义的默认值而不是用NULL表示“没有”。比如邮箱没填业务上可以让它存一个“未填写”的占位字符串再加唯一约束就不会出幺蛾子了。5.3 坑二大表加CHECK约束的版本兼容性这是我帮朋友排查过的一个生产事故。他们的项目跑在MySQL 5.7上有人为了提高数据准确性在ALTER TABLE语句里加了一个CHECK约束。执行成功大家很开心以为数据从此有了保障。结果第二天发现插入的负数金额照样进去了。原因就是前面反复强调的MySQL 5.7根本不强制CHECK约束你写了它也不管你。所以排查问题的时候一定先确认版本SELECT VERSION();8.0.16之前别依赖CHECK约束8.0.16开始可以正常使用。但要注意即使8.0.16以后CHECK里的表达式也有限制比如不能引用其他表的字段、不能用存储函数等。真遇到复杂校验还是用触发器或者应用层判断更稳。5.4 坑三外键导致的分库分表和迁移噩梦外键在高并发、分库分表场景下非常麻烦。分库分表后A表的数据在库1B表的数据在库2MySQL的跨库外键根本没法用。这也是为什么很多大厂规范明确规定“线上业务表不要使用外键”。我自己在一次工单系统的拆分改造中体会过这种痛。原来一张订单表和用户表有外键关联拆分后用户表被挪到另一个库订单表里的user_id就失去了外键约束。数据一致性只能靠应用层补偿。好在拆分前我们做了数据完整性检查没有产生孤儿数据。我的经验是做项目架构时提前想清楚未来会不会分库分表。如果是简单的单库项目、内部系统外键随便用方便省心。如果项目规划体量很大一开始就别建外键用应用层保证一致性省的以后拆库时把建表语句改个底朝天。5.5 坑四修改约束的正确姿势很多人以为约束建好了就不能动了实际上MySQL是完全支持ALTER TABLE修改约束的。这里列出几个常用语法-- 添加唯一约束 ALTER TABLE user ADD UNIQUE KEY uk_phone (phone); -- 删除唯一约束 ALTER TABLE user DROP INDEX uk_phone; -- 添加检查约束 ALTER TABLE order_info ADD CONSTRAINT chk_amount CHECK (total_amount 0); -- 删除检查约束 ALTER TABLE order_info DROP CHECK chk_amount; -- 删除主键约束慎用 ALTER TABLE user DROP PRIMARY KEY; -- 修改默认值 ALTER TABLE user ALTER COLUMN status SET DEFAULT 1;有几个坑提醒一下第一给已有大量数据的表加约束前先检查数据是否符合约束条件。比如你给phone字段加唯一约束结果表里已经有两行重复的手机号ALTER table直接失败。先查重再添加。SELECT phone, COUNT(*) FROM user GROUP BY phone HAVING COUNT(*) 1;第二删主键前先看看有没有外键引用它。有外键引用时你要先把外键约束也一起清理掉否则会报错。第三约束命名要规范。我习惯用“约束类型_表名_字段名”的格式uk_user_phone、fk_order_user、chk_order_amount。这样报错信息一眼就知道是哪里出了问题。没名字或者名字随意的约束出问题时看报错根本不知道是谁。6. 外键到底用不用我的决策方法论关于外键前面讲了一些利弊这里系统地说说我的决策思路算是一套个人化的判断框架。以下情况我推荐用外键项目体量不大单库部署并发写入量不高。数据一致性要求极高比如财务系统、订单系统、工单系统。团队开发水平参差不齐用数据库硬约束可以挡住粗心的代码。数据经常被外部工具直接操作比如DBA直连修数据、数据迁移导入这时外键能防止“改完父表忘了子表”的人为失误。以下情况我建议不用外键系统需要高频写入外键检查的每一次插入/更新都要去父表做存在性检查这个额外查询在极高并发下会被放大。规划中要做分库分表跨库外键根本不可用。表结构经常变更比如频繁添加字段、重命名表外键会拖累DDL操作。在不用外键的情况下业务完整性怎么保证我的做法是应用层在事务里先查父表记录是否存在再由代码逻辑保证数据一致。虽然多写几行代码但换来了高并发下的写入自由度。外键用不用没有标准答案。选型时最怕的是不清楚自己的场景盲目模仿大厂。大厂说“不用外键”是因为他的量级和架构决定了外键不可用你一个日活几千的管理系统跟着不用外键只会白白损失一层数据保障。反过来说你要是做一个高并发的秒杀系统还坚持每个表都挂外键那可能连压测都过不了。场景不同结论就不同。7. 索引和约束的关系很多人分不清的隐性知识点最后补一个非常容易混淆的知识点约束和索引到底什么关系一句话总结主键约束、唯一约束在MySQL中都是通过索引来实现的。你建了一个主键就建了一个主键索引聚簇索引你建了一个唯一约束就建了一个唯一索引。外键约束则会自动在子表外键列上建一个普通索引。所以你会发现SHOW INDEX FROM 你的表;查出来的索引里有些并不是你自己建的而是约束“附赠”的。清晰理解这种关系有助于你判断当表已经在某些字段上有唯一约束时不需要再为它单独建立一个普通索引因为唯一索引本身就能加速该字段的等值查询。非空约束、默认值约束和检查约束则不走索引它们纯粹是逻辑层面的检查不生成任何物理结构。这也能解释一个经验法则一张表上没必要看到字段就加索引很多字段的索引需求其实是约束已经帮你覆盖了的。仔细梳理一遍现有表的约束和索引能帮你砍掉不少冗余索引写入性能立马能感觉到改善。我个人在实际操作中的体会是约束和索引是MySQL里最容易“相爱相杀”的一对概念。真正理解了“约束决定索引、索引服务约束”的关系你看到一张表的SHOW CREATE TABLE输出就能大概推测出它的查询热点和写入代价。最后再分享一个小技巧拿到任何一张别人写的表第一件事就是执行SHOW CREATE TABLE 表名;看它的约束设计。从约束设计的严谨程度基本能判断出写这个表的人对业务的理解深度和工程素养。数据库约束设计得好后面所有写代码的人都会省心设计得烂各种数据异常就会层出不穷。表结构是编码的灵魂约束是表结构的骨架。骨架正了上面的业务逻辑才立得住。