在数据库设计的生命周期中建立表结构只是第一步如何保证存入数据的准确性和正确性才是核心痛点。如果没有适当的限制数据库很容易充斥着各种“脏数据”和冗余数据。为了解决这个问题MySQL引入了完整性约束Constraints的概念。本文将带大家系统盘点MySQL中的五大核心完整性约束结合具体场景让你在建表时游刃有余。一、 什么是完整性约束简单来说约束就是为了确保表数据的完整性为表添加的一些限制规则。它是数据库表中设计的一个最基本规则。使用约束可以使数据更加准确从而大大减少冗余的“脏数据”。数据库完整性约束主要分为以下五大类主键约束 (Primary Key)唯一约束 (Unique)非空约束 (Not Null)默认约束 (Default)外键约束 (Foreign Key)二、 主键约束 (Primary Key) 与 自动递增1. 主键约束的核心概念理论上来说每一个数据表都必须有一个唯一的主键作为数据的唯一标识。特性设置为主键的列不允许为空 (Not Null)且在添加数据时不能重复既唯一性。习惯主键习惯用id表示。可以在创建数据表时直接指定也可以通过修改表结构追加。2. 自动递增 (AUTO_INCREMENT)主键一般不是数据表当中的应用列不具备具体业务含义而是额外增加的列。如果每次插入数据都需要手动维护这个ID不仅麻烦而且容易出错例如报Duplicate entry 1 for key PRIMARY错误。INSERT INTO student(id,name,age,email) VALUES (1,张安,18,1443005893qq.com) INSERT INTO student(id,name,age,email) VALUES (1,李四,20,1443005893qq.com)因此MySQL针对主键定制了一种自动增长的策略。让MySQL自己去维护这个主键方法是每次找到数据表中最大的ID值并加1。自动递增的5大特点类型限制只有整型数据列才能设置为自动递增。键的限制只有主键才能设置为自增列。赋值自由自动增长的列在新增数据时可以不赋值数据库会自动填补。初始与步长初始化默认值为1默认增量也为1。消耗机制自增列一旦被使用过即便该条数据被删除该数字也不会再次出现ID不会回退。三、 唯一约束 (Unique)唯一约束用来保护表中某列的数据不允许重复。与主键的区别它与主键约束类似但级别没有主键高。一份表中主键只能有一个但唯一约束可以创建多个。并且唯一约束的列是允许为空 (Null)的。应用场景一般用于约束具有唯一性质的业务信息例如手机号、账户名称、邮箱等。INSERT INTO student(name,age,email) VALUES (张安,18,1443005893qq.com) INSERT INTO student(name,age,email) VALUES (lisi,20,1443005893qq.com)当我们做第二次插入的时候我们会发现如下错误⚠️ 避坑指南在某些MySQL版本或配置下注意varchar长度设置。如果varchar长度设置为255时可能会因为索引长度超限而无法设置唯一约束。四、 非空约束 (Not Null) 与 默认约束 (Default)1. 非空约束 (Not Null)可以通过NOT NULL设置数据表中某一列是必填字段。一旦设置在插入数据时该字段既不允许为空必须提供具体的值。2. 默认约束 (Default)可以通过DEFAULT设置默认约束。设置了默认约束的列如果在插入数据时不给它传值系统就会自动使用设置的默认值来填充。例如可以将“性别”字段的默认值设置为“保密”或者将“状态”字段默认设置为“0”。五、 外键约束 (Foreign Key)外键用来让两个表的数据之间建立连接以此来保证数据的一致性和完整性。例如用student表的c_id字段和class表的id字段进行链接。1. 外键的删除和修改行为当父表被引用的表发生变更时子表包含外键的表该如何反应MySQL提供了几种策略CASCADE (级联)在父表上 update/delete 记录时同步 update/delete 子表的匹配记录。这是一种“同生共死”的策略。SET NULL在父表上 update/delete 记录时将子表上匹配记录的列设为 null前提是子表的外键列不能设置 not null。NO ACTION / RESTRICT如果子表中有匹配的记录则不允许对父表对应候选键进行 update/delete 操作直接拒绝动作。2. 外键设置的注意点添加外键的数据库存储引擎必须为InnoDB。从表中的关联字段数据必须在主表中存在。主表中的关联字段通常要求是主键或具有唯一索引。六、 架构师思考为什么现在很多公司不建议设置物理外键虽然外键从理论上看非常完美但在实际的互联网开发中很多架构师和DBA往往完全放弃使用物理外键转而用代码逻辑来保证数据一致性。主要原因在于外键对性能具有较大的影响性能损耗在表上拥有活动的外键虽然提高了数据质量但在每一次执行插入、更新和删除操作之前数据库都需要去检查是否违反了数据完整性。这种跨表的检查在高并发场景下会导致严重的性能瓶颈。数据仓库不适用数据仓库和分析型数据库尤其如此。这些库不以交易方式一次一行处理数据而是批量处理数据。对于它们来说性能就是一切外键检查会拖垮整个ETL流程。总结 掌握MySQL的五大约束是进行高质量数据库设计的基础。在实际业务中主键、非空、默认、唯一几乎是建表标配而对于外键则需要结合系统的并发量和性能要求权衡到底是使用数据库层面的物理外键还是在业务代码层面实现逻辑外键。