MySQL建表实战总结
1.根据业务建模
这个过程中主要是梳理业务实体之间的关系，画出相应的实体图。
关系主要有三种：一对一，一对多，多对多。
实体映射成表的经验之谈
建表根据关系行为也会有所不同。
-
如果是一对一的关系，如A与B是一对一的关系，B又与其他实体没有其他关系，那么B其实完全可以作为A表的一部分。
-
如果是多对多的关系，那么往往需要建立一张中间表。
-
如果是一对多的关系，如A与B是一对多的关系,那么B表大概需要维护A表id的映射
create table B(
Aid bigint,
...其他字段
);
一对多到了代码编写环节可能会反过来变成这样：
public class A(
List<B> list;
);
区分：业务主键，数据库主键
- 数据库主键：
id bigint,加主键约束。
无论如何，记住，数据库主键是一定要存在的。
- 业务主键是否存在看具体业务。
如办公室编号这些属于业务主键，它往往可能有唯一性，但是不会加主键索引，往往也不会加唯一性索引（考虑is_delete的逻辑删除，而且也不会作为联表条件）。
业务主键不加主键索引主要在于考虑删除。
- 加主键索引的字段一般是不能改的，如果改的话会发生什么？
主键索引字段的修改实际上等于 插入+删除 的操作。
2.选择合适的数据类型
（1）字符类型
误区一： char(M) 固定占M*w个字节。（w是选择的字符集规定的每个字符最大的长度）
实际上，取决于 MySQL选用的行格式与字符集,如compact行格式与选择的字符集latin1或者utf8mb4。
- 在compact行格式下，选择的字符集如果是可变字符集utf8mb4
(可变字符集指的是：每一个字符存储占用的字节是可变的。如utf8mb4中，汉字一般三个字节，邮箱表情一般四个字节)
-
在redundant行格式下，char(M)占用的字节确实等于M*字符集规定的最大长度
误区二：varchar(M)实际占用的内存大小
-
varchar(M)实际占用的字节=L字符存储实际占用的字节+长度
-
varchar(M)的可变不止是存储的字符数量的可变，甚至还是存储数量长度的可变。
-
字符数量的可变性：
不会浪费未达到M个字符的空间，用多少就分配多少空间。
- 长度的可变性：可能占用1个字节或者2个字节。
1个字节：字符存储占用0-255bytes
2个字节：字符存储占用256-65535bytes
- 实际上，text与varchar的区分只是text不具备动态的长度存储而已。
text存储的空间=L字符存储实际占用的字节+2Bytes(固定存储2字节的长度)
-
如果是在MySQL的远古版本4.0以下，那时varchar(M),M指的就是最大字节数。
关于详细的内容，可以参考下表：
MySQL官网 storage-requirements.html#data-types-storage-reqs-strings
M表示的是字符个数
L表示字符的实际字节的长度
【实战用法】
根据需要char,varchar(),text,mediumtext都是可以使用的。
longtext一般也不太可能用吧，真这么大也不存数据库了。
- varchar(M)的M到底有什么用，既然上面都分析了varchar会动态存储字符串，长度也是根据字符串实际的占用来，那我设置M是否可以随便设置?
varchar(M)的M实际上作用有两个：
（1）M字符数量限制；
（2）作为MySQL加载数据的内存估计使用：Mysql中Varchar(50)和varchar(500)区别是什么? 掘金
MySQL加载数据会根据表的结构定义读取,M会作为每条数据内存估计的一部分，如果读取的数据量太大，预估无法一次加载进内存，就可能会分多次加载进内存，
从而处理速度就降下来了。
因此定义varchar(M)时，M尽可能在满足使用的情况下尽可能小。
- char与常见的密码长度的设计：
使用bcypt算法密码长度固定60个字符
如果md5就固定30个字符
-
JSON一般存储也会采用字符类型
text或者varchar(M),而非MySQL中的原生数据类型JSON.
（2）枚举类型&布尔值
-
实际上都应该使用
tinyint类型来完成。
不得不吐槽一下公司有看到is_delete int(1)的写法，其实完全没有必要,白白浪费了3个字节的。
-
需要特殊说明一下，tinyint(1)与tinyint(2)在内存上或者物理磁盘上占用的内存是完全一样，都是1个字节。int(1)也不会因为只使用到一个2进制位就占用更少内存，它仍然会占用4个字节。
-
实际上，数据库MySQL也有自己的enum类型，但这是不采用的。
（3）时间类型
- 需要存储完整的时间采用
datetime
timestamp一般现在不采用了，UTC 2038-01-19 03:14:07.999999就到期了。
- 我还写过timestamp时间戳转日期的算法，大家感兴趣的话也可以写一些。
timestamp当做一个Long，不考秒以下的单位。提取出有多少天+时分秒
年月日处理：
大概算法就是定义一个月份存储天数的数组。2月特殊处理（需要知道闰年平年的算法，【被4整除&&不被100整除】||【被400整除】为闰年）
时分秒无需处理：直接保留
-
如果不需要存储时分秒，只要年月日，直接使用
date。
（4）整数类型
数据库id采用bigint 等于JavaLong。
其他看需要使用int 4字节``mediumint 3字节``smallint 2字节``tinyint 1字节
（5）浮点类型
一般只有demical(M,D)是可以用的。float与double是不考虑使用的，因为会丢失精度。
（阿里手册明确禁用float,double)
M为整数位+小数位
D为小数位
0<=M<=65，
0<=D<=30
如demical(5,2)存储的范围为-999.99~999.99
从这里就可以看出来，demical底层是以字符串方式存储的。
映射为Java的BigDemical.（Java使用时需注意BigDemical的一些细节）
【其他细节说明】
1.其他类型其实一般也很少使用，上面基本满足90%以上类型设计的选择了。
前面说到一般不会采用的MySQL原生数据类型有：JSON、Enum。
2.关于【命名、索引等的其他细节】参考阿里巴巴手册就差不多了，这里重点围绕数据类型进行。
