上周帮某项目的同事评审建库脚本我翻到订单表那段时愣了几秒一张十来列的表一个约束都没写。没有主键没有唯一约束状态字段连默认值都没有。问了一句为什么对方说“约束条件不是可选的么先建表后面有空再加”。这个“后面有空再加”后来在测试环境里替他演示了一遍后果一天之后表里出现了两条一模一样的订单号状态字段被写进去一个不存在的数字删了一个客户之后订单表里剩下的全是孤儿单。那个下午全在写数据修复脚本比当初建表多花了几十倍时间。今天想聊的就是MySQL创建数据表及其约束这件事。标题里的“可选”两个字我特别想掰开揉碎讲清楚约束规则在语法上确实可以省略但省略只是你把业务规则从数据库的责任列表里划掉了它不会凭空消失只会变成跨过应用层合规检查的脏数据概率。这篇文章会从约束设计的思路、引擎与字段类型、主键唯一外键检查约束的细节一直讲到实际报错的排查链路内容偏实战适合正在设计表结构、或者已经被线上脏数据教育过的开发者慢慢看。1. 建表之前先回答三个问题约束自然会浮出来1.1 把约束当作业务规则的合同而不是可有可无的备注很多新手把约束理解成“建表语法的附加题”能写就写不能写就拉倒。但约束的本质是业务规则在数据库层的固化它跟你写入接口里的校验参数是同一件事只是落点不同。我习惯把约束理解成数据库和业务规则签的合同。接口里的校验是口头约定今天A同事记得明天B同事离职了后天新来的C同事可能就漏了数据库表上的约束是白纸黑字谁都改不掉。任何入口写入的数据不管是正常业务接口、临时脚本、手工SQL还是半夜跑的数据修复任务都要过这一关。所以建表之前先问自己三个问题第一这张表里哪些数据不允许重复第二这张表和其他表是什么关系孤立数据能不能存在第三哪些字段的值域是可枚举的哪些字段不允许为空哪些字段要有合理的默认值这三个问题想清楚了约束清单基本就成型了根本不需要背语法。1.2 从一个简化版的订单模块看约束清单为了让后面讨论不至于悬空我用一个简化版的订单模块贯穿全文涉及三张表客户表、订单表、订单明细表。这三张表的约束需求非常典型几乎覆盖了MySQL里所有常用约束类型。业务规则对应的约束落点每个客户编号全局唯一UNIQUE KEYcustomer.customer_no客户可以不填手机号但手机号不能重复UNIQUE KEY 允许NULLcustomer.phone订单必须归属一个真实存在的客户FOREIGN KEYorders.customer_id 引用 customer.id订单号全局唯一UNIQUE KEYorders.order_no订单总额不能为负数CHECK约束orders.total_amount订单状态只能是固定几种CHECK约束orders.status明细行数量必须大于0CHECK约束order_items.quantity删除订单时明细行不应该残留外键级联order_items.order_id 引用 orders.id看到没有约束不是从语法里反推出来的是从业务规则里正推出来的。先有规则后有约束。后面所有的章节都是在讲这些约束落到CREATE TABLE语句时有哪些细节和坑。2. 引擎、字符集、字段类型三个在CREATE TABLE里一错就埋雷的选型约束写对之前得先把表的基础配置写对。引擎、字符集、字段类型这三个选型看似基础实际上项目里大量“建表带病上线”的案例都栽在这里。2.1 引擎绝大部分场景选InnoDB外键约束也只有在InnoDB上才可能成立MySQL里常见的存储引擎是InnoDB和MyISAM。InnoDB支持事务、支持行级锁、支持崩溃恢复最重要的是外键约束只有InnoDB才支持。MyISAM连事务都没有外键写了也会被忽略加上表级锁在并发写入时表现很差除了极少数只读历史表、日志表我已经很多年没有在生产环境建过MyISAM表了。MySQL 8.0里默认引擎就是InnoDB如果建表时不写ENGINEInnoDB默认也是它。但我还是建议在CREATE TABLE语句里显式写出来一方面是清晰表达设计意图另一方面是防止未来有人把参数调到别的引擎去。外键、事务、并发写入这些需求都默认InnoDB基本不用犹豫。2.2 字符集别让唯一约束在大小写上“开小差”字符集这个问题很多团队直到线上出现乱码才想起来检查。字符集不统一导致的乱码只是表象更隐蔽的是它对约束判断的影响。MySQL里utf8字符集最多只有3个字节存不了emoji这类4字节字符而utf8mb4才是完整的UTF-8实现。现在新建表我基本无脑选utf8mb4。字符集后面带排序规则这个容易被忽略。排序规则决定字符怎么比较也就决定了唯一约束怎么判断“重复”。utf8mb4_general_ci和utf8mb4_0900_ai_ci都是大小写不敏感的也就是说在这种排序规则下davy和Davy会被唯一索引判定为同一个值。如果业务上希望邮箱、用户名不区分大小写这个行为正好合适但如果你的业务要求严格区分大小写就必须用utf8mb4_bin。我见过一个真实案例用户表用户名加了唯一约束用了大小写不敏感的排序规则结果用户注册时输入了不同大小写的用户名系统提示已存在用户还以为是撞号了。最后排查下来不是bug是排序规则定的。所以建表时字符集和排序规则要一起想清楚库、表、列三级尽量统一不要建完表之后临时改。2.3 字段类型选错的三类典型问题字段类型比约束更容易“看着对实际错”。第一类是主键用字符串比如直接拿手机号或身份证号当主键。字符串主键占用空间大插入乱序导致聚簇索引页分裂查询性能和价值密度都不如整型。第二类是用FLOAT或DOUBLE存金额。浮点数在二进制里无法精确表示累计几笔订单后再比较就会冒出0.1这样的幽灵误差。订单金额用DECIMAL(10,2)精确到分满打满算能支撑千万级别的单笔订单不够就放大DECIMAL(12,2)或更高精度。第三类是状态字段的长度乱拍明明只有三五个状态却开VARCHAR(255)浪费空间不说CHECK约束或者应用层枚举都难维护状态这种值域固定的字段用TINYINT配合CHECK语义最清晰。类型选好之后约束才有讨论的意义。字段类型本身就是一种软约束INT决定了它装不下小数VARCHAR(20)决定了它装不下无限长的文本。这个层次想清楚了后面写约束会顺手很多。3. 主键和唯一约束数据的“身份底线”到底该怎么定3.1 自增主键、UUID、业务主键没有银弹只有取舍主键是表的身份底线InnoDB里主键还直接决定聚簇索引的物理排列。很多刚入门的开发者喜欢问“主键到底用自增还是UUID”我的回答是看你的部署规模和未来迁移方式。单体项目、数据量可控、不需要跨库合并自增整型主键就是最优解。它有序、紧凑、索引友好写起来也直观。缺点是合并数据库时会撞ID比如A库和B库各自从1开始自增合并之后主键冲突得重新分配。所以需要频繁合并多库的分布式场景一般会用雪花ID或UUID其中雪花ID是整型且趋势递增比字符串UUID更适合做索引如果用UUID建议用BINARY(16)存而不是VARCHAR(36)能省不少空间。业务主键这条路我基本不推荐。把手机号、身份证、订单号当主键表面上省了一列实际上业务规则一变就得改表结构牵一发动全身。核心表的核心原则是主键越无关业务越好让它是纯粹的标识符。业务唯一性交给唯一约束去管而不是把主键本身变成业务字段。3.2 唯一约束与NULL多个“没有手机号”的客户为什么可以共存唯一约束和主键最大的区别是唯一约束列允许NULL主键列不允许。但这里有个特别容易踩的坑MySQL里同一列的唯一索引允许存在多个NULL值。回到客户表phone字段我设计成VARCHAR(20)允许NULL同时加UNIQUE KEY。这时候往表里插三条客户记录phone全部填NULL三条都能插进去。MySQL的逻辑是NULL代表未知每个NULL都是不同的未知值所以不冲突。这个行为要结合业务看待。如果你的需求是“客户可以不填手机号但填了的不能重复”那这个设计完美。但如果你另有需求未填手机号的记录全世界只能保留一条那唯一约束就兜不住了需要应用层在写入时先把空值统一成一个固定值或者用生成列把NULL映射成某个唯一的占位符。类似这种“多个NULL怎么办”的语义设计阶段就要定清楚不然上线后要写一堆补救SQL。4. 外键约束它保护的不是一条数据而是一整片数据关系4.1 用订单和明细两张表说明外键到底在拦什么外键的职责是维护两张表之间引用的完整性。还是用订单模块举例orders表的customer_id引用customer表的id这条外键约束一建立数据库就开始管两件事。第一件往orders表插入一条customer_id999的订单如果customer表里没有id999的客户这条插入直接失败报ERROR 1452。这就在数据库层面堵住了“订单找不到客户”的孤儿单。第二件你想删掉customer表里一个客户如果orders表还有他的订单默认情况下删除会失败因为外键默认的约束规则是RESTRICT不允许直接删有子记录的父亲。我见过太多没加外键的表应用层校验漏了一行删除接口直接删客户订单表瞬间全是孤儿单业务跑起来之后查不到归属修复成本极高。加了外键之后至少这类低级事故在数据库层面就拦住了。4.2 级联策略与生产库里的“去外键派”外键还能定义父表数据变化时子表怎么跟着变。订单明细表order_items里order_id引用orders.id加一句ON DELETE CASCADE删除订单时它的明细自动跟着删。这样以来应用层就少写很多“删完主表再删子表”的流水线代码也不容易出现只删了一半的中间状态。但CASCADE不是万能的。生产环境里大表级联删除的杀伤力非常大一条DELETE语句可能瞬间删掉几十万条明细锁范围拉满直接拖垮在线业务。所以很多团队会选择逻辑删除而不是物理删除给订单表加一个is_deleted字段删除只是改状态明细永远还在自然也就不需要级联删除。这种做法规避了外键的一部分性能问题。关于外键的争议生产环境里确实有一批“去外键派”理由不外乎几条外键写入时要比对父表性能有损耗分库分表之后外键没法跨库建立微服务各自持有独立库业务上不再把数据一致性托付给数据库。这些理由都成立但我想补充一句外键的缺席不代表约束消失只是位置变了——变成应用层事务、幂等校验、对账任务。如果你在单体项目里连外键都不敢用纯粹因为听说“大厂不用”那就有点因噎废食了。对绝大多数中小规模业务来说外键是性价比非常高的一致性防线。5. CHECK约束与非空、默认值数据库的“边角料规则”值得用好5.1 CHECK约束8.0.16是一个分水岭CHECK约束在MySQL里的命运有点曲折。MySQL 8.0.16之前CREATE TABLE里的CHECK子句会被解析但会被静默忽略表照样能建约束不会真正执行。这意味着你在5.7版本里写了CHECK (status IN (1,2,3))数据照样能把4写进去而且没有任何报错。这个“假约束”最坑人你以为有保护实际裸奔。从8.0.16开始CHECK约束才在存储引擎层真正生效。MySQL 8.0.19之后还支持ENFORCED和NOT ENFORCED关键字可以显式开启或忽略某个CHECK约束方便临时降级检查。用一个例子看CHECK的实际写法CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, customer_id INT UNSIGNED NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(id), CONSTRAINT chk_total_amount CHECK (total_amount 0), CONSTRAINT chk_status CHECK (status IN (1,2,3)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;需要注意CHECK约束只能引用当前表的当前行不能引用其他表的字段也不能包含子查询。它适合表达“这个字段必须在某个范围内”这种简单规则一旦涉及跨表判断就要改用触发器或者应用层逻辑。如果你还在用5.7版本又想保证状态字段合法只能退而求其次用ENUM或者写触发器心里得有数。5.2 默认值与“当前时间戳”的配合非空约束和默认值通常成对出现。一个字段如果业务上必须要有值就加NOT NULL如果通常有默认值就写清楚DEFAULT别让数据库在“用户没传时”一脸茫然。时间字段是默认值的重灾区。我强烈建议建表时统一用数据库的当前时间戳来做created_at和updated_at而不是依赖应用层传入。看上面orders表的写法created_at的默认值是CURRENT_TIMESTAMPupdated_at额外加ON UPDATE CURRENT_TIMESTAMP。这表示插入时自动写入当前时间行数据后续发生UPDATE时updated_at自动刷新。这样应用层少传两个字段还能保证所有入口写入的时间口径一致。DATETIME和TIMESTAMP之间也值得多提一句TIMESTAMP范围到2038年同时跟随会话时区DATETIME范围更大不随时区漂移。新项目里我更倾向用DATETIME配合应用层统一管理时区展示至少不会冒出“2038年问题”。NULL和空字符串的区别也要说清楚NULL表示“没有这个值”空字符串表示“知道这个值内容是空的”如果一张表里一会儿存NULL一会儿存查询条件会被迫写成一大串业务语义也模糊。设计字段时选定一种语义全表统一贯彻。6. 约束、索引与性能你加的每条规则数据库都在用索引记账6.1 约束底层是怎么实现的网上经常有人问“为什么主键和唯一约束查询那么快”答案是它们底层就是索引。约束不是数据库里另存的一张规则表而是基于索引结构的校验机制。InnoDB里PRIMARY KEY直接生成聚簇索引表数据本身就是按主键顺序物理组织的。UNIQUE约束会自动创建一个唯一二级索引插入数据时数据库利用索引快速判断“这个值是不是已经存在”。外键做的事情更多一些父表被引用列必须是一个索引通常是主键子表的外键列如果本身没有索引InnoDB会自动帮你建一个非唯一索引否则外键删改时要全表扫描判断引用关系代价太高。理解这一层之后再看“加约束会不会拖慢性能”就有谱了拖慢是必然的但慢在“多了一次索引查找和校验”不是慢到不可用。约束的本质是用插入和更新时的查询成本换来数据的确定性。问题不在于要不要这个成本而在于你换来的确定性是否值得。6.2 约束多的表写放大在哪里每张表每加一个唯一约束插入或更新时就要多执行一次唯一性判断每加一个外键子表写入时要对父表做一次存在性检查父表对应行可能被加锁每加一个CHECK约束每一行写入都要过一遍表达式虽然表达式本身很轻但量大之后都会累积。外键的级联删除是最典型的写放大场景。ON DELETE CASCADE删除一条父记录数据库会顺着引用关系把子表所有相关记录删掉如果子表还有孙子表一路级联下去一次删除可能变成几十万次删除。生产大表上这么玩DBA看了血压都要上来。这也是我前面提到的“约束要精准”的原因约束不是越多越好更不该为了存在感堆规则每个约束都得对应一条明确的业务规则。还有一个工程上的常见操作批量导数据时临时SET foreign_key_checks0关闭外键检查导完再开回来。这个操作本身没问题但要清楚它的代价——关闭期间写入的不合法数据不会被自动拦截重新开启外键检查时数据库不会把存量数据重新校验一遍。也就是说关掉外键导入导进去的脏数据会安静地躺在表里直到某次联表查询露出马脚。导完数据一定要主动做一次业务层面的完整性校验别以为开关一开就万事大吉。7. 一个建表报错的完整排查链路ERROR 12157.1 复现过程和第一个判断外键约束相关的报错里ERROR 1215“Cannot add foreign key constraint”是出现频率最高的。我拿一个很常见的真实场景复现一下。客户表的id是INT UNSIGNED自增主键CREATE TABLE customer ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, customer_no VARCHAR(32) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;订单表的customer_id我一开始写成了普通INT有符号的CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, ... PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;然后执行ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(id);结果直接报ERROR 1215。这个报错经常把人卡住因为从语法看两个字段确实都是INT为什么加不上外键这里就要引出排查的第一步把两个表的完整定义拉出来对比重点不是看列名而是看字段的完整属性。一字不差地看有没有UNSIGNED、字符集是什么、排序规则是什么、两表引擎是不是一致。很多时候问题不是“类型长得不一样”而是“类型长得几乎一样就差一个属性”。7.2 逐项排除引擎、类型、符号、字符集与索引ERROR 1215涉及外键约束但触发原因不止一个我按排查顺序整理了一张表。检查点要求报错结果两表存储引擎都必须是InnoDB外键不支持MyISAM1215字段类型子表和父表被引用列类型必须一致1215有无符号两个INT必须同为有符号或同为无符号1215字符集与排序规则字符类型字段必须完全一致1215父表列是否索引父表被引用列必须是一个索引1822约束命名冲突同库内约束名不能重复相关约束已存在报错回到刚才的报错两边都是INT但customer.id是INT UNSIGNEDorders.customer_id是INTUNSIGNED属性不一致所以报1215。改成INT UNSIGNED之后外键就加上了。这个坑特别隐蔽因为SELECT出来看数据无差别一旦字段值超过有符号INT上限子表根本存不下父表却有值引用完整性无从谈起所以InnoDB干脆不允许这种字段建立外键。关于父表列索引的问题也经常有人踩。如果父表被引用列不是索引报错通常不是1215而是ERROR 1822提示缺少索引。InnoDB要求父表被引用列必须显式存在索引主键自然满足但如果你引用了一个普通字段记得先给那个字段建索引。7.3 写数据时违反外键的ERROR 1452是同一个问题的另一面建表阶段的外键问题排查完运行阶段还有一个高频报错ERROR 1452“Cannot add or update a child row: a foreign key constraint fails”。这个报错的意思是你往子表插入了一条记录但引用的父表id不存在。遇到1452先别急着怀疑外键写错按两个方向查第一父表里到底有没有这个id如果父表数据是分批导入的可能是导数据顺序错了先导了子表后导父表第二子表里插入的那一行外键列的值是不是被应用层填错了比如把0当成了默认值来传。0这个值尤其恶心它不一定存在父表里却常常被各种语言里“int默认值0”的习惯带进来。临时处理已经出现问题的场景可以SET foreign_key_checks0导数据但这只是“绕过去”不是“修好了”。只要外键检查重新开启之后的新写入依然严格存量脏数据也不会自动被清理。我个人的习惯是建表阶段多花十分钟检查类型、符号、字符集比上线后拿半小时对着一堆1452和1215发愁要划算得多。外键这类依赖元数据的报错排查时最有用的工具就是SHOW CREATE TABLE把两张表的完整定义并列摆在屏幕上逐行对照。你多对照几次就会发现绝大多数约束问题是设计初期的一个小忽略而这个忽略恰好会在三个月后的某个深夜变成报警短信。建表这件事慢即是快规则先立后面才省心。