1. 建表之前得先想清楚“约束”到底是干嘛的很多人刚开始接触 MySQL 的时候都觉得建表就是写一条 CREATE TABLE 语句把字段名和类型列出来就完事了。约束条件嘛听上去像是个“可选”的附加项有空就加没空就拉倒。我当年也是这么想的直到被线上数据的脏数据坑过几次之后才彻底改了这个观念。如果你也被“约束条件可选”这几个字迷惑过那这篇文章就是写给你的。先说说约束到底是什么。简单讲约束就是数据库自己给自己定的一套“规矩”用来保证写进去的数据是合法、合规、符合业务预期的。比如一个人的年龄不能是负数订单金额不能为 NULL用户邮箱不能重复这些听起来像是业务逻辑但如果只在应用层做判断那总有漏网之鱼——可能是某个接口忘了校验可能是有人直接连数据库改了数据也可能是历史代码埋的雷。而把约束建在数据库层面就等于给数据上了最后一道保险。那为什么标题里要加“可选”呢我的理解是MySQL 里的约束确实不是每个字段都必须要加的有些表结构很灵活业务规则也不严格不加约束完全能用。但“可选”不等于“可以随便省”而是说你需要根据业务场景去判断哪些约束该加、哪些不用加、哪些加了反而碍事。这篇文章我会把常用的几类约束挨个拆开讲清楚再结合实际的建表案例告诉你每一步为什么要这么写。不管你是刚接触数据库的学生还是写后端写了几年但一直没系统梳理过约束的开发者这篇文章都值得花十分钟看完。我不会堆一堆抽象的概念而是直接用建表语句来讲每一行代码对应什么效果、什么场景下要用、什么场景下千万别用都会说到。2. 约束的五种类型先分清谁管谁在动手写 CREATE TABLE 之前得先把 MySQL 里的约束类型捋一遍。很多人搞混是因为这些约束在语法上长得有点像功能上又互相交叉不仔细分的话容易张冠李戴。先记住一句话主键管“唯一且非空”唯一键管“唯一”非空约束管“不能为空”默认值管“不给值时有兜底”检查约束管“值必须满足条件”外键管“表和表之间的关系”。2.1 主键约束每张表的定海神针主键可以说是所有约束里最核心的一个。它的规则是值不能为 NULL并且不能重复。一张表最多只能有一个主键但主键可以由多个字段联合组成这叫复合主键。我在实际项目里见过不少设计失误的表最典型的是没有主键只建了一个普通索引。这种表在数据量小的时候看不出问题一旦数据量上来做数据同步、分页查询、去重操作的时候就会非常痛苦。因为没有主键就意味着没有唯一的记录标识你连“这条数据到底是哪条”都说不清楚。所以我的习惯是建表第一步先定主键。业务上如果有天然唯一的字段比如身份证号、订单号可以用它做主键如果没有就直接加一个自增的 id 字段省心又稳。2.2 唯一约束防止业务数据重复唯一约束和主键很像都要求值不能重复但区别在于唯一约束允许 NULL而且一张表可以有多个唯一约束。它很适合用来锁业务字段比如用户表的手机号、邮箱这类字段逻辑上不应该重复。这里有个细节很多人会踩坑MySQL 里唯一约束对 NULL 的处理是“多个 NULL 不算重复”。也就是说你可以往一个加了唯一约束的字段里插入十条 NULL它不会报错。因为 MySQL 的逻辑是“NULL 表示未知未知不等于未知”。这在某些业务场景下是好事比如用户没填手机号但在有些场景下会让人困惑你得心里有数。2.3 非空约束拒绝空数据的入场券非空约束是最简单直白的一种写法就是字段后面加 NOT NULL。它表达的意思是这条记录如果没给这个字段的值就不允许写进表里。什么时候该加非空我的判断标准很简单这个字段在业务逻辑上是否“必须有值”。比如订单的金额、用户的状态、文章的创建时间这类字段如果你允许为 NULL后面写查询语句的时候就麻烦了。因为 NULL 的运算逻辑和空字符串完全不同它既不等于 0也不等于 更不等于 NULL 本身判断起来特别绕。与其后面到处提心吊胆地处理 NULL不如建表的时候就把它堵死。2.4 默认值约束给字段一个兜底方案默认值是很多人容易忽略的一个约束它的作用是插入数据的时候如果某个字段没有显式赋值MySQL 会自动用默认值填充。这个约束做业务的时候特别实用。举个例子用户状态字段默认值是“正常”新用户注册的时候不用专门传这个值创建时间字段默认值设为 CURRENT_TIMESTAMP新记录插进去的时候自动带当前时间。这两个场景在几乎所有项目里都会出现用默认值可以少写不少冗余代码还能保证数据规范。2.5 检查约束MySQL 8.0.16 之后才真正可用的“守门员”检查约束的作用是限制字段值的取值范围比如年龄必须在 0 到 120 之间状态只能是“启用”或“禁用”。在过去很长一段时间里MySQL 虽然支持 CHECK 语法但它只是“假装支持”——你写了它也不检查形同虚设。直到 MySQL 8.0.16 版本开始检查约束才真正生效。如果你用的版本在 8.0.16 以下想限制取值范围的话只能用枚举类型 ENUM 或者靠应用层判断。到了 8.0.16 以上我建议能用 CHECK 就用 CHECK毕竟数据库自己管着比应用层可靠得多。这一点很多人不知道值得单独记一下。3. 手写一条建表语句把约束逐个加进去理解归理解真正动手写的时候还是会有很多细节问题。这一节我会用一张实际业务中非常常见的“用户表”来演示从零开始写一条完整的建表语句然后一步一步给它加上各种约束每一行都会解释清楚为什么这么写。先看一个最简版本CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT, username VARCHAR(50), email VARCHAR(100), age INT, status TINYINT );这条语句能跑通但问题很多。id 没指定是主键username 可以被重复插入email 也可以为空status 没人管它填了什么。这在实际生产中基本没法用。接下来我们一步步完善。3.1 加主键约束和自增属性CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100), age INT, status TINYINT );这里做了两件事一是把 id 指定为主键二是加上自增属性。自增的作用是每次插入新记录时id 自动加一不用手动传值。有人会问为什么主键要用自增 id而不是直接用 username 或者 email 当主键原因有两点。第一业务字段一旦设置为主键后续想改这个字段的值就会非常麻烦因为你要连带更新所有引用它的地方第二自增主键在 InnoDB 存储引擎下是顺序写入的性能更好页分裂的概率更低。所以除非有很强的业务理由否则默认自增 id 就对了。3.2 加唯一约束和联合唯一CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, age INT, status TINYINT );username 和 email 都加了 NOT NULL 和 UNIQUE。意思很清楚用户名和邮箱不能为空也不能重复。这符合用户体系的一般预期。这里我还要专门讲一个“联合唯一”的用法。有些业务里单个字段没有唯一性要求但两个字段合在一起必须唯一。比如“同一个班级里学号不能重复”但不同班级可以有相同学号这时候就需要联合唯一约束CREATE TABLE student_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, class_id BIGINT NOT NULL, student_no VARCHAR(20) NOT NULL, student_name VARCHAR(50), UNIQUE KEY uk_class_student (class_id, student_no) );联合唯一约束的底层实现是建了一个联合唯一索引索引里的字段值组起来不能重复。这在表示“多对多”关联关系的中间表里尤其常见比如好友关系表里(user_id, friend_id) 这个组合必须唯一防止重复加好友。这是个高频需求你得记住。3.3 加默认值和非空约束CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, age INT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 1, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );注意看年龄和状态这两个字段age 设了 NOT NULL 和默认值 0status 设了默认值 1。这样设计的意思是新用户注册的时候年龄可以先给 0后续再补状态默认是“启用”。create_time 这个字段值得单独说说。把默认值设为 CURRENT_TIMESTAMP是 MySQL 里记录创建时间的标准写法。它表示每次插入记录时如果没显式指定时间就自动取当前时间。有了这个约束你连代码里设置时间的步骤都省了而且数据库中记录的时间是数据库自己的时间更准确。3.4 加检查约束如果你用的 MySQL 是 8.0.16 以上版本可以试试加一个检查约束让表更“严谨”一点CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, age INT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 1, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_age CHECK (age 0 AND age 120), CONSTRAINT chk_status CHECK (status IN (0, 1)) );CHECK 约束的格式是 CONSTRAINT 约束名 CHECK (条件表达式)。约束名是自定义的建议起得见名知义不然数据库报错的时候你看到一堆 chk1、chk2 根本不知道在说谁。上面这段代码里age 被限制在 0 到 120 之间status 被限制只能是 0 或者 1。这样就算应用层忘了判断非法值数据库也会直接拒绝写入。不过我得提醒一句生产环境能不能用检查约束还得看你们团队是否统一使用新版 MySQL。如果你维护的是老项目MySQL 版本还在 5.7那这里写的 CHECK 约束只是摆设不会起任何作用。这种情况你就得靠应用层校验了。3.5 完整示例一张稍微正经点的用户表把上面这些组合起来一条相对完整的建表语句长这样CREATE TABLE user_info ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 主键ID, username VARCHAR(50) NOT NULL UNIQUE COMMENT 用户名, email VARCHAR(100) NOT NULL UNIQUE COMMENT 邮箱, age INT NOT NULL DEFAULT 0 COMMENT 年龄, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态0-禁用1-启用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, CONSTRAINT chk_age CHECK (age 0 AND age 120), CONSTRAINT chk_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表;这里我额外加了两个东西一是字段和表后面的 COMMENT 注释二是 update_time 字段的 ON UPDATE CURRENT_TIMESTAMP。注释很容易理解就是为了让别的同事看表结构时不至于猜谜而 ON UPDATE CURRENT_TIMESTAMP 的意思是每次更新这条记录时它会自动把时间刷新为当前时间记录“最后更新时间”不需要你手动维护。4. 建表实操中的硬核细节每一个都是坑上节给出了完整的用户表建表语句但实际操作里光会照抄远远不够。这一节我把实操中最容易出问题的几个点单独拎出来讲都是我踩过的坑或者帮别人排查过的问题。4.1 字符集和排序规则怎么选建表语句最后那行 ENGINEInnoDB DEFAULT CHARSETutf8mb4很多人会直接复制但可能不知道为什么要用 utf8mb4 而不是 utf8。一个关键的知识点MySQL 里的 utf8 字符集其实不是完整的 UTF-8它最多只支持三个字节的字符。四个字节的字符比如 emoji 表情、生僻字存进去就会报错或者变成乱码。而 utf8mb4 是真正的“四字节 UTF-8”能覆盖所有 Unicode 字符。所以现在主流做法一律是 utf8mb4别再用 utf8 了。排序规则建议用 utf8mb4_unicode_ci 或者 utf8mb4_general_ci。前者的校对逻辑更准确后者性能稍好一点点。绝大多数场景下我用 utf8mb4_unicode_ci不是因为那点性能差异而是它的排序和比较更符合直觉不容易出奇奇怪怪的问题。4.2 存储引擎选 InnoDB 还是 MyISAM这条其实已经快变成常识了能用 InnoDB 就用 InnoDB。InnoDB 支持事务、支持行级锁、支持外键崩溃恢复能力也比 MyISAM 强得多。MyISAM 的查询性能在某些场景下确实有点优势但它不支持事务没有行级锁一旦并发一上来全是坑。所以说建表的时候直接写 ENGINEInnoDB别给表留下一个隐性的性能炸弹。即使你的表今天只是单机小项目保不齐哪天数据量大了要搬上线到时候再迁移引擎可是个不小的工程。4.3 字段类型选择宁缺毋滥这是新手最爱犯的错误。见过有人把状态字段设计成 VARCHAR(255)里面就存一个 0 或者 1见过有人把所有数字字段全部用 BIGINT也不管实际范围。字段类型选大了浪费存储空间选小了上线之后发现存不下数据再改表又会锁表影响业务。我的建议是能用 INT 就别用 BIGINT能用 VARCHAR(32) 就别上 VARCHAR(255)能用 TINYINT 表达状态就别用 INT。MySQL 里的约束和类型是一个整体设计约束再严类型给得太大也是一种浪费。4.4 复合主键的适用场景和副作用前面我提过联合唯一这里要分清它和复合主键的区别。复合主键是把多个字段一起设为主键效果是所有字段的组合必须唯一并且任何一个字段都不能为 NULL。它的使用场景很少一般是业务表里没有独立 id只能靠多个字段才能定位一条记录。但复合主键有个明显的副作用它会让你后续的关联查询变复杂。因为你引用这张表的时候外键也得跟着用多个字段否则无法准确关联。所以能加自增单列主键的表就不要用复合主键。复合主键适合那种纯粹的关联表、日志表这些表本身没有独立的业务主键概念。4.5 外键约束要不要用什么时候别用外键是约束里最“有争议”的一个。它的作用是保证两张表之间的数据一致性——子表里的外键值必须存在于父表的主键或唯一键中否则不允许插入或更新。比如一个订单表里有一个 user_id 字段你希望它必须引用用户表的某个 id就可以加外键。加了之后除非你使用 CASCADE级联删除等规则否则不能删除一个被引用的用户。但在实际开发中很多团队反而会刻意避免使用外键。原因包括但不限于外键会影响写入性能因为每次插入都要去父表做一致性检查外键会让表之间耦合度高分库分表之后外键约束几乎没法维护还有不少业务场景要求“逻辑删除”而外键会限制你的一些操作。权衡下来我的个人习惯是如果项目是比较标准的单体应用数据量不大外键可以放心用如果项目从一开始就想好了要做微服务或者数据分片那外键还是尽量省掉把一致性校验放在应用层做反而更灵活。5. 实操中的常见问题和排查记录这个板块我整理了自己在项目里真实遇到过的几个问题每一个都带排查思路希望你能少走弯路。5.1 插入重复数据主键报错还是唯一索引报错有一次同事跑来问我“我往用户表里插入一条数据报错说什么 Duplicate entry但我不确定到底是主键冲突还是用户名冲突怎么快速判断”其实 MySQL 的错误信息里已经带了索引名或者约束名。比如 Duplicate entry 1 for key PRIMARY 就是主键冲突如果显示 for key uk_username那就说明用户名重复。看错误信息的时候别只盯着前半句后半句才是关键。不过如果你的约束没有命名MySQL 会默认给你生成一个名字比如 username 字段的唯一约束名字可能就叫 username。所以我在前面强调写约束的时候最好手动命名特别是唯一约束和检查约束。命名之后报错信息一目了然维护别人写的表也不至于一头雾水。5.2 NULL 和空字符串是两回事这是个经典的混淆点。有一次查数据发现一张表里很多行的字段值明明是空的但用 IS NULL 却查不出来。原因很简单那些“空值”是空字符串 不是 NULL。在 MySQL 里 是一个具体的值它不等于 NULL。NULL 表示“没有值” 表示“有一个值是空的”。这影响很大如果你对字段加了 NOT NULL 约束你可以往里存 但存不进去 NULL如果你用的是默认值 那查询的时候要注意用field 而不是field IS NULL。我建议你在设计表结构的时候就明确哪些字段允许 NULL哪些字段用空字符串加默认值代替。最省心的方案是业务字段能设 NOT NULL 就不留 NULL实在需要“暂未知”优先给一个明确的默认值比如 0、、0000-00-00 之类的。这样后续写 SQL 的时候少很多麻烦。5.3 约束对性能的影响被高估还是低估很多人一听说“约束”就觉得它会让数据库变慢。我的实践经验是约束带来的性能损耗远小于它带来的数据可靠性价值。唯一约束和主键约束本质上都是索引索引本身确实是额外存储和写入开销但这个开销对于绝大多数业务场景都是可以忽略的。真正要小心的是外键每次写入都会触发对父表的检查高并发场景下这个开销会被放大。所以我才说外键要不要加得看你项目的规模。如果你真的很在意写入性能又想要约束的可靠性可以做一个折中把唯一性检查放到应用层但数据库里加上唯一索引做兜底。这样一来正常情况下应用层已经拦截了重复数据数据库索引只在极端情况下“背锅”既能拦截问题也不会成为性能瓶颈。5.4 表建完了才发现约束加错了怎么改线上表跑了一段时间同事发现当初给某字段加了唯一约束但现在业务上允许重复了。这时候怎么改两条路。一种是直接删掉这个约束。如果要删的是唯一约束你需要先知道索引名ALTER TABLE user_info DROP INDEX uk_username;另一种是加约束。比如你要给某个字段加唯一约束ALTER TABLE user_info ADD UNIQUE KEY uk_email (email);如果表里已经存在重复数据加唯一约束的时候会直接报错。MySQL 不会允许你建一个“已经不符合规则”的约束。你需要先把重复数据清理掉再执行加约束语句。这里提醒一句在数据量大的表上执行 ALTER TABLE 加约束/删约束是有可能锁表的。MySQL 8.0 支持的在线 DDL 比老版本好了不少但高风险操作最好还是安排在业务低峰期并且提前做好备份。6. 聊点实用的一个“会过日子”的建表习惯最后这部分不写高深的东西说几个我自己平时建表时的习惯。不一定适用于所有团队但你要是不知道从何下手可以参考一下。6.1 先花三分钟把表和表的关系画出来我见过最乱的数据库就是那种一张表里什么字段都往里面塞没有清晰的设计全靠开发的时候临时加字段。建表之前花几分钟梳理一下实体关系哪怕只是在纸上画几个方框连几条线都能避免后面大量返工。所谓约束不只是单个表的字段规则更是整个表之间关系的表达。外键、唯一约束、冗余字段怎么取舍这一阶段就决定了。6.2 建表语句尽量写进版本管理用命令行临时建表可以但表结构最后一定要落到项目代码里用迁移脚本或者初始化 SQL 文件管理起来。这样做的原因很简单别人可以 review 你的表结构线上环境可以复现表结构出问题的时候可以对比“改了哪里”。数据库的结构和业务代码一样需要版本管理的约束。这句话可能有点偏题但确实是我无数次吃亏之后最想强调的。6.3 表建完之后第一时间用 SHOW CREATE TABLE 复盘我每写完一张表都会执行一遍SHOW CREATE TABLE user_info;这个命令会把表的完整定义打印出来包括所有约束、索引、字符集、注释。这么做有一个好处你可以站在“数据库视角”重新审视一遍自己的建表语句看看有没有遗漏有没有约束被静默忽略掉。特别是版本兼容性问题比如 CHECK 约束不生效SHOW CREATE TABLE 一眼就能看出来。6.4 终极建议约束是设计不是补丁把约束当“补丁”用的人数据库肯定越到后面越乱。约束应该是设计阶段就规划好的东西它是你对业务规则的郑重声明而不是上线后发现数据乱了再追着加的东西。提示任何约束的加入都要考虑历史数据的兼容性。如果你打算给一个跑了好几年的老表加约束先跑一遍全表数据检查如果有脏数据存在得先处理掉否则约束加不上还会让你误以为是语法写错了。我在实际项目中体会最深的一点是好的表结构一般在设计阶段就“长好了”而不是靠后面不停打补丁。所有约束的设计必须服务于数据的真实来源、真实用途和真实消费方式。如果你对自己要存什么数据还没想明白再多的约束也只是空架子。希望这篇文章能帮你把 MySQL 建表这关过得轻松一点。