MySQL：数据库的约束与表设计的简要理念

7. 数据库的约束

约束可以理解成，数据库提供的一种针对数据的合法性验证的机制，数据库中经常会涉及大量的数据，也会涉及到大量的增删改查操作，如果数据库不提供这样的校验机制，就只能靠程序员手工来保证，基本认为是不靠谱的

7.1 NOT NULL

设定NOT NULL，说明列里不能存储空值，表里的内容就是必填项

7.2 UNIQUE

create table student (id int unique, name varchar(20) unique);

设定这一列的所有行的数据都得是唯一的（不能重复）

每次插入/修改，都要先触发查询，如果当前插入/修改，值已经存在，就会插入/修改失败

加上unique之后，执行效率就会降低，对数据的校验更严格了，有助于写代码的时候减少出错的概率，提高开发的效率

是否有办法，不删除重新建表，也能添加约束？ — SQL有一个 alter table 操作，不仅能够修改约束，还能修改列名/类型/新增列/删除列… ，用法非常复杂，工作和面试也很少涉及

7.3 DEFAULT

create table student(id int, name varchar(20) default 'li');

指定默认值，不进行任何指定，默认值就是NULL，在指定列插入的时候，会用到

设置好了之后，后续进行指定列插入的时候，未被指定的列就会保持默认值

7.4 PRIMARY KEY

create table student(id int primary key, name varchar(20));

主键是一条记录的身份标识：NOT NULL + UNIQUE

比较两个东西，是否是同一个，就可以通过身份标识来进行区分

• 不能为NULL

• 不能重复

• 一般给表设定主键，都是使用数字（整数形式），很少会使用字符串

• 一个表只能有一个主键（怕出现二义性），一个主键不一定只针对一个列（联合主键：把多个列的内容联合到一起，共同构成一个主键，很少使用）

• 当某个列集合了not null和unique就成为主键了

自增主键

create table student(id int primary key auto_increment, name varchar(20));
insert into student values (null, '张三');
insert into student values (null, '李四');
insert into student values (null, '王五');

每次插入新的数据，都可以把主键基于上一条数据的主键再+1，此时插入数据，主键的值就不必手动指定了,当然也可以手动指定，不一定非要依赖自增主键

规则是最大之上+1

自增主键，本质上是MySQL服务器存储了当前表中的最大ID，再进一步的进行累加的，基于分布式系统，存储数据的时候，MySQL自增主键就无能为力了，如果要存储的数据量非常大，一台机器存不下，就需要引入若干台机器了，每个机器存储一部分数据

在分布式系统中，如何生成唯一ID，具体的生成算法有很多种，此处给出一个最简单最朴素的方式，其他的生成算法核心思路大同小异

时间戳（ms） + 机器的编号 + 随机因子（随机数）

hash算法

md5，sha1本质上都是数学问题，套公式，把字符串每个字符代表的数字，代入公式，进行一系列计算得到hash值

相比于具体的算法，更关注特性，以md5为例：

• 定长（校验和）：无论输入的字符串多长，最终算出来的hash值都是一样长的
• 分散（hash算法）：输入的字符串，哪怕只有一点点不一样，得到的md5值都会差异很大
• 不可逆（加密）：给定hash值，还原成原始的字符串，理论上不可行

7.5 FOREIGN KEY

create table class (classId int primary key, className varchar(20));
create table student (id int primary key auto_increment, name varchar(20), classId int, foreign key(classId) references class(classId));

外键涉及到两个表的关系，和unique类似的效果，都要在插入/修改之前，进行查询

引用父表的这一列，要么是主键，要么是unique

此时，student的classId就和class表中的classId建立了联系，student classId中的值，必须要在class表的classId中存在，class表就对student表产生了制约，称class表为父表（parent table），student表为子表（child table）

一旦建立好外键约束，后续针对子表进行操作，就会频繁的涉及到在父表中针对被引用的列， 进行查询操作

数据库中，针对主键/unique，查询操作都是有额外的优化策略的，使这里的查询效率比较高，比遍历表要更高

7.6 CHECK

check指定条件，插入/修改数据，数据符合条件才能插入/修改成功，不符合条件，直接失败

MySQL 5.7 不支持check

比如某个表有一列性别 varchar(1)类型，通过check一个条件（gender = ‘男’ or gender = ‘女’）

考虑一个场景：电商网站

商品表(id,name,price....)//父表
订单表(orderId,...,goodId)//子表

如何将某一个商品下架

商品表(id,name,price....isOk)//父表 1表示有效数据，0表示无效数据

当我们要删除商品的时候，不是delete，而是把isOk改成0

后续用户查询商品列表的时候，也是通过条件，只返回isOk为1的记录

在硬盘上删除一个文件，其实也不是说把硬盘上的对应空间的数据给擦除，也是标记为无效，标记成无效之后，可能就被系统用来存储别的数据

8. 表的设计

此处只介绍基础的方法论

8.1 设计表的通用步骤

• 理清楚需求场景中的“实体”（entry）有哪些
• 理清楚实体之间的关系

8.2 实体之间的关系

1. 一对一

• 一个学生只能有一个账号，一个账号只能属于一个学生
• 设计方式：
  • 一张表：既包含学生的信息，又包含账号的信息
  • 两张表：
    • student (studentId,name,gender,classId…accountId)
    • account(accountId, username, password,loginTime…studentId)

2. 一对多

• 一个班级可以包含多个学生，一个学生只能存在于一个班级
• 设计方式：
  • 第一种：
    • 学生表(Id, name…)
    • 班级表(classId, className, studentList) 就像一个数组一样，存储了哪些学生的Id
  • 第二种：
    • 学生表(Id, name, classId)
    • 班级表(classId, className)

3. 多对多

• 一个学生可以选择多个课程来学习，一个课程也可以被多个学生选择
• 设计方式，引入关联表：
  • student(id, name…)
  • course(courseId, courseName)
  • student_course(studentId,courseId)
• 关联表的数据是否要同时和原来的实体的数据表一起进行添加更新，要看实际场景