数据库设计规范

4.3.1 表设计

规则1：数据库设计文档中，必须包含表数据保留时间；
规则2：数据库设计文档中，必须包含表在最大保留时间下的数据量；
规则3：数据库设计文档中，必须包含表的读写频率；
规则4：和其他表有关联的表，和其他表功能一致的字段类型以及长度，尽量使用相同的列名；
规则5: 每个表应设计一个主键；
规则6：数据库字符集，表字符集，字段字符集统一选用UTF8字符集，校对规则统一使用大小写敏感的utf8_bin；
规则7：表引擎选用INNODB引擎；
规则8：必须要有表的注释，用于描述表的功能；

建议1：对于需要同步到数据仓库的表，原则上必须包含同步频率以及同步机制；
建议2：历史表后缀建议用"_hist";

4.3.2 字段

规则1：字段必须要有注释信息，如果字段的值是有限的（如状态值只有"有效"、"无效"，如性别只有"男"、"女"等）必须在字段注释中对每个值表达的意思进行描述；
规则2：定长字符列使用CHAR类型, 不定长字符型使用VARCHAR类型；
规则3：日期字段只需要表达年月日的选用DATE类型，需要表达年月日时分秒的字段选用DATETIME类或TIMESTAMP类型，但请注意各自能表达的范围以及TIMESTAMP的时区特性；
说明：MySQL中的DATETIME对应ORACLE的DATE类型，而MySQL的DATE类型只是ORACLE DATE类型的年月日部分不包括时分秒部分，MySQL TIME类型是ORACLE DATE 类型的时分秒部分，下表是MySQL各时间类型的格式样例
 

     
DATETIME与TIMESTAMP类型的区别

 
规则4：ORACLE转MySQL之NUMBER字段类型转换；
number(M,N)如果N是0则为整形，对应MySQL的整形类型，下面是MySQL的各整形类型的所需字节数及能表达的范围（摘抄至官网5.6）,

如果number(M,N)中N大于0则对应MySQL的decimal类型，如oracle的number(5,2)则MySQL为decimal(5,2),值得注意的是，在超出范围的情况下ORACLE会报错，而MySQL会取它能表示的最大值来替代原来的值，如decimal(5,2)/number(5,2)能表达的范围为 -999.99-999.99 如果要插入1000的数据，oracle会提示超表达范围的错，而MySQL会以999.99来替代
 
规则5：固定长度的字符串使用CHAR,单字符字段使用CHAR(1)类型；
规则6：字段避免使用NULL值，用默认值来替代，修改时间，审核时间等用"0000-00-00 00:00:00"这样的默认值进行替代，备注用''的空字符进替代；
规则7：不建议使用ENUM,SET类型，用TINYINT替代；

建议1:   尽量不使用BLOB,TEXT类型，大字段建议单独设计表，通过关联进行查找；
建议2:   建议使用UNSIGNED 存储非负数值；
同样的字节数，存储的数值范围更大

4.3.3 索引

规则1:   无特别说明，每个表的索引不得超过5个；
规则2:   单字段上的索引不得超过2个；（即一个单字段最多可在上面建立一个单字段索引和一个组合索引包含这个字段）
规则3:   复合索引原则上不得超过3个字段；
规则4:   外键列需要创建索引；

建议1:   频繁出现在where子句里的字段建议建立索引；
建议2:   用来和其他表关联的字段建议建立索引；
建议3:   索引字段建议有高的选择性和过滤性（count(distinct)/count>0.6）；
建议4:   建立索引的时候，建议考虑到SELECT和INSERT,UPDATE,DELETE的平衡；
建议5:   一般建议在查询数据量10%以下使用索引；
建议6:   WHERE子句的查询条件构成索引字段前导字段；
建议7:   选择性更高的字段放在组合字段索引的前导字段；
建议8:   如果字段选择性接近，则把频繁查询的字段放在前面；
建议9:   进行GROUP BY或者是ORDER BY的字段应在组合字段索引的前导字段；

4.3.6 视图

规则1：视图中不允许出现ORDER BY排序；
规则2：基于多表关联的视图，必须在字段名前指定表别名；

建议1：视图的基础数据尽量从表中获取，尽量不要嵌套视图；

4.3.7 存储过程

规则1： 避免将业务逻辑放在存储过程中，那样容易将业务逻辑和DB耦合在一起；
规则2： 存储过程，必须有异常捕获代码；
规则3： 存储过程中严禁使用GOTO语句进行跳转；
规则4： 有循环更新的存储过程，必须进行批量提交，且必须进行事务控制；
说明：MySQL存储过程中必须用START TRANSACTION 来显示开始一个事务,否则会按默认的每个DML做个一个事务。
规则5： 存储过程中如果使用了游标，则在存储过程正常或者异常退出必须关闭所有打开的游标；
规则6： 存储过程中如果有更新，必须在异常捕获代码中做回退操作；
规则7： 注释格式如下,存储过程说明放在在COMMENT中，其他用"##"进行注释说明；
CREATE PROCEDURE prc_vendor
COMMENT  '说明:同步下发接收; 参数:xxxx; 返回:标志0=成功;'
##建立：xxx 2012.07.17

1.  
   1. Modify by xxx 2012.08.08 增加供应商状态字段
      ##Modify by xxx 2012.11.10 增加供应商英文名称字段
      BEGIN
      ...
      END;
      建议1：存储过程每次被更新，应在注释中说明更新的内容，更新的日期，以及更新的责任人，并且在更新前保留旧版本代码
      建议2：尽量避免在存储过程中使用动态SQL。
      

4.3.8 函数

• 规则1： 避免将业务逻辑放在函数中，那样容易将业务逻辑和DB耦合在一起；
  规则2： 函数中，如果进行了事务处理，必须有异常捕获代码；
  规则3： 函数中严禁使用GOTO语句进行跳转；
  规则4： 有循环更新的函数，必须进行批量提交，且必须进行事务控制；
  规则5： 函数中如果使用了游标，则在函数正常或者异常退出必须关闭所有打开的游标；
  规则6： 函数中如果对数据进行了更新操作，必须在异常捕获代码中做回退操作；
  规则7： 注释格式如下
  CREATE FUNCTION func_get_serialno
  (
       p_request     VARCHAR       ###请求编号
  )
  return VARCHAR
  COMMENT  '说明：产生序列号函数，通过serialno_config配置表响应产生序列号; 参数：p_requestid 请求编号 返回：返回需要的序列号'
  ##建立：xxx 2013.07.02
  ##modified by xxx 2014-6-18 xxx
  BEGIN
  ...
  END;
  
  建议1：函数每次被更新，应在注释中说明更新的内容，更新的日期，以及更新的责任人，并且在更新前保留旧版本代码；
  建议2：函数尽量只是实现复杂的计算功能，不对数据库进行更新操作；

4.3.9 触发器

规范1：如无必要，不得设计触发器，任何触发器的设计，必须得到DBA批准；
规范2：应用的完整性不应由触发器保证，而是通过代码的事务控制；
建议1：有高度一致性依赖的逻辑，触发器应设计为BEFORE而非AFTER方式；