前言
对于后端开发同学来说，拜访数据库，是代码中必不可少的一个环节。
零碎中收集到用户的外围数据，为了安全性，咱们个别会存储到数据库，比方：mysql，oracle 等。
后端开发的日常工作，须要一直的建库和建表，来满足业务需要。
通常状况下，建库的频率比建表要低很多，所以，咱们这篇文章次要探讨建表相干的内容。
如果咱们在建表的时候不留神细节，等前面零碎上线之后，表的保护老本变得十分高，而且很容易踩坑。
明天就跟大家一起聊聊，数据库建表的 15 个小技巧，心愿对你会有所帮忙
1. 名字
建表的时候，给 表、字段 和索引 起个好名字，真的太重要了。
1.1 见名知意
名字就像 表、字段 和索引 的一张脸，能够给人留下第一印象。
好的名字，长篇累牍，见名知意，让人情绪愉悦，可能进步沟通和保护老本。
坏的名字，模仿两可，不知所云。而且显得横七竖八，看得让人抓狂。
反例：
用户名称字段定义成：yong_hu_ming、用户_name、name、user_name_123456789你看了可能会一脸懵逼，这是什么骚操作？
正例：
用户名称字段定义成：user_name舒适揭示一下，名字也不宜过长，尽量管制在
30个字符以内。
1.2 大小写
名字尽量都用 小写字母，因为从视觉上，小写字母更容易让人读懂。
反例：
字段名：PRODUCT_NAME、PRODUCT_name全副大写，看起来有点不太直观。而一部分大写，一部分小写，让人看着更不爽。
正例：
字段名：product_name名字还是应用全小写字母，看着更难受。
1.3 分隔符
很多时候，名字为了让人好了解，有可能会蕴含多个单词。
那么，多个单词间的 分隔符 该用什么呢？
反例：
字段名：productname、productName、product name、product@name单词间没有分隔，或者单词间用驼峰标识，或者单词间用空格分隔，或者单词间用 @分隔，这几种形式都不太倡议。
正例：
字段名：product_name强烈建议大家在单词间用 _ 分隔。
1.4 表名
对于表名，在长篇累牍，见名知意的根底之上，倡议带上 业务前缀。
如果是订单相干的业务表，能够在表名后面加个前缀：order_。
例如：order_pay、order_pay_detail 等。
如果是商品相干的业务表，能够在表名后面加个前缀：product_。
例如：product_spu，product_sku 等。
这样做的益处是为了不便归类，把雷同业务的表，能够十分疾速的汇集到一起。
另外，还有有个益处是，如果哪天有非订单的业务，比方：金融业务，也须要建一个名字叫做 pay 的表，能够取名：finance_pay，就能十分轻松的辨别。
这样就不会呈现 同名表 的状况。
1.5 字段名称
字段名称 是开发人员施展空间最大，但也最容易产生凌乱的中央。
比方有些表，应用 flag 示意状态，另外的表用 status 示意状态。
能够对立一下，应用 status 示意状态。
如果一个表应用了另一个表的主键，能够在另一张表的名前面，加 _id 或_sys_no，例如：
在 product_sku 表中有个字段，是 product_spu 表的主键，这时候能够取名：product_spu_id 或 product_spu_sys_no。
还有创立工夫，能够对立成：create_time，批改工夫对立成：update_time。
删除状态固定为：delete_status。
其实还有很多公共字段，在不同的表之间，能够应用全局对立的命名规定，定义成雷同的名称，以便于大家好了解。
1.6 索引名
在数据库中，索引有很多种，包含：主键、一般索引、惟一索引、联结索引等。
每张表的主键只有一个，个别应用：id或者 sys_no 命名。
一般索引和联结索引，其实是一类。在建设该类索引时，能够加 ix_ 前缀，比方：ix_product_status。
惟一索引，能够加 ux_ 前缀，比方：ux_product_code。
2. 字段类型
在设计表时，咱们在抉择 字段类型 时，可施展空间很大。
工夫格局的数据有：date、datetime 和 timestamp 等等能够抉择。
字符类型的数据有：varchar、char、text 等能够抉择。
数字类型的数据有：int、bigint、smallint、tinyint 等能够抉择。
说实话，抉择很多，有时候是一件坏事，也可能是一件好事。
如何抉择一个 适合 的字段类型，变成了咱们不得不面对的问题。
如果字段类型选大了，比方：本来只有 1 -10 之间的 10 个数字，后果选了 bigint，它占8 个字节。
其实，1-10 之间的 10 个数字，每个数字 1 个字节就能保留，抉择 tinyint 更为适合。
这样会白白浪费 7 个字节的空间。
如果字段类型择小了，比方：一个 18 位的 id 字段，抉择了 int 类型，最终数据会保留失败。
所以抉择一个适合的字段类型，还是十分重要的一件事件。
以下准则能够参考一下：
- 尽可能抉择占用存储空间小的字段类型，在满足失常业务需要的状况下，从小到大，往上选。
- 如果字符串长度固定，或者差异不大，能够抉择 char 类型。如果字符串长度差异较大，能够抉择 varchar 类型。
- 是否字段，能够抉择 bit 类型。
- 枚举字段，能够抉择 tinyint 类型。
- 主键字段，能够抉择 bigint 类型。
- 金额字段，能够抉择 decimal 类型。
- 工夫字段，能够抉择 timestamp 或 datetime 类型。
3. 字段长度
后面咱们曾经定义好了 字段名称 ，抉择了适合的 字段类型 ，接下来，须要重点关注的是 字段长度 了。
比方：varchar(20)，biginit(20)等。
那么问题来了，varchar代表的是 字节 长度，还是 字符 长度呢？
答：在 mysql 中除了 varchar 和char是代表 字符 长度之外，其余的类型都是代表 字节 长度。
biginit(n) 这个 n 示意什么意思呢？
如果咱们定义的字段类型和长度是：bigint(4)，bigint 理论长度是 8 个字节。
当初有个数据 a =1，a 显示 4 个字节，所以在不满 4 个字节时后面填充 0（前提是该字段设置了 zerofill 属性），比方：0001。
当满了 4 个字节时，比方当初数据是 a =123456，它会依照理论的长度显示，比方：123456。
但须要留神的是，有些 mysql 客户端即便满了 4 个字节，也可能只显示 4 个字节的内容，比方会显示成：1234。
所以 bigint(4)，这里的 4 示意显示的长度为 4 个字节，理论长度还是占 8 个字节。
4. 字段个数
咱们在建表的时候，肯定要对 字段个数 做一些限度。
我之前见过有人创立的表，有几十个，甚至上百个字段，表中保留的数据十分大，查问效率很低。
如果真有这种状况，能够将一张 大表 拆成多张 小表，这几张表的主键雷同。
倡议每表的字段个数，不要超过 20 个。
5. 主键
在创立表时，肯定要创立 主键。
因为主键自带了主键索引，相比于其余索引，主键索引的查问效率最高，因为它不须要回表。
此外，主键还是人造的 惟一索引，能够依据它来判重。
在 单个 数据库中，主键能够通过 AUTO_INCREMENT，设置成 主动增长 的。
但在 分布式 数据库中，特地是做了分库分表的业务库中，主键最好由内部算法(比方：雪花算法）生成，它可能保障生成的 id 是全局惟一的。
除此之外，主键倡议保留跟业务无关的值，缩小业务耦合性，不便今后的扩大。
不过我也见过，有些一对一的表关系，比方：用户表和用户扩大表，在保留数据时是一对一的关系。
这样，用户扩大表的主键，能够间接保留用户表的主键。
6. 存储引擎
在 mysql5.1 以前的版本，默认的存储引擎是 myslam，而mysql5.1 当前的版本，默认的存储引擎变成了innodb。
之前咱们还在创立表时，还始终纠结要选哪种存储引擎？
myslam的索引和数据离开存储，而有利于查问，但它不反对事务和外键等性能。
而 innodb 虽说查问性能，稍强劲一点，但它反对事务和外键等，性能更弱小一些。
以前的倡议是：读多写少的表，用 myslam 存储引擎。而写多读多的表，用 innodb。
但虽说 mysql 对 innodb 存储引擎性能的一直优化，当初 myslam 和 innodb 查问性能相差曾经越来越小。
所以，倡议咱们在应用 mysql8 当前的版本时，间接应用默认的 innodb 存储引擎即可，无需额定批改存储引擎。
7. NOT NULL
在创立字段时，须要抉择该字段是否容许为NULL。
咱们在定义字段时，应该尽可能明确该字段NOT NULL。
为什么呢？
咱们次要以 innodb 存储引擎为例，myslam 存储引擎没啥好说的。
次要有以下起因：
- 在 innodb 中，须要额定的空间存储 null 值，须要占用更多的空间。
- null 值可能会导致索引生效。
- null 值只能用
is null或者is not null判断，用= 号判断永远返回 false。
因而，倡议咱们在定义字段时，能定义成 NOT NULL，就定义成 NOT NULL。
但如果某个字段间接定义成 NOT NULL，万一有些中央忘了给该字段写值，就会 insert 不了数据。
这也算正当的状况。
但有一种状况是，零碎有新性能上线，新增了字段。上线时个别会先执行 sql 脚本，再部署代码。
因为老代码中，不会给新字段赋值，则 insert 数据时，也会报错。
由此，十分有必要给 NOT NULL 的字段设置默认值，特地是前面新增的字段。
例如：
alter table product_sku add column brand_id int(10) not null default 0;
8. 外键
在 mysql 中，是存在 外键 的。
外键存在的次要作用是：保证数据的 一致性 和完整性。
例如：
create table class (id int(10) primary key auto_increment,
cname varchar(15)
);有个班级表 class。
而后有个 student 表：
create table student(id int(10) primary key auto_increment,
name varchar(15) not null,
gender varchar(10) not null,
cid int,
foreign key(cid) references class(id)
);其中 student 表中的 cid 字段，保留的 class 表的 id，这时通过 foreign key 减少了一个外键。
这时，如果你间接通过 student 表的 id 删除数据，会报异样：
a foreign key constraint fails必须要先删除 class 表对于的 cid 那条数据，再删除 student 表的数据才行，这样可能保证数据的一致性和完整性。
顺便说一句：只有存储引擎是 innodb 时，能力应用外键。
如果只有两张表的关联还好，但如果有十几张表都建了外键关联，每删除一次主表，都须要同步删除十几张子表，很显然性能会十分差。
因而，互联网零碎中，个别倡议不应用外键。因为这类零碎更多的是为了性能思考，宁肯就义一点数据一致性和完整性。
除了 外键 之外，存储过程 和触发器 也不太倡议应用，他们都会影响性能。
9. 索引
在建表时，除了指定 主键索引 之外，还须要创立一些 一般索引。
例如：
create table product_sku(id int(10) primary key auto_increment,
spu_id int(10) not null,
brand_id int(10) not null,
name varchar(15) not null
);在创立商品表时，应用 spu_id（商品组表）和 brand_id（品牌表）的 id。
像这类保留其余表 id 的状况，能够减少一般索引：
create table product_sku (id int(10) primary key auto_increment,
spu_id int(10) not null,
brand_id int(10) not null,
name varchar(15) not null,
KEY `ix_spu_id` (`spu_id`) USING BTREE,
KEY `ix_brand_id` (`brand_id`) USING BTREE
);前面查表的时候，效率更高。
但索引字段也不能建的太多，可能会影响保留数据的效率，因为索引须要额定的存储空间。
倡议单表的索引个数不要超过：5个。
如果在建表时，发现索引个数超过 5 个了，能够删除局部 一般索引 ，改成 联结索引。
顺便说一句：在创立联结索引的时候，须要应用留神 最左匹配准则，不然，建的联结索引效率可能不高。
对于数据反复率十分高的字段，比方：状态，不倡议独自创立一般索引。因为即便加了索引，如果 mysql 发现 全表扫描 效率更高，可能会导致索引生效。
10. 工夫字段
工夫字段 的类型，咱们能够抉择的范畴还是比拟多的，目前 mysql 反对：date、datetime、timestamp、varchar 等。
varchar类型可能是为了跟接口保持一致，接口中的工夫类型是 String。
但如果哪天咱们要通过工夫范畴查问数据，效率会非常低，因为这种状况没法走索引。
date类型次要是为了保留 日期 ，比方：2020-08-20，不适宜保留 日期和工夫，比方：2020-08-20 12:12:20。
而 datetime 和timestamp类型更适宜咱们保留 日期和工夫。
但它们有稍微区别。
timestamp：用 4 个字节来保留数据，它的取值范畴为1970-01-01 00:00:01UTC ~2038-01-19 03:14:07。此外，它还跟时区无关。datetime：用 8 个字节来保留数据，它的取值范畴为1000-01-01 00:00:00~9999-12-31 23:59:59。它跟时区无关。
优先举荐应用 datetime 类型保留日期和工夫，能够保留的工夫范畴更大一些。
舒适揭示一下，在给工夫字段设置默认值是，倡议不要设置成：
0000-00-00 00:00:00，不然查问表时可能会因为转换不了，而间接报错。
11. 金额字段
mysql 中有多个字段能够示意浮点数：float、double、decimal 等。
而 float 和double可能会失落精度，因而举荐大家应用 decimal 类型保留金额。
个别咱们是这样定义浮点数的：decimal(m,n)。
其中 n 是指 小数 的长度，而 m 是指 整数加小数 的总长度。
如果咱们定义的金额类型是这样的：decimal(10,2)，则示意整数长度是 8 位，并且保留 2 位小数。
12. 惟一索引
惟一索引 在咱们理论工作中，应用频率相当高。
你能够给单个字段，加惟一索引，比方：组织机构 code。
也能够给多个字段，加一个联结的惟一索引，比方：分类编号、单位、规格等。
单个的惟一索引还好，但如果是联结的惟一索引，字段值呈现 null 时，则唯一性束缚可能会生效。
创立惟一索引时，相干字段肯定不能蕴含 null 值，否则唯一性会生效。
13. 字符集
mysql 中反对的 字符集 有很多，罕用的有：latin1、utf-8、utf8mb4、GBK 等。
这 4 种字符集状况如下：
latin1容易呈现乱码问题，在理论我的项目中应用比拟少。
而 GBK 反对中文，但不反对国内通用字符，在理论我的项目中应用也不多。
从目前来看，mysql 的字符集应用最多的还是：utf-8和utf8mb4。
其中 utf-8 占用 3 个字节，比 utf8mb4 的 4 个字节，占用更小的存储空间。
但 utf- 8 有个问题：即无奈存储 emoji 表情，因为 emoji 表情个别须要 4 个字节。
由此，应用 utf- 8 字符集，保留 emoji 表情时，数据库会间接报错。
所以，倡议在建表时字符集设置成：utf8mb4，会省去很多不必要的麻烦。
14. 排序规定
不晓得，你关注过没，在 mysql 中创立表时，有个 COLLATE 参数能够设置。
例如：
CREATE TABLE `order` (
`id` bigint NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `un_code` (`code`),
KEY `un_code_name` (`code`,`name`) USING BTREE,
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin它是用来设置 排序规定 的。
字符排序规定跟字符集无关，比方：字符集如果是 utf8mb4，则字符排序规定也是以：utf8mb4_ 结尾的，罕用的有：utf8mb4_general_ci、utf8mb4_bin等。
其中 utf8mb4_general_ci 排序规定，对字母的大小写不敏感。说得更直白一点，就是不辨别大小写。
而 utf8mb4_bin 排序规定，对字符大小写敏感，也就是辨别大小写。
说实话，这一点还是十分重要的。
如果 order 表中当初有一条记录，name 的值是大写的 YOYO，但咱们用小写的 yoyo 去查，例如：
select * from order where name='yoyo';如果字符排序规定是 utf8mb4_general_ci，则能够查出大写的 YOYO 的那条数据。
如果字符排序规定是 utf8mb4_bin，则查不进去。
由此，字符排序规定肯定要依据理论的业务场景抉择，否则容易呈现问题。
15. 大字段
咱们在创立表时，对一些非凡字段，要额定关注，比方：大字段，即占用较多存储空间的字段。
比方：用户的评论，这就属于一个大字段，但这个字段可长可短。
但个别会对评论的总长度做限度，比方：最多容许输出 500 个字符。
如果间接定义成 text 类型，可能会节约存储空间，所以倡议将这类字段定义成 varchar 类型的存储效率更高。
当然，我还见过更大的字段，即该字段间接保留合同数据。
一个合同可能会占 几 Mb。
在 mysql 中保留这种数据，从零碎设计的角度来说，自身就不太正当。
像合同这种十分大的数据，能够保留到 mongodb 中，而后在 mysql 的业务表中，保留 mongodb 表的 id。