MySQL之范式的使用详解

admin MySQL 2022-02-09 20:54:59 MySQL 范式"

一、范式

范式的英文名称是Normal Form，它是英国人E.F.Codd（关系数据库的老祖宗）在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。目前有迹可寻的共有8种范式，依次是：1NF，2NF，3NF，BCNF，4NF，5NF，DKNF，6NF。通常所用到的只是前三个范式，即：第一范式（1NF），第二范式（2NF），第三范式（3NF）。

第一范式（1NF）

第一范式其实是关系型数据库的基础，即任何关系型数据库都是符合第一范式的。简单的将第一范式就是每一行的各个数据都是不可分割的，同一列中不能有多个值，如果出现重复的属性就需要定义一个新的尸实体。
下面数据库便不符合第一范式：

+------------+-------------------+
| workername | company      |
+------------+-------------------+
| John    | ByteDance,Tencent |
| Mike    | Tencent      |
+------------+-------------------+

上面描述的数据所表达的意思是，Mike在Tencent工作，而John同时在ByteDance和Tencent工作（假设这是可能的）。但是这种表达方式并不符合第一范式，即列的数据必须是不可分的，要满足第一范式，必须是下面的这种形式：

+------------+-----------+
| workername | company  |
+------------+-----------+
| Mike    | Tencent  |
| John    | ByteDance |
| John    | Tencent  |
+------------+-----------+

第二范式（2NF）

首先，一个数据库要满足第二范式必须要先满足第一范式。
我们先看一个表格：

+----------+-------------+-------+
| employee | department | head |
+----------+-------------+-------+
| Jones  | Accountint | Jones |
| Smith  | Engineering | Smith |
| Brown  | Accounting | Jones |
| Green  | Engineering | Smith |
+----------+-------------+-------+

这个表描述了被雇佣者，工作部门和领导的关系。这个表所表示的关系在现实生活中是完全可能存在的，现在让我们考虑一个问题，如果Brown接任Accounting部门的领导，我们需要怎样对表进行修改？这个问题将会变得非常麻烦，因为我们会发现数据都耦合在一起了，你很难找到一个很好的能唯一确定每一行的判断条件来执行你的UPDATE语句。而我们把能够唯一表示数据库中表的一行的数据成为这个表的主键。因此，没有主键的表是不符合第二范式的，也就是说符合第二范式的表需要规定主键。

因此我们为了使上面的表符合第二范式，需要将它拆分为两个表：

+----------+-------------+
| employee | department |
+----------+-------------+
| Brown  | Accounting |
| Green  | Engineering |
| Jones  | Accounting |
| Smith  | Engineering |
+----------+-------------+

+-------------+-------+
| department | head |
+-------------+-------+
| Accounting | Jones |
| Engineering | Smith |
+-------------+-------+

在这两个表中，第一个表的主键为employee，第二个表的主键为department。在这种情况下，完成上面的问题就显得非常简单了。

第三范式（3NF）

一个关系型数据库要满足第三范式必须要先满足第二范式。
将第三范式前，我们同样先看两个表：

+-----------+-------------+---------+-------+
| studentid | studentname | subject | score |
+-----------+-------------+---------+-------+
| 1     | Mike    | Math  | 96  |
| 2     | John    | Chinese | 85  |
| 3     | Kate    | History | 100  |
+-----------+-------------+---------+-------+

+-----------+-----------+-------+
| subjectid | studentid | score |
+-----------+-----------+-------+
| 101    | 1     | 96  |
| 111    | 3     | 100  |
| 201    | 2     | 85  |
+-----------+-----------+-------+

上面的两个表格的主键分别为studentid和subjectid，很显然两个表都符合第二范式。

但是我们会发现这两个表有重复冗余的数据score。因此第三范式就是要消除冗余的数据，具体到上面的情况，就是两个表只有一个能够存在score这一列数据。那么怎么将这两个表联系起来呢，这里就出现了外键。如果两个表中有冗余重复的列，而且这个表中的一个非主键列在另一个表中是主键，那么我们为了消除冗余列可以把这个非主键列作为联系两个表的桥梁，也就是外键。通过观察可以发现，studentid在第一个表中是主键，在第二个表中是非主键，所以他就是第二个表的外键。因此上述情况我们有了以下符合第三范式的写法：

+-----------+-------------+---------+
| studentid | studentname | subject |
+-----------+-------------+---------+
| 1     | Mike    | Math  |
| 2     | John    | Chinese |
| 3     | Kate    | History |
+-----------+-------------+---------+

+-----------+-----------+-------+
| subjectid | studentid | score |
+-----------+-----------+-------+
| 101    | 1     | 96  |
| 111    | 3     | 100  |
| 201    | 2     | 85  |
+-----------+-----------+-------+

可以发现在设定了外键之后，第一个表即使删除了score列，也可以通过studentid在第二个表中查找到相应的score的值，这样即消除了数据的冗余，又不会影响查找，满足第三范式。

二、范式的优点和缺点

范式的优点