大厂设计规范

强制

建表

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsignedtinyint（1 表示是，0 表示否）。
说明：任何字段如果为非负数，必须是 unsigned。
注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在设置从 is_xxx到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围。
正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表
名、字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【强制】表名不使用复数名词。
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。
【强制】小数类型为 decimal，禁止使用 float 和 double。
说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的
结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
【强制】varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长
度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索
引效率。
【强制】表必备三字段：id, create_time, update_time。
说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time
的类型均为 datetime 类型。

索引

【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达
90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

语句

【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinctcol1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。
正例：使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;
【强制】使用 ISNULL()来判断是否为 NULL 值。
说明：NULL 与任何值的直接比较都为 NULL。
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。
【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】数据订正（特别是删除、修改记录操作）时，要先 select，避免出现误删除，确认无
误才能执行更新语句。

ORM映射

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络消耗，尤其是 text 类型的字段。
【强制】POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性之间的映射。
说明：参见定义 POJO 类以及数据库字段定义规定，在中增加映射，是必须的。在
MyBatis Generator 生成的代码中，需要进行对应的修改。
【强制】不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每一个表也必然有一个 POJO 类与之对应。
说明：配置映射关系，使字段与 DO 类解耦，方便维护。
【强制】sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入。
【强制】iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
说明：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取
start,size 的子集合。
正例：Map<String, Object> map = new HashMap<>(); map.put("start", start); map.put("size", size);
【强制】不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。
【强制】更新数据表记录时，必须同时更新记录对应的 gmt_modified 字段值为当前时间。

其他设计思路

关于varchar的长度

关于varchar的长度，一般只需要大概设计，而不会设计得非常具体，更加不会设计得“刚刚好”。

以“用户名”为例，当前项目的业务规则可能是“长度必须4-8字符”，但是，以后出现新的需求时，可能将规则改为“长度必须6-12字符”，如果一开始设计为varchar(8)则需要改为varchar(12)，而且，随着项目的继续运营，业务规则仍可能再次改变！

数据库只是用于读写数据的载体，不应该体现业务规则，而且，调整数据库（或数据表）设计的风险太大，所以，一般把varchar的长度设计为“肯定满足当前业务规则，并且，即使以后调整业务规则，当前设计也是满足的”，简单来说，“设计为充裕的长度”。

当然，也不要设计得特别大，utf8mb4编码为例，理论的最大长度是16383，那么，把所有的varchar长度都设置为非常大的值（与表的字段数量也有关系，不可以多个varchar字段的长度都设置为16383）是不会出错的，但是，特别大的值可能会导致一些歧义，例如把“用户名”的长度设置为500是不合适的，因为，无论业务规则怎么调整，都不会出现长达500长度的用户名。

在本项目中，大概划分了几个档次的长度，例如50、255、2500等（你也可以自行添加更多档次），较短的字段使用varchar(50)，例如“用户名”、“昵称”，略长的使用varchar(255)，例如“商品标题”、“商品关键词”等，更长的且不足以使用text的就使用varchar(2500)，例如“商品的规格参数集合”等。

总的来说，数据表的设计不会精准对应业务规则（例如用户名长度最多12字符），业务规则应该通过程序来实现。

关于not null的约定

关于not null，一般不推荐设置，使用default null（或不设置，亦等效）即可。

这样的设计思路与以上varchar的长度也大致相同：很难确定以后是否会出现新的运营模式或业务规则。

例如在早期，“用户”的“用户名”和“密码”就是必要的数据，设计为not null非常合理，但是，随着手机的普及，现在许多平台都可以使用“手机号码”和“手机验证码”实现登录，根本不需要“用户名”和“密
码”，在这种情景下，“用户名”和“密码”就需要设计为“允许为null”。

另外，因为思维模式的局限性，站在开发人员的角度，可能很难想像某些数据“允许为null”的情景，例如“商品”的“价格”，但是，结合界面的设计、使用者的操作习惯，not null的约束可能会带来一些问题，例如，当商家准备发布一款商品时，可能“价格”还没有最终确定，但是希望先填写其它信息，例如商品的“标题”、“简介”、“详细介绍”等，如果把“价格”设计为not null，使用者的操作就非常不灵活，所以，“允许为null”会让设计变得更加自由。

其实，数据是否允许为null，在某种程度上来说，也算是一种业务规则（是否必须填写），只要程序的业务逻辑是完整的，在数据库（或数据表）中并不需要设计得特别严格，还是那句话，调整数据库（或数据表）设计的风险太大，能够通过程序完善的，就不要在数据库（或数据表）中设计规则。

关于unique约束

该约束表示“唯一”，以“用户”为例，如果需要“用户名”具有“唯一”的特性，则可以添加unique.

但是，此前的设计思路中一直有个观点：业务规则应该通过程序来实现。

所以，如果要保证“唯一”的特性，只要程序的业务逻辑是完整的，根本不需要在表中添加unique约束！同时，通过数据表的设计来添加约束反而会使用得程序设计受到约束，后期还可能带来维护相关问题！这样看来，添加unique约束可能不是什么好事。

其实，设计数据表时添加的unique约束可以作为数据安全的最后一道防线，即使出现某些特殊的情况导致程序业务逻辑出现Bug，甚至某些数据绕过程序代码直接发送到了数据库服务器，也能保证数据是“唯一的”，不过，这不是最主要的原因，unique不仅仅只是一种约束，它还是一种索引（唯一索引），能非常明显的提高该字段的查询效率，以“用户”为例，绝大部分项目的期望用户数量都是达到千万级甚至更多，在验证用户身份（例如：登录）时可能需要“根据用户名查询数据”，如果没有索引，查询效率就会非常低，有索引时查询效率就非常高！综合来看，“用户名”这样的字段，既需要是“唯一的”，也需要保证该字段的查询效率，那么，使用unique是一举两得的。

合理的设计冗余字段

在《阿里巴巴Java开发手册》中已明确的提到“字段允许适当冗余，以提高查询性能”，所以，在本项目中，有许多“冗余”的设计，简单来说，就是“存了不必要的（在其它表中已经存储的）数据”，例如在ams_admin表中就存储了“最后登录IP地址”、“最后登录时间”这2项数据，事实上，通过查询ams_login_log表也可以查出相关数据，但是，并不是所有时候都需要“显示某1个管理员的登录日志”，在“显示管理员列表”时，可能也需要显示每个管理员的最后登录信息，如果没有使用冗余的设计，就必须使用关联查询才可以实现，而使用冗余的设计后，只需要查询ams_admin即可，显然，只查询1张表比关联查询的效率更高一些。

另外，冗余的设计并不一定是把同一个数据存储多次，例如在ams_admin表中存储了“登录次数”这项数据，理论上，当需要知道某个管理员的登录次数时，应该统计ams_login_log表中的数据，所以，在ams_admin记录“登录次数”也是冗余的！同样的，这样的设计依然是为了便于查询。当然，一旦使用这样的设计，在编写业务时，每次向ams_login_log中插入或删除数据时，一定要更新ams_admin表中记录的“登录次数”的统计值，否则在ams_admin中记录的统计值就可能是不准确的，甚至，为了避免某些意外情况导致记录的统计值有误，还可以专门开发一个“修正”或“重新校对”的功能。

最后，每种设计观点是否正确，都需要结合实际使用情景才能评价，同一个设计观点，在不同时期来看，可能会得到完全不同的评价。例如，数据库的设计范式基本上都是反对冗余的，或不提倡冗余的，主要是因为提出这些设计范式的时间都比较偏早，那些年计算机硬件的性能并不强，而且昂贵（例如：在2000年左右，一块30GB的硬盘可能需要1200元），同时，由于互联网并不普及，上网渠道非常有限，网络传输速度也非常慢（例如：在2000年左右，大多家庭并没有接入互联网，并且，家用网络传输速度大多在256Kbps或以下）。

所以，硬盘空间相对昂贵，服务器的访问频率偏低，那么，不是每个网络应用程序都对性能有较高的要求，综合这些特点，在绝大部分网络应用程序的设计中，使用不冗余的设计的性价比更加高！相比之下，目前的硬盘空间相对廉价（例如：在2021年，一块2TB的机械硬盘大概350元，500GB的SSD固态硬盘不超过300元），而随着从2011年左右开始普及移动互联网，上网渠道更多、网民更多，导致各网络应用程序的访问量都急剧提升，性能才是更需要关注的，所以，为了得到更好的性能表现，牺牲一定的硬盘存储空间是值得的，也就产生了冗余的设计思路。

本文由 liyunfei 创作，采用知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: Jun 29,2022

数据库与数据表设计之设计规范