数据库设计文档规范

数据库设计文档规范

一、命名规范

1、总命名规范

• 库名与应用名称尽量一致
• 不得使用数据库保留关键字,以及java等常用语言的保留关键字,或者可能成为关键字的单词作为完整命名.（对于一些疑似关键字的单词,可以在后面加一个下划线来避免,例如“key_”）.

附：MySQL保留关键字列表：https://dev.mysql.com/doc/refman/5.7/en/keywords.html

• 如无特殊说明,表名、字段名必须用英文字母开头,禁止出现数字开头,采用有特征含义的单词或缩写,单词中间用“_”分割,且只能由英文字母和下划线组成,不能用双引号包含.

• 除数据库名称长度为1至8个字符,其余（包括表、字段、索引等）不超过30个字符,Database link名称也不要超过30个字符.（30并不是凭空想象出来的,而是参考了Oracle的限制）

• 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint (1 表示是,0 表示否).

说明：
任何字段如果为非负数,必须是 unsigned.
注意：POJO 类中的任何布尔类型的变量,都不要加 is 前缀.数据库表示是与否的值,使用 tinyint 类型,
坚持 is_xxx 的 命名方式是为了明确其取值含义与取值范围.
正例：表达逻辑删除的字段名 is_deleted,1 表示删除,0 表示未删除.

• 小数类型为 decimal,禁止使用 float 和 double.

说明：
float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,
得到不 正确的结果.如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储.

• 如果存储的字符串长度几乎相等,使用 char 定长字符串类型.

• varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索 引效率.

• 唯一索引名为 uk_字段名；普通索引名则为 idx_字段名.

说明：
uk_ 即 unique key；idx_ 即 index 的简称

• 不得使用外键与级联,一切外键概念必须在应用层解决.外键与级联更新适用于单机低并发,不适合分布式、高并发集群；级联更新是强阻塞,存在数据库更新风暴的风险；外键影响数据库的插入速度.

2、表名

• 表名不使用复数名词

• 表的命名最好是加上“业务名称_表的作用”.如,meet_user

• 表必备三字段：id, gmt_create, gmt_modified

说明：

• 其中 id 必为主键,类型为 bigint unsigned、单表时自增、步长为 1.
  (如果使用分库分表集群部署,则id类型为verchar,非自增,业务中使用分布式id生成器)
  gmt_create, gmt_modified 的类型均为 datetime 类型,前者现在时表示主动创建,后者过去分词表示被 动更新.

3、字段名

a) 表的字段数不超过50个.
b) 类型：各表之间相同含义的字段,类型定义要完全相同（包括精度、默认值等）；
c) 命名：

1. 字段名无单词数的限制,但是名字的字符长度应该符合上面的“总命名规范”.
2. 字段命名及其注释,要做到清楚、无歧义.

举两个实际的例子

1）有些数据可能会存在多种完全不同类型的状态,例如,例如汽车数据,有启停状态,参保状态,维修状态,年审状态……总之,在有些数据表中,有许多的状态字段.如果没写清楚,
例如有个字段 “STATUS tinyint NULL; – 状态”,这是让人很疑惑的,状态？到底是什么状态？
状态的取值有哪些？——如果改成“DELETE_STATUS tinyint default 0; – 删除状态（1：已删除,默认为0：未删除）”,这样的命名和注释,让人一目了然.
2）再比如“belong_dept – 所属部门”,这也有歧义,因为部门除了数据唯一ID之外,还有一个部门编码CODE也是唯一的.那到底是存 部门ID,
还是 部门编码 CODE？实际情况是,有的人认为存ID,有的却认为存编码.所以,在命名上就应该做到无歧义,如果要存ID,就应该命名为“belong_dept_id – 所属部门ID”,如果要存部门编码,就应该为“belong_dept_code – 所属部门编码”.

1. 同一个字段名在一个数据库中只能代表一个意思.比如phone在一个表中代表“座机号码”的意思,在另外一个表中就不能代表其他意思（比如手机名称、品牌等,否则在A表中phone存的是座机号码,在B表中存的是手机品牌,那就混乱了）

2. 反之,代表同一个意思的字段,在各个表中都用相同单词表示,例如电话号码字段,在A表中叫telephone,在B表中叫phone,在C表中叫mobile,这样就很混乱.

特殊情况：如果有多个字段时,可以加前缀或后缀区分,代表复数含义时,单词后可以加s,例如user_ids.比如“电话号码”,在A表字段中名称为tel,在B表中也只能叫做tel（但是如果B表中有多种电话号码,可以加后缀,例如 保卫部 tel_bw,科技部 tel_kj,综合部 tel_zh）.

3. 对于多个表关联的外键字段,例如 create_user_id,关联的是 user表里面 id 字段,建议的命名规则是 “关联表名（无需前缀）+“_”+关联字段名”,也就是说,单词是根据表和字段名而来的,不是凭空随便想出来的.例如这个 create_user_id,create_是前缀,user_代表 abf_sys_user表,id代表abf_sys_user表的id字段.再比如create_user_dept_code,user_是abf_sys_user表的后缀,dept_是abf_sys_dept表的后缀,code是abf_sys_dept表的code_字段.

综合第2、3点,再举一例：有一个部门表abf_sys_dept,里面有一个部门编码字段code_,如果有一个表需要保存 “责任部门编号” 和 “创建人所属部门编号”,按照规范,这两个字段可以命名为：resp_dept_code 和 create_user_dept_code.

4、主键名

前缀为PK_.以PK_+表名+主键字段名构成.如果复合主键的构成字段较多,则只包含第一个字段.表名可以去掉前缀.例如PK_SYS_CAR_ID.

5、外键名

前缀为FK_.以FK_+ 外键表名 + 主键表名 + 外键字段名构成.表名可以去掉前缀.例如FK_SYS_USR_SYS_CAR_ID.

6、普通索引

前缀为IDX_.以IDX_+表名+索引字段名构成.如果复合索引的构成字段较多,则只包含第一个字段,并添加序号.表名可以去掉前缀.例如IDX_SYS_CAR_DIN.

7、主键索引

前缀为IDX_PK_.以IDX_PK_+表名+索引字段名构成.表名可以去掉前缀.例如IDX_PK_SYS_CAR_ID.

8、唯一索引

前缀为IDX_UK_.以IDX_UK_+表名+索引字段名构成.表名可以去掉前缀.例如IDX_UK_SYS_CAR_DIN.

9、外键索引

前缀为IDX_FK_.以IDX_FK_+表名+外键字段名构成.表名可以去掉前缀.例如IDX_FK_SYS_CAR_ID.

二、表设计规范

• 采用UTF8字符集.
• 对于数据量可能很大的表（超过2000万）,采用分库/分表/分区表,横向拆分控制单表容量.
• 必须为表、字段等添加注释.
• 遵守数据的设计规范3NF 规定.

• 表内的每一个记录都只能被表达一次.
• 表内的每一个记录都应该被唯一的标识（有唯一键）.
• 表内不应该存储依赖于其他键的非键信息.

• 反范式化冗余字段使用规范 考虑具体使用场景,当SQL关连查询比较频繁,或涉及到4张以上表时可考虑采用冗余字段.
• 必须设置唯一主键,尽量使用自增id作为主键.
• 建议主键为数字类型,且为递增顺序,主键不表示任何业务含义,严禁数据量大的表使用UUID/MD5作为主键.
• 不使用数据库外键,由程序保证.
• MySQL：

• 使用InnoDB存储引擎.
• 数据库和表字符集类型统一（utf8mb4 – UTF-8 Unicode）,排序规则统一（utf8mb4_unicode_ci）；建表语句中强制指定字符集；
• 自增字段类型必须是整型,使用 BIGINT类型.并且自增字段必须是主键或者是主键的一部分.

三、字段设计规范

1、 凡是可能被索引的字段,必须定义为NOT NULL,可以设置default值；
2、 非负值的数字统一使用unsigned（无符号）类型存储
3、 大对象字段

• 通常情况下,禁止使用LOB类型保存大文本、图片和文件,建议使用其他方式存储（例如文件系统,数据库只保存其地址信息）.
• MySQL：尽量不要使用TEXT数据类型,mysql的varchar类型支持65535字节,满足大多数场景,仅当字符数特别大时,才考虑text类型；

附–大对象字段处理方法：

• 将大对象字段从主表中拆分出来单独存放,与原表主键单独存储在另外一个表里；
• 如果是Oracle 12g之前的版本,VARCHAR2最多支持4000,如果文本内容只是偶尔可能超过4000,但是不会超过8000,那么可以用两个VARCHAR2字段来存储,使用的时候将这两个字段拼接起来就行了.
• 如果有方便的文件系统,可以将大文本或附件,保存在文件系统中,数据库中只保存其位置和路径信息即可.

4、 禁止使用enum,对于boolean类型或者表示简单状态的字段,MySQL用tinyint,Oracle用NUMBER(1)

• 建议字段not null,根据业务要求来设置默认值（例如默认为0）.
• 对于boolean类型,以1代表是（true）, 0 代表否（false）.
• 对于状态类型,注释中应该注明每一种状态的含义,例如“0：编辑中,1：审核中,2：已完成”.

5、数字、小数类型

• 对于数字、小数类型,不得使用VACHAR等字符串类型来保存,应该使用相应精度的数字、小数类型.
• 尽量确保数值型列都有默认值
• 对于Oracle,确定好Number的精度.
• 对于MySQL,选好数字类型：TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL(存储空间逐渐变大,而性能却逐渐变小),超过tinyint（256）但不超过65536的使用smallint；当该字段超过42亿时,才使用bigint；
• 使用DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数？？why？

5、时间类型标准

Oracle:有两种时间类型：DATE和TIMESTAMP,DATE的精度只保存到秒,例如“2013-11-02 11:16:36”,而TIMESTAMP精度更高可以保存小数秒,例如“2013-11-03 11:16:36.000000” .有时候,DATE只保存到秒,不足够区别出两个事件哪个先发生,这时建议使用TIMESTAMP类型.
MySQL：存储年使用year类型,存储日期使用date类型,使用精确时间戳（精确到秒）尽量使用timestamp类型,因为timestamp使用4字节,datetime使用8字节,它们的区别：TIMESTAMP值不能早于1970或晚于2037（‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC）.

6、必须使用int unsigned存储IPV4；
7、一些常见字段的命名统一

为了规范命名,并结合一般命名习惯,指定如下几个字段定义（以Oracle为例）：

• ID 编号 NUMBER(22)（Integer）
• Create_By 创建人 NUMBER(22)（Integer）
• Create_Time 创建时间 TIMESTAMP --默认为系统当前时间
• Update_By 修改人 NUMBER(22)（Integer）
• Update_Time 修改时间 TIMESTAMP --默认为系统当前时间

其他参考命名：

• Code_ 编码 VARCHAR2(30)
• Level_ 层级 NUMBER(1或2)
• Delete_Status 删除标志 NUMBER(1) --1：表示已经删除,默认为0：表示未删除
• Description_ 描述或备注 VARCHAR2（200）

四、索引规范

• 复合索引的字段数不能超过5个.
• 单表的索引数量尽量控制在5个以内.
• 联合索引的字段排列顺序以去重后字段的数值的个数大小排序先后顺序.比如表mk_task有id,name,id有50000个独立值,name有5000个独立值,那么,顺序是id在name前面,建立的索引是idx_id_name.
• Order by、distinct、group by后的字段尽量建立索引.
• update、delete的where尽量使用有索引的字段或主键.
• 超过20字节的varchar字段建议用前缀索引,禁止对字符串长度超过50个字符的列创建索引.
• 不建议在低基数列上创建索引,例如“性别”列；
• 合理创建联合索引（避免冗余）,(a,b,c) 相当于（a）、（a、b）、（a、b、c）.
• 长文本类型字段（例如Text）不能使用索引.

五、其他

• 主键ID

建议使用分布式全局唯一递增ID,比如类snowflake算法,很多大公司都在用,有许多成熟的案例,而且百度、腾讯、美团都把自己的ID生成工具开源了.

• 禁止使用存储过程、视图、事件、触发器、数据库自带的分区表.
• 临时库、表名必须以”tmp_日期”为后缀,如当日创建多个,则在日期后增加数字后缀
• 备份库、表必须以”bak_日期”为后缀,如当日创建多个,则在日期后增加数字后缀

SQL规约

• 【强制】不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关.

说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行.
【强制】count(distinct col) 计算该列除NULL之外的不重复数量.注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0.

• 【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题.

正例：可以使用如下方式来避免sum的NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g))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更新，则为级联更新.外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度.

• 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性.
• 【强制】IDB数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能提交执行.
• 【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内.
• 【参考】移植需要，所有的字符存储与表示，均以utf-8编码，那么字符计数方法注意：

说明：
SELECT LENGTH(“阿里巴巴”)； 返回为12
SELECT CHARACTER_LENGTH(“阿里巴巴”)； 返回为4
如果要使用表情，那么使用utfmb4来进行存储，注意它与utf-8编码.

• 【参考】TRUNCATE TABLE 比 DELETE速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发trigger，有可能造成事故，故不建议在开发代码中使用此语句.

说明：TRUNCATE TABLE 在功能上与不带 WHERE子句的 DELETE语句相同.

ORM规约

• 【强制】在表查询中，一律不要使用*作为查询的字段列表，需要哪些字段必须明确写明.

说明：
1）增加查询分析器解析成本
2）增减字段容易与resultMap配置不一致.

• 【强制】POJO类的boolean属性不能加is，而数据库字段必须加is_，要求在resultMap中进行字段与属性之间的映射.

说明：参见定义POJO类以及数据库字段定义规定，在sql.xml增加映射，是必须的.

• 【强制】不要用resultClass当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每一个表也必然有一个与之对应.

说明：配置映射关系，使字段与DO类解耦，方便维护.

• 【强制】xml配置中参数注意使用：#{}，#param#不要使用${}此种方式容易出现SQL注入.
• 【强制】iBATIS自带的queryForList(String statementName,int start,int size)不推荐使用.

说明：
其实现方式是在数据库取到statementName对应的SQL语句的所有记录，再通过subList取start,size的子集合，线上因为这个原因曾经出现过OOM.
正例：在sqlmap.xml中引入 #start#, #size#
Map<String, Object> map = new HashMap<String,Object>();
map.put(“start”, start);
map.put(“size”, size);

• 【强制】不允许直接拿HashMap与HashTable作为查询结果集的输出.

反例：某同学为避免写一个，直接使用HashTable来接收数据库返回结果，结果出现日常是把bigint转成Long值，而线上由于数据库版本不一样，解析成BigInteger，导致线上问题.

• 【强制】更新数据表记录时，必须同时更新记录对应的gmt_modified字段值为当前时间.
• 【推荐】不要写一个大而全的数据更新接口，传入为POJO类，不管是不是自己的目标更新字段，都进行update table set c1=value1,c2=value2,c3=value3; 这是不对的.执行SQL时，尽量不要更新无改动的字段，一是易出错；二是效率低；三是binlog增加存储.
• 【参考】@Transactional事务不要滥用.事务会影响数据库的QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等.
• 【参考】中的compareValue是与属性值对比的常量，一般是数字，表示相等时带上此条件；表示不为空且不为null时执行；表示不为null值时执行.
  te table set c1=value1,c2=value2,c3=value3; 这是不对的.执行SQL时，尽量不要更新无改动的字段，一是易出错；二是效率低；三是binlog增加存储.
• 【参考】@Transactional事务不要滥用.事务会影响数据库的QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等.
• 【参考】中的compareValue是与属性值对比的常量，一般是数字，表示相等时带上此条件；表示不为空且不为null时执行；表示不为null值时执行.