前言刚接触 MySQL 的同学几乎都会有一个疑问字段明明已经指定了数据类型为什么还要额外使用约束举个最简单的例子我们把年龄字段设置为int整型它只能保证存入的数据是整数却无法限制数值不能为负数将邮箱设置为字符串类型也不能自动保证邮箱地址不重复。数据类型仅仅是对数据格式做基础校验而数据库约束则是站在业务逻辑层面进一步规范数据、保证数据的完整性、合法性和关联性。在实际项目开发中数据库约束是保障数据质量的第一道防线。如果缺少约束数据表中很容易出现空值、重复数据、无效关联数据等脏数据不仅会增加后端代码的校验压力还会导致统计、查询、业务逻辑出现各类异常。本文将从零开始详细讲解 MySQL 中所有常用表约束包括非空、默认值、字段注释、零填充、主键、自增、唯一键、外键搭配大量可直接运行的 SQL 案例、报错解析、使用场景和实战建表示例适合 MySQL 初学者系统学习也可作为开发日常查阅手册。一、数据类型与约束的核心区别在正式学习约束之前我们先理清两个核心概念的边界这也是理解约束的前提。数据类型只限制数据的存储格式。比如int只能存整数、varchar只能存字符串、date只能存日期只要格式符合要求无论内容是否符合业务规则都可以正常存入数据表。约束Constraint在数据类型的基础上限制数据的业务合法性。结合项目规则规定字段是否允许为空、是否允许重复、取值范围、表与表之间的关联关系等。结合业务场景举例说明age int仅约束年龄为整数无法阻止存入 -10、-50 这类负数学号字段字符串类型无法保证学号唯一容易出现重复学号学生所属班级仅存班级编号无法校验该班级是否真实存在于班级表中。而以上所有业务规则都需要依靠数据库约束来实现。简单总结数据类型管格式约束管规则二者搭配才能构建规范的数据表。MySQL 主流常用约束汇总不区分大小写约束名称核心作用NOT NULL限制字段不能为空DEFAULT为字段设置默认值未传参时自动填充COMMENT为字段 / 表添加备注说明方便维护ZEROFILL整型字段自动补零格式化显示PRIMARY KEY主键约束唯一标识单条数据AUTO_INCREMENT整型字段自增长常与主键搭配UNIQUE KEY唯一键约束保证字段值不重复FOREIGN KEY外键约束维护多表之间的关联关系接下来我们逐个拆解每一种约束的语法、特性、案例、报错原因以及开发使用规范。二、NULL 与 NOT NULL 非空约束2.1 理解 MySQL 中的 NULL很多初学者会把NULL、空字符串、数值0混为一谈这是典型的认知误区。在 MySQL 中NULL代表无值、未知值不等同于 0、空字符串、falseMySQL 中字段默认允许为 NULL也就是不添加任何约束时字段可以不传入数据。NULL最大的问题是无法参与正常数学运算、逻辑判断任何数值与NULL做运算结果都会直接变为NULL。执行测试 SQL-- 查看 NULL 本身 SELECT NULL; -- 数值与 NULL 相加运算 SELECT 1 NULL;运行结果可以清晰看到1 NULL的最终结果依旧是NULL。在实际业务中这种特性会引发一系列问题数据求和、统计平均值、分组统计等报表计算全部失效后端代码需要额外增加大量判空逻辑代码冗余且容易出现 Bug。因此行业通用规范设计数据表时尽量避免字段允许为 NULL。2.2 NOT NULL 非空约束用法NOT NULL约束的作用非常明确强制该字段必须传入有效值不允许为空。案例创建班级表业务规则班级名称、教室编号是必填项不允许为空。-- 创建班级表两个字段均添加非空约束 CREATE TABLE myclass( class_name VARCHAR(20) NOT NULL, class_room VARCHAR(10) NOT NULL );使用DESC查看表结构DESC myclass;结果中Null列显示为NO代表该字段受非空约束限制禁止存入空值。非法数据插入与报错解析如果插入数据时跳过被NOT NULL约束的字段-- 仅传入班级名称未传入教室编号 INSERT INTO myclass(class_name) VALUES(class1);执行后会抛出错误ERROR 1364 (HY000): Field class_room doesnt have a default value报错原因class_room被设置为NOT NULL既没有手动传入数值也没有设置默认值MySQL 无法填充数据直接拒绝插入。这也印证了非空约束的强制校验效果。三、DEFAULT 默认值约束3.1 约束作用部分字段存在固定默认取值比如人员性别默认男、账户状态默认正常、年龄默认 0。如果每次插入数据都重复填写固定值会增加冗余操作。DEFAULT约束就是为字段预设默认值当插入数据时未给该字段传值MySQL 会自动填充预设的默认内容。3.2 建表示例创建测试表姓名非空年龄默认 0性别默认 “男”CREATE TABLE tt10( name VARCHAR(20) NOT NULL COMMENT 姓名, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年龄, sex CHAR(2) DEFAULT 男 COMMENT 性别 );查看表结构DESC tt10;在Default列可以看到对应字段的默认值Null列显示YES代表这两个字段允许为空有默认值兜底。3.3 默认值生效测试插入数据时仅填写必填的姓名字段INSERT INTO tt10(name) VALUES(zhangsan); -- 查询全表数据 SELECT * FROM tt10;查询结果年龄自动填充为 0性别自动填充为 “男”默认值约束正常生效。3.4 使用注意事项只有添加DEFAULT约束的字段插入数据时才可以省略不传值若字段同时设置NOT NULL且无默认值必须手动传值否则插入失败默认值的数据类型需要和字段本身数据类型保持一致。四、COMMENT 字段注释约束4.1 约束作用COMMENT用于给字段、数据表添加中文描述注释它不会影响数据存储、查询、业务逻辑核心作用偏向维护和文档管理方便开发人员理解字段含义尤其是多人协作项目帮助 DBA数据库管理员维护、排查数据表问题可直接基于注释生成数据库设计文档。在复杂项目中一张表可能包含几十甚至上百个字段清晰的注释是规范数据库设计的基本要求。4.2 带注释的建表示例CREATE TABLE tt12( name VARCHAR(20) NOT NULL COMMENT 用户姓名, age TINYINT UNSIGNED DEFAULT 0 COMMENT 用户年龄, sex CHAR(2) DEFAULT 男 COMMENT 用户性别 );4.3 查看注释的两种方式DESC 命令只能查看字段类型、非空、主键等基础信息无法查看注释SHOW CREATE TABLE完整查看建表语句包含所有字段、表的注释是查看注释的标准命令。执行命令SHOW CREATE TABLE tt12\G终端会展示完整的建表语句每个字段后都能看到对应的COMMENT注释内容。五、ZEROFILL 零填充约束5.1 整型长度的误区初学者经常疑惑int(10)、int(5)中括号内的数字代表什么众所周知int类型固定占用 4 字节和括号内数字无关。核心结论在没有添加 ZEROFILL 零填充时整型后括号内的长度参数毫无意义仅为预留格式只有搭配ZEROFILL该长度才会生效。5.2 ZEROFILL 特性ZEROFILL是专门作用于整型字段的格式化约束按照设定的长度对数值左侧自动补 0仅改变展示效果数据库底层实际存储的原始数值不变一旦添加ZEROFILL字段会自动附带UNSIGNED无符号属性。5.3 完整实操案例步骤 1创建测试表并插入数据CREATE TABLE tt3 ( a INT(10) UNSIGNED, b INT(10) UNSIGNED ); INSERT INTO tt3 VALUES(1,2); SELECT * FROM tt3;此时两个字段均无零填充正常展示数值1和2。步骤 2为 a 字段添加 ZEROFILL 约束修改表结构将a字段长度改为 5并开启零填充ALTER TABLE tt3 CHANGE a a INT(5) UNSIGNED ZEROFILL; SHOW CREATE TABLE tt3\G查看建表语句可看到a字段已携带ZEROFILL属性。步骤 3查询验证展示效果SELECT * FROM tt3;结果中a字段的1被格式化为00001按照 5 位长度左侧补零而b字段保持原样。步骤 4验证底层存储不变使用HEX()函数查看数据底层存储SELECT a, HEX(a) FROM tt3;结果显示HEX(a)的值为1证明数据库内部依旧存储原始数字补零只是前端格式化展示不会改变真实数据。5.4 适用场景编号类字段流水号、工号、订单编号是ZEROFILL的主要使用场景比如要求编号固定 6 位不足位数补 0。六、PRIMARY KEY 主键约束6.1 主键的定义与核心特性主键PRIMARY KEY是数据表中唯一标识单条记录的字段相当于每条数据的 “身份证号”是数据库设计中最核心的约束之一。主键三大硬性规则值唯一整张表中主键值不能重复非空主键字段不允许为NULL数量限制一张表只能有一个主键。6.2 单列主键创建与测试1. 建表时指定主键CREATE TABLE tt13( id INT UNSIGNED PRIMARY KEY COMMENT 学号主键, name VARCHAR(20) NOT NULL COMMENT 姓名 );执行DESC tt13Key列显示PRI代表该字段为主键。2. 主键唯一性测试插入第一条数据INSERT INTO tt13 VALUES(1,aaa);再次插入相同主键值的数据INSERT INTO tt13 VALUES(1,bbb);系统抛出报错ERROR 1062: Duplicate entry 1 for key PRIMARY报错含义主键值1已存在违反唯一性约束插入失败。6.3 后期添加 / 删除主键如果建表时未设置主键可通过ALTER TABLE动态修改-- 给已有表添加主键 ALTER TABLE 表名 ADD PRIMARY KEY(字段名); -- 删除表的主键 ALTER TABLE 表名 DROP PRIMARY KEY;6.4 复合主键联合主键1. 概念当单个字段无法唯一标识数据时可以使用多个字段组合作为主键也就是复合主键。 规则多个字段组合的值整体唯一单独某个字段可以重复。典型场景学生选课表一条记录由「学生 ID 课程 ID」共同唯一确定。2. 复合主键建表示例CREATE TABLE tt14( id INT UNSIGNED COMMENT 学生ID, course CHAR(10) COMMENT 课程编号, score TINYINT UNSIGNED DEFAULT 60 COMMENT 考试分数, -- 多个字段联合作为主键 PRIMARY KEY(id, course) );3. 规则验证允许数据学生 ID1课程 C学生 ID1课程 Java单字段重复组合不重复禁止数据两条记录同时为 1 C组合值重复违反主键规则。复合主键在多对多关联表中使用非常广泛。七、AUTO_INCREMENT 自增长约束7.1 约束特性AUTO_INCREMENT是整型字段自增长约束搭配主键使用频率最高。规则插入数据时不给该字段传值MySQL 自动取当前字段最大值 1生成新值使用前提字段必须是索引主键、唯一键且数据类型为整型一张表最多只能有一个自增长字段。7.2 实战案例自增主键创建自增主键表CREATE TABLE tt21( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 自增主键ID, name VARCHAR(10) NOT NULL DEFAULT COMMENT 姓名 );插入数据时省略自增主键字段INSERT INTO tt21(name) VALUES(a); INSERT INTO tt21(name) VALUES(b); SELECT * FROM tt21;查询结果中id自动从 1 开始依次递增无需手动赋值。7.3 开发使用建议自增 ID 是目前互联网项目最主流的主键方案无业务含义、纯数字、递增有序后续业务调整不会受影响。八、UNIQUE KEY 唯一键约束8.1 为什么需要唯一键我们已知一张表只能有一个主键但业务中往往有多个字段需要保证唯一性手机号、身份证、邮箱、工号等。此时就需要使用UNIQUE KEY唯一键约束。8.2 唯一键核心特性字段值全局唯一不允许重复允许为 NULL且多个NULL互不冲突NULL 不参与唯一性比对一张表可以创建多个唯一键。8.3 案例与测试1. 创建带唯一键的数据表CREATE TABLE student( id CHAR(10) UNIQUE COMMENT 学号唯一键, name VARCHAR(10) COMMENT 姓名 );2. 唯一性测试插入重复数据会触发Duplicate entry重复报错和主键报错一致。3. NULL 兼容性测试INSERT INTO student VALUES(NULL,bbb);语句可以正常执行验证唯一键允许存入空值。九、主键 vs 唯一键 详细对比很多初学者容易混淆主键和唯一键这里做清晰对比并结合业务场景讲解选型规范对比维度主键PRIMARY KEY唯一键UNIQUE KEY唯一性必须唯一必须唯一空值禁止 NULL允许 NULL数量一张表仅能有 1 个一张表可存在多个核心用途标识单条数据逻辑主键保障业务字段唯一性开发最佳实践主键优先选用无业务含义的自增 ID自增数字和业务无关后续业务迭代、字段修改都不会影响主键业务唯一字段设置为唯一键身份证、手机号、邮箱等业务唯一字段使用UNIQUE KEY禁止使用身份证、手机号等业务字段作为主键一旦业务规则变更如手机号升级会引发全表数据重构。十、FOREIGN KEY 外键约束10.1 外键的作用前面所有约束都是单表约束而外键FOREIGN KEY是专门用于维护多表之间关联关系的约束也是保证参照完整性的核心。相关概念主表父表被引用的表字段必须是主键 / 唯一键从表子表引用主表字段的表被引用的字段就是外键。外键核心规则从表中的外键值必须在主表的对应字段中存在不允许引用不存在的数据。10.2 外键基础语法FOREIGN KEY(从表字段) REFERENCES 主表(主表主键字段)10.3 完整实操案例班级表 学生表步骤 1创建主表班级表-- 主表班级表 CREATE TABLE myclass( id INT PRIMARY KEY COMMENT 班级编号主键, name VARCHAR(30) NOT NULL COMMENT 班级名称 );步骤 2创建从表学生表并添加外键学生表的class_id引用班级表的主键id-- 从表学生表class_id 为外键 CREATE TABLE stu( id INT PRIMARY KEY COMMENT 学生编号, name VARCHAR(30) NOT NULL COMMENT 学生姓名, class_id INT COMMENT 所属班级编号, -- 建立外键关联 FOREIGN KEY(class_id) REFERENCES myclass(id) );步骤 3插入合法关联数据先给主表插入班级数据INSERT INTO myclass VALUES(10,C大牛班),(20,Java大神班);再给从表插入学生数据班级编号在主表中存在INSERT INTO stu VALUES(100,张三,10),(101,李四,20);数据插入成功关联关系合法。步骤 4插入非法数据测试外键校验插入一条班级编号为 30 的学生数据主表无该班级INSERT INTO stu VALUES(102,王五,30);执行报错ERROR 1452: Cannot add or update a child row含义从表引用了主表不存在的数据外键约束拦截非法数据保证表之间关联关系合法。10.4 外键的价值与使用场景如果不使用外键后端代码需要手动编写大量逻辑校验判断班级编号是否存在不仅增加开发量还容易出现遗漏最终数据库中出现 “孤儿数据”引用不存在关联的数据。外键将表关联校验交给数据库层执行从根源上杜绝逻辑脏数据。补充说明在高并发互联网项目中为了提升数据库性能部分架构会放弃外键将关联校验放到业务代码中但在传统管理系统、中小型项目、教学场景中外键依旧是首选。十一、综合实战商城数据库完整建表示例结合上文所有约束设计一套简易商城数据库包含数据库、商品表、客户表、订单表综合运用非空、默认值、注释、主键、自增、唯一键、外键所有约束模拟真实项目开发。11.1 创建数据库并指定编码-- 创建商城数据库设置字符集为 utf8 CREATE DATABASE IF NOT EXISTS bit32mall DEFAULT CHARACTER SET utf8; USE bit32mall;11.2 商品表goods综合使用主键自增、非空、默认值、注释约束CREATE TABLE IF NOT EXISTS goods( goods_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品编号自增主键, goods_name VARCHAR(32) NOT NULL COMMENT 商品名称, unitprice INT NOT NULL DEFAULT 0 COMMENT 商品单价单位分, category VARCHAR(12) COMMENT 商品分类, provider VARCHAR(64) NOT NULL COMMENT 供应商名称 );11.3 客户表customer综合使用主键自增、非空、枚举、唯一键、注释约束CREATE TABLE IF NOT EXISTS customer( customer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 客户编号自增主键, name VARCHAR(32) NOT NULL COMMENT 客户姓名, address VARCHAR(256) COMMENT 客户地址, email VARCHAR(64) UNIQUE KEY COMMENT 邮箱唯一不重复, sex ENUM(男,女) NOT NULL COMMENT 客户性别, card_id CHAR(18) UNIQUE KEY COMMENT 身份证号唯一不重复 );11.4 订单表purchase核心多表关联使用双外键关联客户表和商品表CREATE TABLE IF NOT EXISTS purchase( order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单编号自增主键, customer_id INT COMMENT 关联客户编号, goods_id INT COMMENT 关联商品编号, nums INT DEFAULT 0 COMMENT 购买数量, -- 外键1关联客户表 FOREIGN KEY(customer_id) REFERENCES customer(customer_id), -- 外键2关联商品表 FOREIGN KEY(goods_id) REFERENCES goods(goods_id) );以上三张表构成了商城最基础的业务模型所有约束各司其职完整保障数据格式、业务规则、多表关联的合法性也是企业开发中标准的表设计思路。十二、全文总结MySQL 约束是数据库设计的基石数据类型负责格式校验约束负责业务规则校验二者结合才能打造健壮、规范的数据存储体系。我们对全文知识点做整体梳理基础属性类约束NOT NULL禁止字段为空避免 NULL 运算异常开发中优先使用DEFAULT设置默认值简化重复数据插入COMMENT字段 / 表注释提升可维护性规范项目必备ZEROFILL整型补零格式化仅改变展示效果不修改原始数据。唯一性与自增约束PRIMARY KEY主键一张表唯一、非空用于标识单条数据AUTO_INCREMENT整型自增常搭配主键使用是主流主键方案UNIQUE KEY唯一键多张表可共存、允许空值用于业务唯一字段。多表关联约束FOREIGN KEY外键维护主表与从表的参照完整性杜绝无效关联数据。开发通用规范核心字段尽量设置NOT NULL配合DEFAULT默认值兜底主键推荐使用自增 ID无业务属性业务唯一字段使用唯一键中小型项目、管理系统建议使用外键从数据库层保证关联关系高并发场景可将校验下沉至业务代码所有字段、数据表添加清晰注释便于团队协作和后期维护。
MySQL 表约束全解：从原理、用法到实战案例（入门必看）
前言刚接触 MySQL 的同学几乎都会有一个疑问字段明明已经指定了数据类型为什么还要额外使用约束举个最简单的例子我们把年龄字段设置为int整型它只能保证存入的数据是整数却无法限制数值不能为负数将邮箱设置为字符串类型也不能自动保证邮箱地址不重复。数据类型仅仅是对数据格式做基础校验而数据库约束则是站在业务逻辑层面进一步规范数据、保证数据的完整性、合法性和关联性。在实际项目开发中数据库约束是保障数据质量的第一道防线。如果缺少约束数据表中很容易出现空值、重复数据、无效关联数据等脏数据不仅会增加后端代码的校验压力还会导致统计、查询、业务逻辑出现各类异常。本文将从零开始详细讲解 MySQL 中所有常用表约束包括非空、默认值、字段注释、零填充、主键、自增、唯一键、外键搭配大量可直接运行的 SQL 案例、报错解析、使用场景和实战建表示例适合 MySQL 初学者系统学习也可作为开发日常查阅手册。一、数据类型与约束的核心区别在正式学习约束之前我们先理清两个核心概念的边界这也是理解约束的前提。数据类型只限制数据的存储格式。比如int只能存整数、varchar只能存字符串、date只能存日期只要格式符合要求无论内容是否符合业务规则都可以正常存入数据表。约束Constraint在数据类型的基础上限制数据的业务合法性。结合项目规则规定字段是否允许为空、是否允许重复、取值范围、表与表之间的关联关系等。结合业务场景举例说明age int仅约束年龄为整数无法阻止存入 -10、-50 这类负数学号字段字符串类型无法保证学号唯一容易出现重复学号学生所属班级仅存班级编号无法校验该班级是否真实存在于班级表中。而以上所有业务规则都需要依靠数据库约束来实现。简单总结数据类型管格式约束管规则二者搭配才能构建规范的数据表。MySQL 主流常用约束汇总不区分大小写约束名称核心作用NOT NULL限制字段不能为空DEFAULT为字段设置默认值未传参时自动填充COMMENT为字段 / 表添加备注说明方便维护ZEROFILL整型字段自动补零格式化显示PRIMARY KEY主键约束唯一标识单条数据AUTO_INCREMENT整型字段自增长常与主键搭配UNIQUE KEY唯一键约束保证字段值不重复FOREIGN KEY外键约束维护多表之间的关联关系接下来我们逐个拆解每一种约束的语法、特性、案例、报错原因以及开发使用规范。二、NULL 与 NOT NULL 非空约束2.1 理解 MySQL 中的 NULL很多初学者会把NULL、空字符串、数值0混为一谈这是典型的认知误区。在 MySQL 中NULL代表无值、未知值不等同于 0、空字符串、falseMySQL 中字段默认允许为 NULL也就是不添加任何约束时字段可以不传入数据。NULL最大的问题是无法参与正常数学运算、逻辑判断任何数值与NULL做运算结果都会直接变为NULL。执行测试 SQL-- 查看 NULL 本身 SELECT NULL; -- 数值与 NULL 相加运算 SELECT 1 NULL;运行结果可以清晰看到1 NULL的最终结果依旧是NULL。在实际业务中这种特性会引发一系列问题数据求和、统计平均值、分组统计等报表计算全部失效后端代码需要额外增加大量判空逻辑代码冗余且容易出现 Bug。因此行业通用规范设计数据表时尽量避免字段允许为 NULL。2.2 NOT NULL 非空约束用法NOT NULL约束的作用非常明确强制该字段必须传入有效值不允许为空。案例创建班级表业务规则班级名称、教室编号是必填项不允许为空。-- 创建班级表两个字段均添加非空约束 CREATE TABLE myclass( class_name VARCHAR(20) NOT NULL, class_room VARCHAR(10) NOT NULL );使用DESC查看表结构DESC myclass;结果中Null列显示为NO代表该字段受非空约束限制禁止存入空值。非法数据插入与报错解析如果插入数据时跳过被NOT NULL约束的字段-- 仅传入班级名称未传入教室编号 INSERT INTO myclass(class_name) VALUES(class1);执行后会抛出错误ERROR 1364 (HY000): Field class_room doesnt have a default value报错原因class_room被设置为NOT NULL既没有手动传入数值也没有设置默认值MySQL 无法填充数据直接拒绝插入。这也印证了非空约束的强制校验效果。三、DEFAULT 默认值约束3.1 约束作用部分字段存在固定默认取值比如人员性别默认男、账户状态默认正常、年龄默认 0。如果每次插入数据都重复填写固定值会增加冗余操作。DEFAULT约束就是为字段预设默认值当插入数据时未给该字段传值MySQL 会自动填充预设的默认内容。3.2 建表示例创建测试表姓名非空年龄默认 0性别默认 “男”CREATE TABLE tt10( name VARCHAR(20) NOT NULL COMMENT 姓名, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年龄, sex CHAR(2) DEFAULT 男 COMMENT 性别 );查看表结构DESC tt10;在Default列可以看到对应字段的默认值Null列显示YES代表这两个字段允许为空有默认值兜底。3.3 默认值生效测试插入数据时仅填写必填的姓名字段INSERT INTO tt10(name) VALUES(zhangsan); -- 查询全表数据 SELECT * FROM tt10;查询结果年龄自动填充为 0性别自动填充为 “男”默认值约束正常生效。3.4 使用注意事项只有添加DEFAULT约束的字段插入数据时才可以省略不传值若字段同时设置NOT NULL且无默认值必须手动传值否则插入失败默认值的数据类型需要和字段本身数据类型保持一致。四、COMMENT 字段注释约束4.1 约束作用COMMENT用于给字段、数据表添加中文描述注释它不会影响数据存储、查询、业务逻辑核心作用偏向维护和文档管理方便开发人员理解字段含义尤其是多人协作项目帮助 DBA数据库管理员维护、排查数据表问题可直接基于注释生成数据库设计文档。在复杂项目中一张表可能包含几十甚至上百个字段清晰的注释是规范数据库设计的基本要求。4.2 带注释的建表示例CREATE TABLE tt12( name VARCHAR(20) NOT NULL COMMENT 用户姓名, age TINYINT UNSIGNED DEFAULT 0 COMMENT 用户年龄, sex CHAR(2) DEFAULT 男 COMMENT 用户性别 );4.3 查看注释的两种方式DESC 命令只能查看字段类型、非空、主键等基础信息无法查看注释SHOW CREATE TABLE完整查看建表语句包含所有字段、表的注释是查看注释的标准命令。执行命令SHOW CREATE TABLE tt12\G终端会展示完整的建表语句每个字段后都能看到对应的COMMENT注释内容。五、ZEROFILL 零填充约束5.1 整型长度的误区初学者经常疑惑int(10)、int(5)中括号内的数字代表什么众所周知int类型固定占用 4 字节和括号内数字无关。核心结论在没有添加 ZEROFILL 零填充时整型后括号内的长度参数毫无意义仅为预留格式只有搭配ZEROFILL该长度才会生效。5.2 ZEROFILL 特性ZEROFILL是专门作用于整型字段的格式化约束按照设定的长度对数值左侧自动补 0仅改变展示效果数据库底层实际存储的原始数值不变一旦添加ZEROFILL字段会自动附带UNSIGNED无符号属性。5.3 完整实操案例步骤 1创建测试表并插入数据CREATE TABLE tt3 ( a INT(10) UNSIGNED, b INT(10) UNSIGNED ); INSERT INTO tt3 VALUES(1,2); SELECT * FROM tt3;此时两个字段均无零填充正常展示数值1和2。步骤 2为 a 字段添加 ZEROFILL 约束修改表结构将a字段长度改为 5并开启零填充ALTER TABLE tt3 CHANGE a a INT(5) UNSIGNED ZEROFILL; SHOW CREATE TABLE tt3\G查看建表语句可看到a字段已携带ZEROFILL属性。步骤 3查询验证展示效果SELECT * FROM tt3;结果中a字段的1被格式化为00001按照 5 位长度左侧补零而b字段保持原样。步骤 4验证底层存储不变使用HEX()函数查看数据底层存储SELECT a, HEX(a) FROM tt3;结果显示HEX(a)的值为1证明数据库内部依旧存储原始数字补零只是前端格式化展示不会改变真实数据。5.4 适用场景编号类字段流水号、工号、订单编号是ZEROFILL的主要使用场景比如要求编号固定 6 位不足位数补 0。六、PRIMARY KEY 主键约束6.1 主键的定义与核心特性主键PRIMARY KEY是数据表中唯一标识单条记录的字段相当于每条数据的 “身份证号”是数据库设计中最核心的约束之一。主键三大硬性规则值唯一整张表中主键值不能重复非空主键字段不允许为NULL数量限制一张表只能有一个主键。6.2 单列主键创建与测试1. 建表时指定主键CREATE TABLE tt13( id INT UNSIGNED PRIMARY KEY COMMENT 学号主键, name VARCHAR(20) NOT NULL COMMENT 姓名 );执行DESC tt13Key列显示PRI代表该字段为主键。2. 主键唯一性测试插入第一条数据INSERT INTO tt13 VALUES(1,aaa);再次插入相同主键值的数据INSERT INTO tt13 VALUES(1,bbb);系统抛出报错ERROR 1062: Duplicate entry 1 for key PRIMARY报错含义主键值1已存在违反唯一性约束插入失败。6.3 后期添加 / 删除主键如果建表时未设置主键可通过ALTER TABLE动态修改-- 给已有表添加主键 ALTER TABLE 表名 ADD PRIMARY KEY(字段名); -- 删除表的主键 ALTER TABLE 表名 DROP PRIMARY KEY;6.4 复合主键联合主键1. 概念当单个字段无法唯一标识数据时可以使用多个字段组合作为主键也就是复合主键。 规则多个字段组合的值整体唯一单独某个字段可以重复。典型场景学生选课表一条记录由「学生 ID 课程 ID」共同唯一确定。2. 复合主键建表示例CREATE TABLE tt14( id INT UNSIGNED COMMENT 学生ID, course CHAR(10) COMMENT 课程编号, score TINYINT UNSIGNED DEFAULT 60 COMMENT 考试分数, -- 多个字段联合作为主键 PRIMARY KEY(id, course) );3. 规则验证允许数据学生 ID1课程 C学生 ID1课程 Java单字段重复组合不重复禁止数据两条记录同时为 1 C组合值重复违反主键规则。复合主键在多对多关联表中使用非常广泛。七、AUTO_INCREMENT 自增长约束7.1 约束特性AUTO_INCREMENT是整型字段自增长约束搭配主键使用频率最高。规则插入数据时不给该字段传值MySQL 自动取当前字段最大值 1生成新值使用前提字段必须是索引主键、唯一键且数据类型为整型一张表最多只能有一个自增长字段。7.2 实战案例自增主键创建自增主键表CREATE TABLE tt21( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 自增主键ID, name VARCHAR(10) NOT NULL DEFAULT COMMENT 姓名 );插入数据时省略自增主键字段INSERT INTO tt21(name) VALUES(a); INSERT INTO tt21(name) VALUES(b); SELECT * FROM tt21;查询结果中id自动从 1 开始依次递增无需手动赋值。7.3 开发使用建议自增 ID 是目前互联网项目最主流的主键方案无业务含义、纯数字、递增有序后续业务调整不会受影响。八、UNIQUE KEY 唯一键约束8.1 为什么需要唯一键我们已知一张表只能有一个主键但业务中往往有多个字段需要保证唯一性手机号、身份证、邮箱、工号等。此时就需要使用UNIQUE KEY唯一键约束。8.2 唯一键核心特性字段值全局唯一不允许重复允许为 NULL且多个NULL互不冲突NULL 不参与唯一性比对一张表可以创建多个唯一键。8.3 案例与测试1. 创建带唯一键的数据表CREATE TABLE student( id CHAR(10) UNIQUE COMMENT 学号唯一键, name VARCHAR(10) COMMENT 姓名 );2. 唯一性测试插入重复数据会触发Duplicate entry重复报错和主键报错一致。3. NULL 兼容性测试INSERT INTO student VALUES(NULL,bbb);语句可以正常执行验证唯一键允许存入空值。九、主键 vs 唯一键 详细对比很多初学者容易混淆主键和唯一键这里做清晰对比并结合业务场景讲解选型规范对比维度主键PRIMARY KEY唯一键UNIQUE KEY唯一性必须唯一必须唯一空值禁止 NULL允许 NULL数量一张表仅能有 1 个一张表可存在多个核心用途标识单条数据逻辑主键保障业务字段唯一性开发最佳实践主键优先选用无业务含义的自增 ID自增数字和业务无关后续业务迭代、字段修改都不会影响主键业务唯一字段设置为唯一键身份证、手机号、邮箱等业务唯一字段使用UNIQUE KEY禁止使用身份证、手机号等业务字段作为主键一旦业务规则变更如手机号升级会引发全表数据重构。十、FOREIGN KEY 外键约束10.1 外键的作用前面所有约束都是单表约束而外键FOREIGN KEY是专门用于维护多表之间关联关系的约束也是保证参照完整性的核心。相关概念主表父表被引用的表字段必须是主键 / 唯一键从表子表引用主表字段的表被引用的字段就是外键。外键核心规则从表中的外键值必须在主表的对应字段中存在不允许引用不存在的数据。10.2 外键基础语法FOREIGN KEY(从表字段) REFERENCES 主表(主表主键字段)10.3 完整实操案例班级表 学生表步骤 1创建主表班级表-- 主表班级表 CREATE TABLE myclass( id INT PRIMARY KEY COMMENT 班级编号主键, name VARCHAR(30) NOT NULL COMMENT 班级名称 );步骤 2创建从表学生表并添加外键学生表的class_id引用班级表的主键id-- 从表学生表class_id 为外键 CREATE TABLE stu( id INT PRIMARY KEY COMMENT 学生编号, name VARCHAR(30) NOT NULL COMMENT 学生姓名, class_id INT COMMENT 所属班级编号, -- 建立外键关联 FOREIGN KEY(class_id) REFERENCES myclass(id) );步骤 3插入合法关联数据先给主表插入班级数据INSERT INTO myclass VALUES(10,C大牛班),(20,Java大神班);再给从表插入学生数据班级编号在主表中存在INSERT INTO stu VALUES(100,张三,10),(101,李四,20);数据插入成功关联关系合法。步骤 4插入非法数据测试外键校验插入一条班级编号为 30 的学生数据主表无该班级INSERT INTO stu VALUES(102,王五,30);执行报错ERROR 1452: Cannot add or update a child row含义从表引用了主表不存在的数据外键约束拦截非法数据保证表之间关联关系合法。10.4 外键的价值与使用场景如果不使用外键后端代码需要手动编写大量逻辑校验判断班级编号是否存在不仅增加开发量还容易出现遗漏最终数据库中出现 “孤儿数据”引用不存在关联的数据。外键将表关联校验交给数据库层执行从根源上杜绝逻辑脏数据。补充说明在高并发互联网项目中为了提升数据库性能部分架构会放弃外键将关联校验放到业务代码中但在传统管理系统、中小型项目、教学场景中外键依旧是首选。十一、综合实战商城数据库完整建表示例结合上文所有约束设计一套简易商城数据库包含数据库、商品表、客户表、订单表综合运用非空、默认值、注释、主键、自增、唯一键、外键所有约束模拟真实项目开发。11.1 创建数据库并指定编码-- 创建商城数据库设置字符集为 utf8 CREATE DATABASE IF NOT EXISTS bit32mall DEFAULT CHARACTER SET utf8; USE bit32mall;11.2 商品表goods综合使用主键自增、非空、默认值、注释约束CREATE TABLE IF NOT EXISTS goods( goods_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品编号自增主键, goods_name VARCHAR(32) NOT NULL COMMENT 商品名称, unitprice INT NOT NULL DEFAULT 0 COMMENT 商品单价单位分, category VARCHAR(12) COMMENT 商品分类, provider VARCHAR(64) NOT NULL COMMENT 供应商名称 );11.3 客户表customer综合使用主键自增、非空、枚举、唯一键、注释约束CREATE TABLE IF NOT EXISTS customer( customer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 客户编号自增主键, name VARCHAR(32) NOT NULL COMMENT 客户姓名, address VARCHAR(256) COMMENT 客户地址, email VARCHAR(64) UNIQUE KEY COMMENT 邮箱唯一不重复, sex ENUM(男,女) NOT NULL COMMENT 客户性别, card_id CHAR(18) UNIQUE KEY COMMENT 身份证号唯一不重复 );11.4 订单表purchase核心多表关联使用双外键关联客户表和商品表CREATE TABLE IF NOT EXISTS purchase( order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单编号自增主键, customer_id INT COMMENT 关联客户编号, goods_id INT COMMENT 关联商品编号, nums INT DEFAULT 0 COMMENT 购买数量, -- 外键1关联客户表 FOREIGN KEY(customer_id) REFERENCES customer(customer_id), -- 外键2关联商品表 FOREIGN KEY(goods_id) REFERENCES goods(goods_id) );以上三张表构成了商城最基础的业务模型所有约束各司其职完整保障数据格式、业务规则、多表关联的合法性也是企业开发中标准的表设计思路。十二、全文总结MySQL 约束是数据库设计的基石数据类型负责格式校验约束负责业务规则校验二者结合才能打造健壮、规范的数据存储体系。我们对全文知识点做整体梳理基础属性类约束NOT NULL禁止字段为空避免 NULL 运算异常开发中优先使用DEFAULT设置默认值简化重复数据插入COMMENT字段 / 表注释提升可维护性规范项目必备ZEROFILL整型补零格式化仅改变展示效果不修改原始数据。唯一性与自增约束PRIMARY KEY主键一张表唯一、非空用于标识单条数据AUTO_INCREMENT整型自增常搭配主键使用是主流主键方案UNIQUE KEY唯一键多张表可共存、允许空值用于业务唯一字段。多表关联约束FOREIGN KEY外键维护主表与从表的参照完整性杜绝无效关联数据。开发通用规范核心字段尽量设置NOT NULL配合DEFAULT默认值兜底主键推荐使用自增 ID无业务属性业务唯一字段使用唯一键中小型项目、管理系统建议使用外键从数据库层保证关联关系高并发场景可将校验下沉至业务代码所有字段、数据表添加清晰注释便于团队协作和后期维护。
