数据库基础系列：范式&MySQL数据格式

1 简介

1.1 范式

范式(Normal Form, NF)是关系数据库理论的基础,设计数据库结构过程中遵循的规则和指导方法.常用范式有三种,即1NF,2NF和3NF。

1.2 三种范式

序号	范式	描述
1	第一范式	列的原子性，即列为原子性、不可再拆分的基本数据格式，如整型、浮点型、字符串、布尔型，不是这些数据类型组合形式，如数组、集合等可拆分的数据形式。
2	第二范式	满足第一范式，包含主键，非主键列完全依赖主键，第二范式中的非主键数据都有唯一的主键标识，因此要求主键是不可重复的。
3	第三范式	第二范式的子集，第三范式避免冗余数据，即共用的非主键数据，为单独的表，不在其他表中重复出现。

2 Usage

以MySQL关系型数据库为例，介绍三种范式。

2.1 MySQL数据类型

• 字符类型

序号	数据类型	字符数	描述
1	CHAR(size)	2^8-1	存储固定长度的字符串（字母、数字及特殊字符），size为指定的长度，最多存储255个字符
2	VARCHAR(size)	2^8-1	存储可变长度的字符串（字母、数字及特殊字符），size为指定的长度，最多存储255个字符，若长度大于255则转换为TEXT类型
3	TINYTEXT	2^8-1	最多存储255个字符
4	TEXT	2^16-1	存储大型字符串，最多存储65535个字符
5	MEDIUMTEXT	2^24-1	最多存储1677215个字符
6	LONGTEXT	2^32-1	最多存储4294967295个字符
7	BINARY	2^8-1	存储二进制数据，而不是普通字符串,最大字符数表示存储的字节数，存储数据时，会在数据尾部追加0x00以保证长度为指定长度
8	VARBINARY	2^8-1	存储二进制数据

• 列表类型

序号	数据类型	值个数	描述
1	ENUM(x,y,z,etc.)	65535	ENUM列表中最多可列出65535个值
2	SET	64	最多只能包含64个列表项

• 大容量

序号	数据类型	字节数	描述
1	BLOB	2^16-1	存储大型二进制数据最多存储65535字节的数据（Binary Large OBjects）
2	MEDIUMBLOB	2^24-1	最多存储16777215字节的数据
3	LONGBLOB	2^32-1	最多存储4294967295字节的数据

• 数字类型

序号	数据类型	字节数	描述
1	TINYINT(size)	1	size为存储数据的最大位数，如1个字节，共有8位，存储数据范围无符号：[0,$2^8$-1]，有符号：[$-2^7-1$,$2^7-1$]，若size=3则最大存储数据范围：[0,$2^3$-1]和[$-2^2-1$,$2^2-1$]
2	SMALLINT(size)	2	存储最大数据16位，数据范围[0,2^16-1] [ -2^15 -1,2^15 -1]
3	MEDIUMINT(size)	3	最大存储数据24位，数据范围[0,2^24-1] [ -2^23 -1,2^23 -1]
4	INT(size)	4	最大存储数据32位，数据范围[0,2^32-1] [ -2^31 -1,2^31 -1]
5	BIGINT(size)	8	最大存储数据64位，数据范围[0,2^64-1] [ -2^63 -1,2^63 -1]
6	FLOAT(size,d)	4	最大存储数据32位，数据范围[0,2^32-1] [ -2^31 -1,2^31 -1]
7	DOUBLE(size,d)	8	最大存储数据64位，数据范围[0,2^64-1] [ -2^63 -1,2^63 -1]
8	DECIMAL(size,d)	/	最大存储数据65位，数据范围[0,2^65-1] [ -2^64 -1,2^64 -1]

• Date类型

序号	数据类型	描述
1	DATE()	日期，格式YYYY-MM-DD
2	DATETIME()	日期和时间，格式YYYY-MM-DD HH:MM:SS
3	TIMESTAMP()	时间戳，使用Unix纪元（1970-01-01 00:00:00)，格式：YYYY-MM-DD HH:MM:SS
4	TIME()	时间，格式HH:MM:SS
5	YEAR()	2位或4位格式的年

• Unicode类型

序号	数据类型	描述
1	NCHAR(size)	存储固定长度的Unicode字符串
2	NVARCHAR(size)	存储可变长度的Unicode字符串
3	NTEXT	存储大型Unicode字符串

• 位类型

序号	数据类型	描述
1	BIT(size)	存储基于位的数据，size指定位数，存储空间单位为字节，计算方式：(size+7)/8个字节
2	bool	存储逻辑值，只有两种状态，TRUE和FALSE，或0和1

2.2 说明

• VARCHAR
  可变长度，存储数据时按需分配，按字节存储，比较节省空间，保存数据，同时记录字符串的实际长度，因此，占用的存储空间大多数情况下是小于指定长度的，若插入的字符长度超过指定字符串长度，MySQL会自动截断字符串，当被截掉的字符串不是空格时，MySQL会进行告警提示，在strict模式下，会报错。
• TEXT
  存储大型字符串。
• BLOB
  存储大型二进制数据。
• NCHAR
  按字符存储数据，若存储内容含有中文，使用nvarchar会减少出现乱码的几率。

2.3 数据库

以学校为单位，设计师生相关的数据库。

• 学校信息表

id	UUID	校名	校训
1	0001	学校1	校训1
2	0002	学校2	校训2

• 学生信息表

id	UUID	姓名	年级	所属学校
1	s001	学生1	年级1	0001
2	s002	学生2	年级2	0001

• 教师信息表

id	UUID	姓名	年级	所属学校
1	t001	教师1	年级1	0001
2	t002	教师2	年级2	0001

2.4 范式对应关系

• 第一范式
  每个表的列均不可再拆分为其他自然信息。
• 第二范式
  每个表均有主键id。
• 第三范式
  学生和教师共用学校信息，因此各自表中只有学校的UUID即可，不存储学校其他自然信息。

3 小结

范式仅作为设计数据库表时的一种参考，实际应用中需要按需设计，不应仅局限于范式，MySQL中，即使不满足第三范式，仍可运行，即，出现重复的数据仍可正常工作。

---

【参考文献】
[1]https://wenku.baidu.com/view/71f1d60dbb68a98271fefa10.html
[2]https://wenku.baidu.com/view/3e445b8d48649b6648d7c1c708a1284ac950054e.html?from=search
[3]https://wenku.baidu.com/view/19c9ffe4d0f34693daef5ef7ba0d4a7302766c24.html?from=search
[4]https://blog.csdn.net/Xin_101/article/details/85247567
[5]https://www.cnblogs.com/Jashinck/p/8384388.html
[6]https://wenku.baidu.com/view/0b20617e68dc5022aaea998fcc22bcd127ff42f9.html?from=search