前言在MySQL中数据类型是对字段存储格式的第一层约束但仅靠数据类型无法保障数据的业务合法性与完整性。比如用户邮箱必须唯一、用户姓名不能为空、订单所属用户必须在用户表中存在……这些业务层面的规则都需要通过表的约束来实现。表约束是MySQL在数据写入层面的最后一道校验关卡能够从数据库层面强制保证数据的正确性、一致性和完整性。本文将系统讲解MySQL中所有常用约束的语法、原理、实战场景与常见坑点帮助你从“会写”到“懂原理”设计出健壮的数据库表结构。一、约束的概念与分类1.1 什么是约束约束是作用于表中字段上的规则用于限制存储在表中的数据类型、取值范围、唯一性等特性。如果插入/修改的数据违反了约束规则操作会被直接拒绝从而保证数据库中数据的正确性和可靠性。1.2 常用约束类型总览我们可以把MySQL常用约束分为几大类约束类型关键字核心作用非空约束NOT NULL限制该字段值不能为NULL默认值约束DEFAULT字段未赋值时自动使用默认值注释约束COMMENT给字段添加业务描述便于维护零填充约束ZEROFILL数值显示时自动补前导零仅影响显示格式主键约束PRIMARY KEY唯一标识一行数据非空且唯一一张表只能有一个自增长约束AUTO_INCREMENT数值字段自动递增常配合主键使用唯一约束UNIQUE KEY保证字段值在表中唯一允许为NULL外键约束FOREIGN KEY建立两表关联保证数据引用的一致性二、非空约束NOT NULL2.1 基本概念非空约束用于限制字段的值不能为NULL。MySQL默认所有字段都允许为NULL即默认是NULL属性但在实际开发中绝大多数业务字段都应该设置为NOT NULL。2.2 为什么要尽量避免NULL很多新手觉得NULL和空值没区别这是最大的误区。NULL在MySQL中是一个特殊值会带来很多问题运算结果不可控任何数值与NULL进行运算结果都是NULLSELECT1NULL;-- 结果为NULLSELECTCONCAT(hello,NULL);-- 结果为NULL统计结果偏差COUNT(字段名)会自动忽略值为NULL的行导致统计数据不准-- 若name字段有一行NULLSELECTCOUNT(*)FROMuser;-- 统计总行数包含NULL行SELECTCOUNT(name)FROMuser;-- 统计name非空的行数不包含NULL行查询逻辑失效不能用 NULL判断空值必须用IS NULL/IS NOT NULL NULL永远返回false2.3 语法与实战示例创建表时直接在字段后加NOT NULL即可-- 创建班级表班级名和教室都不允许为空CREATETABLEmy_class(class_nameVARCHAR(20)NOTNULLCOMMENT班级名称,class_roomVARCHAR(10)NOTNULLCOMMENT教室编号)ENGINEInnoDBDEFAULTCHARSETutf8mb4;如果插入时不给非空字段赋值会直接报错INSERTINTOmy_class(class_name)VALUES(计算机1班);-- 报错ERROR 1364 (HY000): Field class_room doesnt have a default value2.4 易错点提醒❌ 误区空字符串等于NULL✅ 事实空字符串是一个长度为0的有效字符串不属于NULLNOT NULL约束允许插入空字符串。❌ 误区所有字段都没必要加NOT NULL✅ 事实除了明确允许为空的业务字段比如用户的可选备注信息其余字段都建议设置NOT NULL避免NULL带来的各种异常。三、默认值约束DEFAULT3.1 基本概念当插入数据时如果没有给该字段赋值就会自动使用DEFAULT指定的默认值。默认值约束可以大幅减少插入语句的冗余同时配合NOT NULL保证字段非空。3.2 语法与实战示例CREATETABLEuser_info(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT用户ID,nameVARCHAR(20)NOTNULLCOMMENT用户名,ageTINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT年龄默认0,sexCHAR(2)NOTNULLDEFAULT男COMMENT性别默认男,create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间默认当前时间)ENGINEInnoDBDEFAULTCHARSETutf8mb4;插入时省略带默认值的字段自动填充默认值-- 只插入name字段age、sex、create_time自动使用默认值INSERTINTOuser_info(name)VALUES(张三);-- 查询验证SELECT*FROMuser_info;3.3 重点补充常用默认值场景数值型字段默认0比如状态、数量、年龄字符串字段默认空字符串避免NULL时间字段DEFAULT CURRENT_TIMESTAMP记录创建时间ON UPDATE CURRENT_TIMESTAMP自动记录更新时间开发高频使用update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间3.4 易错点提醒❌ 误区设置了DEFAULT的字段就不用加NOT NULL了✅ 事实DEFAULT只在“插入时不赋值”时生效如果显式插入NULL依然会写入NULL。因此规范写法是NOT NULL DEFAULT xxx既保证默认值又禁止显式插入NULL。❌ 误区默认值只能是常量✅ 事实时间类型可以使用CURRENT_TIMESTAMP等函数但普通字段不能直接用函数生成默认值。四、列描述注释COMMENT4.1 基本概念COMMENT是字段的注释说明不会影响数据存储和约束逻辑专门用来描述字段的业务含义方便后续开发和DBA维护表结构。企业级开发规范要求所有表和所有字段都必须加COMMENT。4.2 查看注释的两种方式查看建表语句最常用SHOWCREATETABLEuser_info\G执行后会显示完整的建表SQL包含所有字段的COMMENT。从系统表查询适合批量获取SELECTCOLUMN_NAME字段名,COLUMN_COMMENT字段注释FROMinformation_schema.COLUMNSWHERETABLE_SCHEMA你的数据库名ANDTABLE_NAMEuser_info;4.3 易错点提醒DESC 表名命令无法查看字段注释只能通过上面两种方式查看。注释不属于强制约束但属于开发规范是表可维护性的关键。五、零填充约束ZEROFILL5.1 基本概念ZEROFILL是数值型字段的显示格式约束当数值的位数小于字段定义的显示宽度时自动在左侧补0。注意ZEROFILL只影响查询显示的格式不影响实际存储的值数据库内部存储的依然是原始数值。5.2 核心原理int(M)的真正含义很多新手会疑惑int(5)和int(11)有什么区别是不是存储范围不一样答案是没有ZEROFILL时括号里的数字没有任何意义int类型的存储范围永远是4字节-2147483648~2147483647。括号里的M叫做“显示宽度”只有配合ZEROFILL时才会生效用来指定补零后的总位数。5.3 实战示例CREATETABLEtest_zerofill(aINT(5)ZEROFILLCOMMENT5位宽度零填充,bINT(10)COMMENT无零填充)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 插入数据INSERTINTOtest_zerofillVALUES(1,2);-- 查询查看效果SELECT*FROMtest_zerofill;查询结果ab000012我们可以用HEX函数验证存储的真实值SELECTa,HEX(a)FROMtest_zerofill;结果显示HEX(a)为1证明内部存储的就是数值100001只是显示格式化的效果。5.4 易错点提醒ZEROFILL会自动添加UNSIGNED属性因为负数没有前导零的意义所以设置ZEROFILL后字段自动变为无符号整型。仅适用于数值型字段实际开发中使用场景很少多用于固定长度的编号比如订单号、学号。六、主键约束PRIMARY KEY6.1 核心概念主键是表中一行数据的唯一标识具备两个核心特性非空主键字段的值不能为NULL唯一主键字段的值不能重复一张表最多只能有一个主键可以是多个字段组成的复合主键。从索引角度看主键对应的是聚簇索引数据在磁盘上就是按照主键顺序存储的因此主键查询效率是最高的。6.2 单字段主键创建表时直接在字段后加PRIMARY KEY即可CREATETABLEstudent(idINTUNSIGNEDPRIMARYKEYCOMMENT学号主键,nameVARCHAR(20)NOTNULLCOMMENT姓名)ENGINEInnoDBDEFAULTCHARSETutf8mb4;插入重复主键会直接报错INSERTINTOstudentVALUES(1,张三);INSERTINTOstudentVALUES(1,李四);-- 报错ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY6.3 复合主键由多个字段共同组成一个主键多个字段组合起来不能重复单个字段可以重复。多用于多对多中间表、联合主键场景。语法在所有字段定义完成后用PRIMARY KEY(字段1, 字段2)指定-- 学生成绩表学号课程号作为复合主键CREATETABLEstudent_score(student_idINTUNSIGNEDNOTNULLCOMMENT学号,course_idCHAR(10)NOTNULLCOMMENT课程号,scoreTINYINTUNSIGNEDDEFAULT60COMMENT成绩,PRIMARYKEY(student_id,course_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;复合主键的规则两个字段的值完全相同时才算冲突单个字段重复是允许的。INSERTINTOstudent_scoreVALUES(1,C001,90);INSERTINTOstudent_scoreVALUES(1,C002,85);-- 正常学号重复课程号不同INSERTINTOstudent_scoreVALUES(2,C001,95);-- 正常课程号重复学号不同INSERTINTOstudent_scoreVALUES(1,C001,88);-- 报错组合重复6.4 主键的增删改表创建完成后也可以通过ALTER语句追加或删除主键追加主键-- 给无主键的表添加主键ALTERTABLEstudentADDPRIMARYKEY(id);删除主键ALTERTABLEstudentDROPPRIMARYKEY;6.5 主键设计最佳实践企业级开发中主键设计遵循一个核心原则使用与业务无关的代理主键不使用业务字段作为主键比如不要用身份证号、手机号、工号作为主键因为业务字段可能会随着业务调整而变化主键修改的成本极高。推荐使用自增INT、BIGINT或者雪花ID作为主键和业务完全解耦。6.6 易错点提醒❌ 误区一张表可以有多个主键✅ 事实一张表只能有一个主键但可以是多字段组成的复合主键。❌ 误区删除主键直接DROP就行✅ 事实如果主键字段有AUTO_INCREMENT属性必须先去掉自增长属性才能删除主键否则会报错。七、自增长约束AUTO_INCREMENT7.1 基本概念自增长约束作用于数值型字段当插入数据不给该字段赋值时系统会自动从当前最大值1生成一个新值。通常和主键搭配使用作为逻辑主键。7.2 基本语法CREATETABLEuser(idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENTCOMMENT用户ID自增主键,usernameVARCHAR(20)NOTNULLCOMMENT用户名)ENGINEInnoDBDEFAULTCHARSETutf8mb4;插入数据时自增字段可以赋值为NULL或者省略自动递增INSERTINTOuser(username)VALUES(user1);INSERTINTOuser(username)VALUES(user2);SELECT*FROMuser;-- id自动变成1、27.3 获取最后插入的自增ID插入数据后可以通过LAST_INSERT_ID()函数获取本次连接最后插入的自增IDSELECTLAST_INSERT_ID();注意该函数是连接级别的不同连接之间互不影响批量插入时返回的是第一条数据的自增ID。7.4 深度解析自增长的特性与坑点自增长的前提条件字段必须是索引主键或唯一键字段必须是整数类型TINYINT、INT、BIGINT等一张表最多只能有一个自增长字段自增长不连续的场景高频面试题自增长值一旦被消耗就不会回退以下场景会出现ID不连续插入数据失败/唯一键冲突自增值已经被消耗事务回滚自增值不会回滚批量插入数据时会预分配自增值多余的会被丢弃数据库重启InnoDB的自增值在内存中重启后会重新计算自定义初始值和步长可以通过修改变量调整自增长的起始值和步长多用于主主架构-- 设置自增步长为2SETauto_increment_increment2;-- 设置自增起始偏移量为1SETauto_increment_offset1;7.5 易错点提醒❌ 误区自增长一定是从1开始连续递增✅ 事实自增长只能保证递增不能保证连续不要依赖自增ID的连续性做业务逻辑。❌ 误区自增长字段不能手动赋值✅ 事实可以手动赋值只要不重复就行后续自增会从手动插入的最大值开始1。八、唯一键约束UNIQUE KEY8.1 基本概念唯一键约束保证字段的值在表中不能重复解决了“一张表只能有一个主键但有多个字段需要保证唯一性”的问题。比如用户表中手机号、邮箱、身份证号都需要唯一这时候就可以用唯一键。8.2 基本语法CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT主键ID,usernameVARCHAR(20)NOTNULLCOMMENT用户名,emailVARCHAR(64)UNIQUECOMMENT邮箱唯一,phoneCHAR(11)UNIQUEKEYCOMMENT手机号唯一)ENGINEInnoDBDEFAULTCHARSETutf8mb4;插入重复值会报错INSERTINTOuser(username,email)VALUES(张三,zhangsantest.com);INSERTINTOuser(username,email)VALUES(李四,zhangsantest.com);-- 报错ERROR 1062 (23000): Duplicate entry zhangsantest.com for key email8.3 联合唯一键和复合主键类似多个字段组合起来保证唯一-- 用户收藏表同一个用户不能重复收藏同一个商品CREATETABLEuser_collect(idINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,goods_idINTNOTNULL,UNIQUEKEYuk_user_goods(user_id,goods_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;8.4 核心特性NULL值的处理唯一键允许字段值为NULL并且多个NULL值不会冲突。因为在MySQL中NULL和任何值都不相等包括NULL本身所以多条数据的唯一键字段为NULL时不会触发唯一约束冲突。INSERTINTOuser(username,email)VALUES(王五,NULL);INSERTINTOuser(username,email)VALUES(赵六,NULL);-- 执行成功两个NULL不会冲突8.5 主键 vs 唯一键 核心区别对比维度主键 PRIMARY KEY唯一键 UNIQUE KEY数量限制一张表只能有一个一张表可以有多个NULL值不允许为NULL允许为NULL且多个NULL不冲突索引类型聚簇索引非聚簇唯一索引作用唯一标识一行数据保证业务字段不重复设计原则用无业务含义的代理主键作用于有唯一性要求的业务字段8.6 易错点提醒❌ 误区唯一键不能有NULL值✅ 事实唯一键允许多个NULL值这是和主键最核心的区别之一。❌ 误区UNIQUE和UNIQUE KEY不一样✅ 事实两者语法完全等价没有区别。九、外键约束FOREIGN KEY9.1 基本概念外键用于建立两张表之间的关联关系保证从表的外键字段值必须在主表的主键/唯一键中存在或者为NULL。从而保证数据引用的一致性避免脏数据。主表父表被引用的表必须有主键或唯一键从表子表设置外键的表外键字段引用主表的主键/唯一键9.2 基本语法语法FOREIGN KEY (从表字段) REFERENCES 主表(主表字段)示例班级表主表和学生表从表先创建主表班级表CREATETABLEclass(idINTPRIMARYKEYCOMMENT班级ID,class_nameVARCHAR(30)NOTNULLCOMMENT班级名称)ENGINEInnoDBDEFAULTCHARSETutf8mb4;再创建从表学生表设置外键关联班级表CREATETABLEstudent(idINTPRIMARYKEYCOMMENT学生ID,nameVARCHAR(30)NOTNULLCOMMENT学生姓名,class_idINTCOMMENT所属班级ID,-- 外键约束class_id 引用 class表的id字段FOREIGNKEY(class_id)REFERENCESclass(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;9.3 约束效果验证正常插入先插入主表数据再插入从表数据INSERTINTOclassVALUES(10,Java班),(20,C班);INSERTINTOstudentVALUES(1001,张三,10),(1002,李四,20);插入不存在的班级ID直接报错INSERTINTOstudentVALUES(1003,王五,30);-- 报错ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails外键字段可以为NULL未分配班级的学生INSERTINTOstudentVALUES(1003,王五,NULL);-- 执行成功9.4 补充外键的级联操作基础外键默认是限制模式主表的记录被从表引用时无法直接删除/修改主表记录。通过设置级联行为可以定义主表数据变更时从表的处理逻辑级联行为说明ON DELETE CASCADE删除主表记录时自动删除从表中关联的记录ON UPDATE CASCADE更新主表主键时自动更新从表中关联的外键值ON DELETE SET NULL删除主表记录时将从表关联的外键值设为NULLON DELETE RESTRICT默认值限制删除有从表关联时禁止删除主表记录示例带级联删除和级联更新的外键CREATETABLEstudent(idINTPRIMARYKEYCOMMENT学生ID,nameVARCHAR(30)NOTNULLCOMMENT学生姓名,class_idINTCOMMENT所属班级ID,FOREIGNKEY(class_id)REFERENCESclass(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4;9.5 深度讨论物理外键 vs 逻辑外键虽然外键能保证数据一致性但在互联网大厂的开发规范中普遍不推荐使用物理外键数据库层面的FOREIGN KEY而是使用逻辑外键应用层维护关联关系原因如下性能影响每次写入数据都要校验外键高并发下性能差影响数据库扩展性维护困难分库分表场景下外键完全无法使用耦合度高表之间强耦合后续业务调整、数据迁移成本高级联风险级联删除可能导致误删大量数据引发生产事故建议小项目、内部系统可以用物理外键保证数据安全高并发、大型系统推荐使用逻辑外键在应用层保证数据一致性。9.6 易错点提醒存储引擎限制只有InnoDB引擎支持外键MyISAM引擎不支持外键约束即使写了FOREIGN KEY语法也不会生效。主表字段要求主表被引用的字段必须是主键或者唯一键否则无法创建外键。字段类型一致从表外键字段和主表被引用字段的类型、长度、字符集必须完全一致否则创建失败。十、综合实战电商商城表设计我们通过一个经典的电商场景综合运用所有约束设计三张表商品表、用户表、订单表。需求说明商品表商品ID主键自增商品名称非空单价非空默认0分类、供应商非空用户表用户ID主键自增用户名非空邮箱唯一手机号唯一性别非空订单表订单ID主键自增用户ID关联用户表商品ID关联商品表购买数量默认1完整建表SQL-- 创建数据库CREATEDATABASEIFNOTEXISTSshop_mallDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;USEshop_mall;-- 1. 商品表CREATETABLEIFNOTEXISTSgoods(goods_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT商品ID主键,goods_nameVARCHAR(64)NOTNULLCOMMENT商品名称,unit_priceINTNOTNULLDEFAULT0COMMENT单价单位分,categoryVARCHAR(32)NOTNULLCOMMENT商品分类,providerVARCHAR(64)NOTNULLCOMMENT供应商名称,create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间)ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT商品表;-- 2. 用户表CREATETABLEIFNOTEXISTScustomer(customer_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT用户ID主键,usernameVARCHAR(32)NOTNULLCOMMENT用户名,emailVARCHAR(64)UNIQUECOMMENT邮箱唯一,phoneCHAR(11)NOTNULLUNIQUECOMMENT手机号唯一非空,sexENUM(男,女,保密)NOTNULLDEFAULT保密COMMENT性别,id_cardCHAR(18)UNIQUECOMMENT身份证号唯一,addressVARCHAR(256)DEFAULTCOMMENT收货地址,register_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT注册时间)ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT客户表;-- 3. 订单表CREATETABLEIFNOTEXISTSpurchase(order_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT订单ID主键,customer_idINTNOTNULLCOMMENT下单用户ID,goods_idINTNOTNULLCOMMENT购买商品ID,numsINTNOTNULLDEFAULT1COMMENT购买数量,order_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间,-- 逻辑外键不创建物理外键约束应用层维护INDEXidx_customer_id(customer_id),INDEXidx_goods_id(goods_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT购买订单表;设计说明所有表和字段都加了COMMENT符合企业开发规范核心业务字段都设置了NOT NULLDEFAULT避免NULL值带来的异常邮箱、手机号、身份证号用唯一键保证业务唯一性订单表没有创建物理外键而是加了普通索引提升查询效率符合互联网开发规范时间字段都设置了默认当前时间减少插入代码冗余十一、常见易错点汇总NULL相关坑NULL参与运算结果为NULLcount(字段)忽略NULL不能用NULL判断int(M)误解M是显示宽度不影响存储范围只有配合ZEROFILL才有用ZEROFILL隐式转换设置ZEROFILL自动变为无符号整型主键数量限制一张表只能一个主键可以是复合主键自增长不连续插入失败、事务回滚都会导致自增ID空洞不要依赖连续性唯一键NULL特性唯一键允许多个NULL不会冲突外键引擎限制只有InnoDB支持外键MyISAM不生效外键字段类型主从表关联字段类型必须完全一致十二、总结MySQL的表约束是数据完整性的基石不同约束有不同的适用场景基础约束NOT NULL、DEFAULT、COMMENT属于开发规范建议所有表都合理使用主键自增长是绝大多数表的标准配置优先使用代理主键唯一键用于业务字段的唯一性校验注意NULL值特性外键根据项目场景选择小型系统可用物理外键高并发系统推荐逻辑外键合理设计表约束能够在数据库层面提前拦截非法数据大幅降低业务代码的校验成本同时提升数据的一致性和可维护性。如果觉得本文对你有帮助欢迎点赞、收藏、评论交流