前言对于后端开发的同学来说，访问数据库是代码中必不可少的一部分。系统收集用户的核心数据。为了安全，我们一般会存储在数据库中，比如：mysql、oracle等，后端开发的日常工作需要不断的建库建表以满足业务需求。通常，建库的频率远低于建表的频率。所以本文主要讨论建表相关的内容。如果在建表的时候不注意细节，后面系统上线后，表的维护成本会变得很高，很容易踩坑。今天和大家聊聊，建数据库表的15个小技巧，希望对大家有所帮助。1.命名创建表时，给表、字段和索引取一个好听的名字非常重要。1、见名知名。名字就像表、字段、索引的一张脸，能给人留下第一印象。一个好的名字，简洁明了，熟悉的名字，让人心情愉快，可以提高沟通和维护成本。名字不好，类比模棱两可，不可理解。而且看起来杂乱无章，让人看得抓狂。反例：用户名字段定义为：yong_hu_ming,user_name,name,user_name_123456789你可能看的时候一头雾水。这是什么风骚操作？正例：用户名字段定义为：user_name温馨提示，名字不要太长，尽量控制在30个字符以内。2、大小写的名字尽量使用小写字母，因为在视觉上，小写字母更容易让人理解。反例：字段名：PRODUCT_NAME、PRODUCT_name全部大写，看起来有点不直观。而且一部分大写，一部分小写，让人看着就更不舒服了。正例：字段名：product_name还是全部小写，看着比较舒服。3.分隔符在很多情况下，名称可能包含多个单词，以便于理解。那么，应该用什么作为多个单词之间的分隔符呢？反例：字段名：productname,productName,productname,product@name单词不分隔，或者单词用驼峰标记，或者单词之间用空格分隔，或者单词之间用@分隔，不推荐使用这些方法。正例：字段名：product_name强烈建议使用_分隔单词。4、表名表名在简洁明了的基础上，建议带上业务前缀。如果是订单相关的业务表，可以在表名前加上前缀：order_。例如：order_pay、order_pay_detail等。如果是产品相关的业务表，可以在表名前加上前缀：product_。比如：product_spu，product_sku等。这样做的好处是为了方便分类，可以非常快速的把同一个业务的表聚集在一起。另外还有一个好处，如果哪天有非订单业务，比如：金融业务，你还需要创建一个表叫pay，可以命名为：finance_pay，这样就很容易区分了。这样就不会出现同名表了。5.字段名字段名是开发者发挥空间最大的地方，但也是最容易产生混淆的地方。比如有的表用flag来表示状态，有的表用status来表示状态。可以统一使用status来表示状态。如果一个表使用了另一个表的主键，可以在另一个表的名字后面加上_id或_sys_no。比如product_sku表中有一个字段，是product_spu表的主键。这时，你可以命名为：product_spu_id或product_spu_sys_no。还有就是创建时间，可以统一成：create_time，修改时间可以统一成：update_time。删除状态固定为：delete_status。其实公共领域还是有很多的。不同的表之间，可以使用全球统一的命名规则，将它们定义为同一个名字，这样大家就可以理解了。6、索引名称在数据库中，索引有很多种，包括：主键、普通索引、唯一索引、联合索引等。每张表只有一个主键，一般命名为：id或sys_no.普通索引和联合索引其实是一种。在建立这种索引时，可以加上ix_前缀，例如：ix_product_status。唯一索引可以以ux_为前缀，例如：ux_product_code。2.字段类型在设计表格的时候，我们在字段类型的选择上有很大的发挥空间。时间格式的数据有：日期、日期时间、时间戳等可以选择。字符类型数据有：varchar、char、text等可以选择。数字类型的数据有：int、bigint、smallint、tinyint等可以选择。说实话，有很多选择有时可能是好事，也可能是坏事。如何选择合适的字段类型成为我们不得不面对的问题。如果选择的字段类型很大，比如：本来1-10之间只有10个数字，结果是bigint，占用8个字节。实际上，对于1-10之间的10个数，每个数可以用1个字节保存，所以选择tinyint比较合适。这将浪费7个字节的空间。如果字段类型选择的比较小，比如：18位的id字段，选择了int类型，最终数据会保存失败。所以选择合适的字段类型还是很重要的。可以参考以下原则：尽量选择占用存储空间少的字段类型，在满足正常业务需求的情况下，从小到大向上选择。如果字符串长度固定，或者差别不大，可以选择char类型。如果字符串长度变化很大，可以选择varchar类型。是否是字段，可以选择位类型。枚举字段，可以选择tinyint类型。主键字段，可以选择bigint类型。对于金额字段，您可以选择小数类型。时间字段，可以选择时间戳或日期时间类型。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个字节为full，比如当前数据是a=123456，会按照实际长度显示，比如：123456。但是需要注意的是有些mysql客户端即使是full也可能只显示4个字节的内容例如4字节，会显示为：1234。所以bigint(4)，其中4表示显示长度为4字节，实际长度仍占8字节。四、字段数我们在建表的时候，一定要对字段数做一些限制。以前看到有人建的表，有几十个甚至上百个字段，表中存储的数据非常大，查询效率很低。如果是这种情况，可以将一张大表拆分成多个主键相同的小表。建议每张表的字段数不要超过20个。五、主键创建表时，一定要创建主键。因为主键自带主键索引，相对于其他索引，主键索引的查询效率是最高的，因为它不需要回表。另外，主键也是一个天然的唯一索引，可以用来判断权重。在单个数据库中，可以通过AUTO_INCREMENT设置主键自动增长。但是在分布式数据库中，尤其是分库分表的业务数据库中，主键最好由外部算法生成（比如雪花算法），这样可以保证生成的id是全局唯一的。另外，主键建议保存与业务无关的值，减少业务耦合，方便以后扩展。但是，我也看到过一些一对一的表关系，比如：用户表和用户扩展表，在保存数据的时候是一对一的关系。这样用户扩展表的主键就可以直接保存用户表的主键了。6、存储引擎在mysql8之前，默认的存储引擎是myslam，在mysql8之后，默认的存储引擎变成了innodb。之前我们还在建表的时候，还在纠结选择哪个存储引擎？myslam的索引和数据分开存储，有利于查询，但是不支持事务和外键等功能。innodb虽然在查询性能上稍弱，但支持事务和外键等，功能更强大。之前的建议是：读多写少的表使用myslam存储引擎。对于写多读多的表，用innodb。但是虽然mysql不断优化innodb存储引擎的性能，但是myslam和innodb在查询性能上的差距已经越来越小了。所以建议我们在使用mysql8以后的版本时，可以直接使用默认的innodb存储引擎，不需要额外修改存储引擎。7.NOTNULL创建字段时，需要选择该字段是否允许为NULL。当我们定义一个字段时，应该尽可能明确该字段是NOTNULL。为什么？我们主要以innodb存储引擎为例，myslam存储引擎就没什么好说的了。主要原因有以下几点：在InnoDB中，需要额外的空间来存储空值，占用空间比较大。空值可能会使索引无效。null值只能通过isnull或isnotnull来判断，通过=号判断永远返回false。所以建议我们在定义字段的时候，如果可以定义为NOTNULL，就定义为NOTNULL。但是如果某个字段直接定义为NOTNULL，如果某个地方忘记给这个字段写值，数据就不会被插入。这也是一种合理的情况。但是有一个案例，系统上线了一个新功能，新增了一个字段。上线时一般先执行sql脚本，再部署代码。由于旧代码没有给新字段赋值，所以插入数据时也会报错。因此，为NOTNULL字段设置默认值是非常有必要的，尤其是后期添加的新字段。例如：altertableproduct_skuaddcolumnbrand_idint(10)notnulldefault0;8、mysql存在外键。外键存在的主要作用是保证数据的一致性和完整性。例如：创建表类（idint(10)primarykeyauto_increment,cnamevarchar(15)）；有一个类表类。然后是学生表：createtablestudent(idint(10)primarykeyauto_increment,namevarchar(15)notnull,gendervarchar(10)notnull,cidint,foreignkey(cid)referencesclass(id));其中student表中的cid字段就是保存的class表的id。这时候通过外键添加外键。这时候如果直接通过student表的id删除数据，会报异常：外键约束失效。必须先删除class表对应的cid的数据，再删除student表中的数据，这样才能保证数据的完整性。一致性和完整性。顺便说一句：只有当存储引擎是InnoDB时才能使用外键。如果只有两个表关联还好，但是如果有十几张外键关联的表，每次删除主表，都需要同步删除十几个子表。显然，性能会很差。因此，在互联网系统中，一般不建议使用外键。因为这类系统更多是出于性能考虑，不如牺牲一点数据的一致性和完整性。除了外键，不推荐使用存储过程和触发器，它们会影响性能。9、索引建表时，除了指定主键索引外，还需要创建一些常用的索引。例如：createtableproduct_sku(idint(10)primarykeyauto_increment,spu_idint(10)notnull,brand_idint(10)notnull,namevarchar(15)notnull);创建产品表时，使用spu_id（商品组表）和brand_id（品牌表）id。像这样保存其他表id的情况下，可以加普通索引：createtableproduct_sku(idint(10)primarykeyauto_increment,spu_idint(10)notnull,brand_idint(10)notnull,namevarchar(15)不为空，KEY`ix_spu_id`(`spu_id`)使用BTREE，KEY`ix_brand_id`(`brand_id`)使用BTREE)；以后查表的时候效率更高。但是不能建立太多的索引字段，可能会影响保存数据的效率，因为索引需要额外的存储空间。建议单表索引数不超过：5个，如果建表时发现索引数超过5个，可以删除一些普通索引，改成联合索引。顺便提一句：创建联合索引时需要注意最左匹配原则，否则构建的联合索引效率可能不高。对于数据重复率非常高的字段，比如status，不建议单独创建普通索引。因为即使加了索引，如果mysql发现全表扫描效率更高，也可能导致索引失效。如果你对索引失效的问题比较感兴趣，可以看看我的另一篇文章《聊聊索引失效的10种场景，太坑了》，里面有很详细的介绍。10.时间字段时间字段的种类还是有很多的，我们可以选择。目前mysql支持：date、datetime、timestamp、varchar等，varchar类型可以和接口保持一致，接口中时间类型为String。但是如果哪天我们要按时间范围查询数据，效率就会很低，因为这样的话，我们就无法通过索引了。日期类型主要用于保存日期，如：2020-08-20，不适合保存日期和时间，如：2020-08-2012:12:20。datetime和timestamp类型更适合我们保存日期和时间。但它们略有不同。timestamp：4个字节用于存储数据，取值范围为1970-01-0100:00:01UTC~2038-01-1903:14:07。此外，它与时区有关。datetime：8个字节用于保存数据，取值范围为1000-01-0100:00:00~9999-12-3123:59:59。它与时区无关。推荐使用datetime类型保存日期时间，可以保存的时间范围更大。温馨提示，在设置时间字段默认值的时候，建议不要设置为：0000-00-0000:00:00，否则查询表时可能会直接报错无法转换.11、金额字段mysql中有多个字段可以表示浮点数：float、double、decimal等，但是float和double可能会丢失精度，所以建议大家使用decimal类型来保存金额。一般我们这样定义浮点数：decimal(m,n)。其中n是指小数的长度，m是指整数加上小数的总长度。如果我们定义的金额类型是这样的：decimal(10,2)，表示整数长度为8位，保留2位小数。十二、唯一索引唯一索引在我们的实际工作中，使用频率是相当高的。您可以为单个字段添加唯一索引，例如：组织代码。也可以为多个字段添加联合唯一索引，如：分类号、单位、规格等。单个唯一索引还可以，但是如果是联合唯一索引，当字段值为null时，唯一约束可能会失效。创建唯一索引时，相关字段不能包含空值，否则唯一性失效。十三、字符集mysql支持的字符集很多，常用的有：latin1、utf-8、utf8mb4、GBK等。这四种字符集如下：latin1容易出现乱码，在实际项目中很少使用。GBK支持中文，但不支持国际字符，在实际项目中用得不多。从目前来看，mysql使用最多的字符集是：utf-8和utf8mb4。其中utf-8占用3个字节，比utf8mb4的4个字节占用存储空间少。但是utf-8有一个问题：它不能存储emoji表情，因为emoji表情一般需要4个字节。所以使用utf-8字符集保存emoji表情时，数据库会直接报错。所以建表时建议将字符集设置为：utf8mb4，这样会省去很多不必要的麻烦。14.不知道排序规则。你注意了吗？在mysql建表的时候，有一个COLLATE参数可以设置。示例：CREATETABLE`order`(`id`bigintNOTNULLAUTO_INCREMENT,`code`varchar(20)COLLATEutf8mb4_binNOTNULL,`name`varchar(30)COLLATEutf8mb4_binNOTNULL,PRIMARYKEY(`id`),UNIQUEKEY`un_code`(`code`),KEY`un_code_name`(`code`,`name`)USINGBTREE,KEY`idx_name`(`name`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_bin它是用于设置排序规则。字符排序规则与字符集有关。例如字符集为utf8mb4，则字符排序规则也以：utf8mb4_开头。常用的有：utf8mb4_general_ci、utf8mb4_bin等，其中utf8mb4_general_cicollat??ion对字母大小写不敏感。说得直白一点，就是不区分大小写。utf8mb4_bin排序规则是case-sensitive，即不区分大小写。说实话，这个还是很重要的。如果order表中有一条记录，name的值是大写的YOYO，但是我们用小写的yoyo来查，例如：select*fromorderwherename='yoyo';如果字符排序规则是utf8mb4_general_ci，可以查到大写YOYO的数据。如果字符排序规则为utf8mb4_bin，则查不到。所以一定要根据实际业务场景来选择字符排序规则，否则很容易出问题。15、大字段我们在创建表的时候，需要格外注意一些特殊的字段，比如：大字段，也就是占用存储空间比较多的字段。比如：用户评论，这是一个很大的字段，但是这个字段可长可短。但是，评论的总长度一般是有限制的，例如：最多允许500个字符。如果直接定义为text类型，可能会浪费存储空间，所以建议将此类字段定义为varchar类型，存储效率更高。当然，我也见过直接保存合约数据的更大的字段。一个合约可能占用数Mb。将这种数据保存在mysql中，从系统设计的角度来说，本身就是不合理的。合同等非常大的数据可以保存在mongodb中，然后在mysql的业务表中，保存mongodb表的id。
