MySQL相关知识点可以通过点击以下链接进行学习一起加油文章目录表的约束数据完整性的守护者1. 空属性 (NULL / NOT NULL)NOT NULL 约束实践2. 默认值 (DEFAULT)DEFAULT 与 NOT NULL 的共存3. 列描述 (COMMENT)4. 零填充 (ZEROFILL)5. 主键 (PRIMARY KEY)主键的创建与作用复合主键6. 自增 (AUTO_INCREMENT)控制AUTO_INCREMENT的起始值获取最后插入的自增ID7. 唯一键 (UNIQUE KEY)主键与唯一键的选择哲学8. 外键 (FOREIGN KEY)外键约束的效果综合案例商城数据库设计在掌握了MySQL的数据类型之后下一个核心议题便是表的约束Constraints。数据类型本身虽然为字段提供了基础的类型和范围限制但这种约束力相对单一。为了从更贴近业务逻辑的层面保证数据的合法性、准确性和一致性MySQL提供了一套丰富的表约束机制。这些约束如同数据库的“法律法规”确保所有写入的数据都符合预设的规则从而极大地提升了数据的质量和可靠性。表的约束数据完整性的守护者表的约束是施加在表列上的规则用于限制可以存入表中的数据。它们通过技术手段从数据库层面引导开发者和用户插入正确的数据防止因误操作或程序逻辑漏洞导致的数据污染。常见的表约束包括NOT NULL非空约束DEFAULT默认值约束COMMENT列注释ZEROFILL零填充属性PRIMARY KEY主键约束AUTO_INCREMENT自增属性UNIQUE KEY唯一键约束FOREIGN KEY外键约束1. 空属性 (NULL/NOT NULL)在数据库中一个字段的值可以被定义为允许NULL或不允许NULL。NULL是一个特殊的值表示“未知”或“不存在”它不等同于数字0也不等同于空字符串。NULLvs的本质区别NULL表示该字段没有任何值。在概念上就像一个人没有办理过银行卡。空字符串表示该字段有一个值但这个值是空的。概念上就像一个人有银行卡但卡里余额为零。这个区别在数据处理中至关重要因为NULL值不能参与大多数算术运算和比较运算。任何与NULL进行的运算结果仍然是NULL。mysqlselect1null;----------|1null|----------|NULL|----------这个简单的SQL查询展示了NULL的传播性。1加上一个未知的值结果依然是未知的。在实际的业务开发中应尽可能地为字段设置NOT NULL约束。因为NULL值会给应用程序的逻辑带来额外的复杂性需要频繁地进行IS NULL或IS NOT NULL的判断稍有疏忽就可能引发空指针异常或逻辑错误。NOT NULL约束实践设想一个场景创建一个班级表包含班级名和教室位置。从业务逻辑上讲一个班级必须有名字也必须有上课的教室。如果这两项信息缺失数据就是无效的。*这个操作序列演示了NOT NULL约束的效果建表t1表的class_name和class_room列都被定义为NOT NULL。插入失败隐式NULL尝试只插入class_name不为class_room提供值。MySQL报错Field class_room doesnt have a default value因为它既不能为空又没有默认值所以插入被拒绝。插入失败显式NULL尝试将class_room的值显式设置为NULL。MySQL报错Column class_room cannot be null直接指出了违反了非空约束。插入成功为所有NOT NULL的列都提供了有效值插入操作成功。*NOT NULL约束是保证数据完整性的第一道防线。2. 默认值 (DEFAULT)DEFAULT约束用于为列指定一个默认值。当插入一条新记录但没有为该列提供值时MySQL会自动使用这个默认值。t2表的age和sex列分别设置了默认值18和男。当插入记录时只提供了nameage和sex自动填充为默认值。当显式提供了age和sex时则使用提供的值。DEFAULT与NOT NULL的共存一个常见的疑问是DEFAULT和NOT NULL能否同时作用于一个列如果可以是否有意义创建了t3表name列同时拥有NOT NULL和DEFAULT unnamed两个约束。*测试插入不提供name值插入成功name被自动设为默认值unnamed。显式提供name为张三插入成功。显式提供name为NULL插入失败因为NOT NULL约束禁止显式插入NULL。*结论NOT NULL和DEFAULT在语法上可以共存。它们的组合效果是不允许显式插入NULL但在不提供值时会自动填充默认值。这种组合在某些场景下是有用的它确保了字段永远有值同时为开发者提供了便利。但在通常的设计中如果一个字段有默认值它本质上就不会为NULL所以NOT NULL约束有时会显得冗余。这个例子作为对照当一个列没有任何约束时如果不提供值它默认为NULL。3. 列描述 (COMMENT)COMMENT约束并不对数据本身施加任何限制它的作用是为列添加一段描述性文本。这个注释会存储在数据库的元数据中是为数据库管理员DBA和开发者准备的“文档”。创建t4表时为name列添加了注释。使用desc t4命令时Comment列是空的无法看到注释。要查看注释必须使用show create table t4;或show full columns from t4;。这说明COMMENT是元数据的一部分而不是简单的表结构摘要。4. 零填充 (ZEROFILL)ZEROFILL是一个作用于数值类型的显示属性而不是一个真正的约束。它通常与INT(M)中的M显示宽度配合使用。对于INT(10)这里的10在没有ZEROFILL的情况下并没有实际的约束作用INT依然是4个字节其取值范围也不会改变。当ZEROFILL被启用时M的意义就显现出来了它指定了数值显示的最小宽度。如果实际数值的位数小于MMySQL会在左边用0来填充直到达到M的宽度。创建了t6表num列是INT(5) ZEROFILL。插入1后查询结果显示为00001。这正是ZEROFILL的效果。重要特性仅影响显示ZEROFILL只改变数据的显示格式数据库中实际存储的仍然是原始数值。自动UNSIGNED当为一个列指定ZEROFILL时MySQL会自动为该列添加UNSIGNED属性。show create table t6;的结果清晰地显示num列的类型被MySQL解释为int(5) unsigned zerofill。这印证了ZEROFILL会自动附加UNSIGNED。关于INT的默认显示宽度默认情况下一个有符号INT的显示宽度是11而无符号INT是10。这是因为有符号INT的最大值是214748364710位最小值是-214748364810位加一个负号共11个字符位。无符号INT最大值4294967295是10位。5. 主键 (PRIMARY KEY)主键是表中用于唯一标识每一行记录的一列或一组列。它是数据库中最重要的约束之一。主键的特性唯一性Uniqueness主键列中的每个值都必须是唯一的。非空性Not Null主键列不允许有NULL值。唯一索引MySQL会自动在主键列上创建一个唯一的索引以加速数据检索。单一性每张表只能有一个主键。主键的创建与作用方法1在列定义中指定创建t12表时在id列后直接跟上PRIMARY KEY。desc t12;的结果显示id列的Null字段为NOKey字段为PRIPrimary。这表明MySQL在设置主键时自动强制了非空约束。主键的唯一性约束效果第一次插入id1成功。第二次尝试插入id1时MySQL报错Duplicate entry 1 for key PRIMARY明确指出了主键冲突插入被拒绝。方法2在表定义末尾指定CREATETABLEt_example(idINT,nameVARCHAR(20),PRIMARYKEY(id));方法3为已存在的表添加主键ALTERTABLEtable_nameADDPRIMARYKEY(column_name);删除主键ALTERTABLEtable_nameDROPPRIMARYKEY;删除主键后desc结果显示Key字段变为空但Null字段依然是NO说明删除主键不会移除原有的非空约束。复合主键一个主键可以由多个列组成这称为复合主键。复合主键的唯一性体现在所有主键列的组合值必须是唯一的。创建tt14表primary key(id, course)定义了id和course为复合主键。desc结果显示这两列的Key都是PRI。insert into tt14 (id,course)values(1, 123);成功。insert into tt14 (id,course)values(1, 456);成功因为(1, 456)与(1, 123)不同。insert into tt14 (id,course)values(1, 123);失败因为组合值(1, 123)重复。*复合主键常用于关系表或需要通过多个维度来唯一确定一条记录的场景。6. 自增 (AUTO_INCREMENT)AUTO_INCREMENT属性通常与主键配合使用创建一个“代理主键”或“逻辑主键”。当插入新记录时不为该列提供值MySQL会自动为其生成一个唯一的、递增的整数。自增的特点基于索引一个列要成为自增列它必须是一个索引通常是主键或唯一键。整数类型自增列必须是整数类型。表内唯一一张表最多只能有一个自增列。*t15表的id是PRIMARY KEY AUTO_INCREMENT。第一次插入不提供idid自动变为1。第二次插入id自动变为2。第三次显式插入id5插入成功。第四次插入不提供idMySQL会找到当前id的最大值5然后加1所以新id是6。*控制AUTO_INCREMENT的起始值可以在创建表时或之后通过AUTO_INCREMENT value来设置下一个自增值的起点。创建t16表时指定AUTO_INCREMENT100。第一次插入后id的值就是100。获取最后插入的自增ID在程序中插入一条记录后往往需要立即获取其生成的自增ID以便进行后续操作。这可以通过SELECT LAST_INSERT_ID();函数实现。插入一条记录到t15后立即执行SELECT LAST_INSERT_ID();返回了刚才生成的id值7。这个函数返回的是当前连接上一次INSERT操作生成的第一个AUTO_INCREMENT值。7. 唯一键 (UNIQUE KEY)当一张表中除了主键之外还有其他列也需要保证其值的唯一性时就需要使用唯一键。唯一键与主键的异同相同点都强制唯一性都会自动创建索引。不同点数量一张表只能有一个主键但可以有多个唯一键。NULL值主键不允许NULL。唯一键允许NULL并且可以有多个NULL值因为NULL不等于任何值包括另一个NULL。*t17表的id是主键phone_num是唯一键。插入phone_num为110的记录成功。再次插入110失败违反唯一键约束。插入phone_num为NULL的记录成功。再次插入phone_num为NULL的记录依然成功。这证明了唯一键对NULL值的特殊处理。*主键与唯一键的选择哲学主键其核心目的是标识行。它应该是稳定、不变、且与具体业务逻辑无关的。因此使用AUTO_INCREMENT的整数作为主键是最佳实践。唯一键其核心目的是保证业务数据的唯一性。例如用户表中的用户名、邮箱、手机号等都应该是唯一键因为它们在业务上是不允许重复的。8. 外键 (FOREIGN KEY)外键用于在两个表之间建立和强制实施一种关联关系以保证引用完整性Referential Integrity。主表父表被引用的表如班级表。从表子表引用主表的表如学生表。外键约束定义在从表上要求外键列的值必须存在于主表的主键或唯一键列中或者为NULL。创建了classes主表和students从表。students表的class_id列通过FOREIGN KEY (class_id) REFERENCES classes(id)与classes表的id主键建立了外键关系。desc students;的结果中class_id的Key字段为MUL表示它是一个非唯一的索引并且是外键的一部分。外键约束的效果*先向classes表插入id1和id2的班级。然后向students表插入学生插入class_id1的学生成功因为classes表中存在id1的班级。插入class_id3的学生失败并报错a foreign key constraint fails因为classes表中不存在id3的班级。*这里演示了外键对主表删除操作的约束。尝试删除classes表中id1的班级失败了因为students表中还有学生属于这个班级。这有效地防止了“孤儿数据”的产生。外键是关系型数据库保证数据一致性的强大工具但它也会带来一定的性能开销每次对从表的写操作都需要检查主表。在某些追求极致性能的互联网应用中有时会选择在应用层逻辑中来保证引用完整性而不在数据库层面设置物理外键。综合案例商城数据库设计通过一个综合案例可以将前面学到的所有约束知识融会贯通。业务需求商品表(goods)商品编号、名称、单价、类别、供应商。客户表(customer)客户号、姓名、地址、邮箱、性别、身份证号。购买表(purchase)订单号、客户号、商品号、数量。约束要求确定各表的主键和外键。客户姓名不能为空。邮箱和身份证号不能重复。性别只能是“男”或“女”。建表SQL脚本-- 创建数据库CREATEDATABASEIFNOTEXISTSmallDEFAULTCHARACTERSETutf8;USEmall;-- 商品表CREATETABLEIFNOTEXISTSgoods(goods_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT商品编号,goods_nameVARCHAR(32)NOTNULLCOMMENT商品名称,unitpriceINTNOTNULLDEFAULT0COMMENT单价单位分,categoryVARCHAR(12)COMMENT商品分类,providerVARCHAR(64)NOTNULLCOMMENT供应商名称);-- 客户表CREATETABLEIFNOTEXISTScustomer(customer_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT客户编号,nameVARCHAR(32)NOTNULLCOMMENT客户姓名,addressVARCHAR(256)COMMENT客户地址,emailVARCHAR(64)UNIQUEKEYCOMMENT电子邮箱,sexENUM(男,女)NOTNULLCOMMENT性别,card_idCHAR(18)UNIQUEKEYCOMMENT身份证);-- 购买表 (关系表)CREATETABLEIFNOTEXISTSpurchase(order_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT订单号,customer_idINTCOMMENT客户编号,goods_idINTCOMMENT商品编号,numsINTDEFAULT0COMMENT购买数量,FOREIGNKEY(customer_id)REFERENCEScustomer(customer_id),FOREIGNKEY(goods_id)REFERENCESgoods(goods_id));设计分析goods和customer表都使用了INT PRIMARY KEY AUTO_INCREMENT作为无业务含义的代理主键。goods_name、unitprice、provider和customer.name都使用了NOT NULL约束。customer.email和customer.card_id使用了UNIQUE KEY来保证业务唯一性。customer.sex使用了ENUM类型来限制取值。purchase表是连接客户和商品的关系表其customer_id和goods_id列分别作为外键引用了customer表和goods表的主键从而建立了数据间的关联保证了引用完整性。通过这个案例可以看到表的约束是如何将抽象的业务规则转化为具体的数据库结构为构建一个健壮、可靠的数据系统奠定了坚实的基础。