9.深度剖析MySQL约束的工程设计：自增主键的分布式局限、外键约束的权衡，与CHECK的版本适配实践

目录一、上节课复习MySQL到底是个啥玩意儿主键的坑你踩过吗二、外键约束父表和子表的爱恨情仇实战场景电商网站的商品下架三、check约束一、上节课复习MySQL到底是个啥玩意儿首先mysql是一个客户端-服务器结构的程序。这意味着我们通过Navicat这样的客户端通过网络发送SQL请求给MySQL服务器服务器处理完再通过网络返回结果。客户端和服务器可以在同一台机器也可以在不同机器。基本操作就是经典的增删改查insertdeleteupdateselect然后重点来了——数据库约束。计算机圈子里流传了一句话人是不靠谱的数据库的数据通常非常重要必须保证准确性程序的约束就是其中一种。常见的约束有这些NOT NULL非空约束DEFAULT默认约束UNIQUE唯一约束PRIMARY KEY主键约束FOREIGN KEY外键约束CHECK检查主键的坑你踩过吗一个表中只能有一个主键通常主键使用整数来表示。最常用的是自增主键create table student (id int primary key auto_increment, name varchar(20));但是这里有个巨坑——自增主键仅限于单节点的MySQL服务器场景使用。如果是多节点的情况比如大公司数据量级非常庞大靠一个服务器存储不现实搞了20个MySQL服务器构成的集群这时候自增主键就可能会重复因为各个节点的自增是独立的AB之间的自增主键是会出现重复的。业界流传了很多分布式情况下唯一的id生成算法比如雪花算法。核心思想是把时间戳主机的标识随机的数字然后通过hash算法映射成整数。虽然hash算法存在冲突概率但工程上选择好的hash算法可以让这个概率变得极低工程上忽略不计。二、外键约束父表和子表的爱恨情仇外键约束涉及到两个表的关系。比如学生表(studentId, name, classId)和班级表(classId, name)。班级表是父表学生表是子表。设定外键约束时需要明确当前表的哪个列和另外一个表的哪个列建立关联关系create table class (classId int primary key auto_increment, name varchar(20)); create table student (studentId int primary key auto_increment, name varchar(20), classId int, foreign key (classId) REFERENCES class(classId));父约束子子也在约束父——这就是外键的精髓当你往student表插入数据时会先触发一次针对class表的查询查到结果了才能插入没有查到就会报错insert into student values(1, 张三, 1), (2, 李四, 2), (3, 王五, 3); -- 如果class表里没有classId3这里就会报错修改时同样会触发查询update student set classId 100 where studentId 3; -- 也会报错更狠的是删除和修改父表时也会去查询子表看子表是否有数据使用有的话就失败没有才能正常进行delete from class where classId 2; -- 会报错实战场景电商网站的商品下架我这里可以提一个很经典的场景淘宝这样的电商网站商品表goods(id, name, price...)订单表order(id, time, goodsId)。订单表的goodsId应该是出自于商品表。如果商品下架了delete能删除吗如果用外键约束是不能直接delete的会报错那怎么办逻辑删除不是使用delete而是通过update把is_online字段改成0。返回商品列表的时候select ... where is_online 1。这就像是学习数据结构中的顺序表里的逻辑删除——不需要把usedSize那个位置的元素干掉只是把标志位改成0。虽然意味着数据库的内容越来越多空间浪费就浪费了毕竟硬盘不值钱这不是主要矛盾。如果不加外键约束就会报错核心问题在于索引。子表中插入数据就会涉及到自动在父表中进行查询。索引相当于在表中搞了一个特殊的数据结构相当于目录可以加快查询的速度。默认是顺序遍历O(N)比较低效被标记为主键的列会自动创建索引。如果引用的是其他列也可以给其他的列手动创建索引create table class(classId int unique, name varchar(20)); create table student(studentId int, name varchar(20), classId int, FOREIGN key (classId) REFERENCES class(classId));unique这样也可以起到一个自动创建索引的效果。三、check约束check (表达式) 是用来做条件判断的后续插入修改数据都会自动代入条件条件成立才能够进行插入修改。create table student (id int, name varchar(20), gender varchar(10)); insert into student values (1, 张三, 武装直升机), (2, 李四, 沃尔玛购物袋); select * from student; -- 可以明显看到这样插入的性别非常不合理但也可以插入然后加上check约束drop table student; create table student (id int, name varchar(20), gender varchar(10), check (gender 男 or gender 女)); insert into student values (1, 张三, 武装直升机), (2, 李四, 沃尔玛购物袋); select * from student; -- 这样就会报错了再比如create table score(id int, score int check (score 0 and score 100)); insert into score values(1, 101); -- 像这种约束也能起作用但是​ check是从mysql 8.0.16这个版本开始支持的。当前很多公司用的是5.7版本也有老项目用更早的。组件的升级对于很多公司来说是不愿意做的。之前check虽然不会报错但是实际没有生效——纯属摆设希望这篇笔记能帮到正在学MySQL的兄弟们少走点弯路少踩点坑有问题评论区交流一起加油