对于后端开发同学来说，访问数据库，是代码中必不可少的一个环节。系统中收集到用户的核心数据，为了安全性，我们一般会存储到数据库，比如： MySQL，Oracle 等。后端开发的日常工作，需要不断的建库和建表，来满足业务需求。通常情况下，建库的频率比建表要低很多，所以，我们这篇文章主要讨论建表相关的内容。如果我们在建表的时候不注意细节，等后面系统上线之后，表的维护成本变得非常高，而且很容易踩坑。今天就跟大家一起聊聊，数据库建表的 18个小技巧。文章中介绍的很多细节，我在工作中踩过坑，并且实践过的，非常有借鉴意义，希望对你会有所帮助。
1.名字
建表的时候，给表、字段和索引起个好名字，真的太重要了。1.1 见名知意
名字就像表、字段和索引的一张脸，可以给人留下第一印象。好的名字，言简意赅，见名知意，让人心情愉悦，能够提高沟通和维护成本。坏的名字，模拟两可，不知所云。而且显得杂乱无章，看得让人抓狂。反例：
用户名称字段定义成：yong_hu_ming、用户_name、name、user_name_123456789
你看了可能会一脸懵逼，这是什么骚操作？
正例：
用户名称字段定义成：user_name
温馨提醒一下，名字也不宜过长，尽量控制在30个字符以内。
1.2 大小写
名字尽量都用小写字母，因为从视觉上，小写字母更容易让人读懂。字段名：PRODUCT_NAME、PRODUCT_name
全部大写，看起来有点不太直观。而一部分大写，一部分小写，让人看着更不爽。1.3 分隔符
很多时候，名字为了让人好理解，有可能会包含多个单词。字段名：productname、productName、product name、product@name
单词间没有分隔，或者单词间用驼峰标识，或者单词间用空格分隔，或者单词间用@分隔，这几种方式都不太建议。强烈建议大家在单词间用_分隔。
1.4 表名
对于表名，在言简意赅，见名知意的基础之上，建议带上业务前缀。如果是订单相关的业务表，可以在表名前面加个前缀：order_。例如：order_pay、order_pay_detail 等。如果是商品相关的业务表，可以在表名前面加个前缀：product_。例如：product_spu，product_sku 等。这样做的好处是为了方便归类，把相同业务的表，可以非常快速的聚集到一起。另外，还有有个好处是，如果哪天有非订单的业务，比如：金融业务，也需要建一个名字叫做 pay 的表，可以取名：finance_pay，就能非常轻松的区分。这样就不会出现同名表的情况。
1.5 字段名称
字段名称是开发人员发挥空间最大，但也最容易发生混乱的地方。比如有些表，使用 flag 表示状态，另外的表用 status 表示状态。如果一个表使用了另一个表的主键，可以在另一张表的名后面，加 _id 或 _sys_no，例如：在 product_sku 表中有个字段，是product_spu表的主键，这时候可以取名：product_spu_id 或 product_spu_sys_no。还有创建时间，可以统一成：create_time，修改时间统一成：update_time。其实还有很多公共字段，在不同的表之间，可以使用全局统一的命名规则，定义成相同的名称，以便于大家好理解。
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 更为合适。如果字段类型择小了，比如：一个 18 位的 id 字段，选择了 int 类型，最终数据会保存失败。所以选择一个合适的字段类型，还是非常重要的一件事情。
以下原则可以参考一下：
- 尽可能选择占用存储空间小的字段类型，在满足正常业务需求的情况下，从小到大，往上选。
- 如果字符串长度固定，或者差别不大，可以选择char类型。如果字符串长度差别较大，可以选择 varchar 类型。
- 时间字段，可以选择 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.字段个数
我之前见过有人创建的表，有几十个，甚至上百个字段，表中保存的数据非常大，查询效率很低。如果真有这种情况，可以将一张大表拆成多张小表，这几张表的主键相同。5. 主键
因为主键自带了主键索引，相比于其他索引，主键索引的查询效率最高，因为它不需要回表。在单个数据库中，主键可以通过 AUTO_INCREMENT，设置成自动增长的。但在分布式数据库中，特别是做了分库分表的业务库中，主键最好由外部算法(比如：雪花算法）生成，它能够保证生成的id是全局唯一的。除此之外，主键建议保存跟业务无关的值，减少业务耦合性，方便今后的扩展。不过我也见过，有些一对一的表关系，比如：用户表和用户扩展表，在保存数据时是一对一的关系。这样，用户扩展表的主键，可以直接保存用户表的主键。
6.存储引擎
在 MySQL 8 以前的版本，默认的存储引擎是 myisam，而 MySQL 8 以后的版本，默认的存储引擎变成了 innodb。之前我们还在创建表时，还一直纠结要选哪种存储引擎？myisam 的索引和数据分开存储，而有利于查询，但它不支持事务和外键等功能。而 innodb 虽说查询性能，稍微弱一点，但它支持事务和外键等，功能更强大一些。以前的建议是：读多写少的表，用 myisam 存储引擎。而写多读多的表，用 innodb。
但虽说 MySQL 对 innodb 存储引擎性能的不断优化，现在 myisam 和 innodb 查询性能相差已经越来越小。所以，建议我们在使用 MySQL 8 以后的版本时，直接使用默认的 innodb 存储引擎即可，无需额外修改存储引擎。7. NOT NULL
在创建字段时，需要选择该字段是否允许为 NULL。我们在定义字段时，应该尽可能明确该字段 NOT NULL。
为什么呢？
我们主要以 innodb 存储引擎为例，myisam 存储引擎没啥好说的。
主要有以下原因：
- 在 innodb 中，需要额外的空间存储 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. 外键
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:01 UTC ~ 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. json 字段
我们在设计表结构时，经常会遇到某个字段保存的数据值不固定的需求。举个例子，比如：做异步 Excel 导出功能时，需要在异步任务表中加一个字段，保存用户通过前端页面选择的查询条件，每个用户的查询条件可能都不一样。这时候就可以使用 MySQL 的 json 字段类型了，可以保存 JSON 格式的结构化数据。MySQL 还支持按字段名称或者字段值，查询 json 中的数据。
13.唯一索引
你可以给单个字段，加唯一索引，比如：组织机构 code。也可以给多个字段，加一个联合的唯一索引，比如：分类编号、单位、规格等。单个的唯一索引还好，但如果是联合的唯一索引，字段值出现null时，则唯一性约束可能会失效。创建唯一索引时，相关字段一定不能包含 null 值，否则唯一性会失效。
14. 字符集
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，会省去很多不必要的麻烦。15. 排序规则
不知道，你关注过没，在 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
