数据库设计规范

目的：为了优化数据库的设计，提高数据库设计的合理性和数据访问高效性，同时便于阅读和理解数据库的结构，以提高数据共享的质量和效率，促进数据库编码的标准化，特制订一套数据库设计规范。

一、总体规范

1. （强制）采用26个英文字母(区分大小写)和0-9这十个自然数，加上下划线'_'组成，共63个字符，不能出现其他字符(注释除外)。库、表、字段全部采用小写，不要使用驼峰式命名。
2. （强制）以上命名都不得超过30个字符的系统限制.变量名的长度限制为29(不包括标识字符@)
3. （强制）数据对象、变量的命名都采用英文字符，禁止使用中文命名。绝对不能在对象名的字符之间留空格。
4. 小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突
5. （强制）保持字段名和类型的一致性,在命名字段并为其指定数据类型和长度的时候一定要保证一致性. 例如：不可以数据类型在一个表里是整数,那在另一个表里可就别变成字符型了.
6. 默认使用utf8_general_ci字符集，数据库排序规则使用utf8_general_ci，这个根据公司DBA建议来定。
7. （强制）数据库名不能超过30个字符。

总览：

对象名	前缀	范例
表（table）	tt_ 、tl_ 、tm_ 、ts_（或不加前缀）	ts_userinfo/tm_user_info/tl_user_info
视图（view）	v_	v_user_info
存储过程（procedure）	pro_	pro_user_info
函数（function）	fn_	fn_user_info
视图(view)	vw_	vw_user_info
类和类体（type & type body）	typ_	typ_user_info
主键（primary key）	pk_	pk_user_info
外键（foreign key）	fk_	fk_user_info_fieldname
唯一索引（unique index）	uk_	uk_user_info_fieldname
普通索引（normal index）	idx_	idx_user_info_fieldname
位图索引（bitmap index）	bk_	bk_user_info_fieldname

二、建表规范

1. 建表时表名及字段名均需有中文注释，字段所代表的真正含义，如枚举值则建议将该字段中使用的内容都定义出来。
2. 系统类表：以”ts_”开头。
3. 基础数据表：以”tm_”开头。
4. 业务数据表：以”tt_”开头，其中针对不同的业务模块可以增加不同前缀作为模块标识，如：”tt_cm_”。
5. 日志表：以“tl_”开头，其中针对不同的业务模块可以增加模块标识，如：”tl_cm_”。
6. 临时库、表名必须以tmp_为前缀，并以日期为后缀， 如：“tmp_user_20160901”。
7. 备份库、表必须以bak_为前缀，并以日期为后缀如， 如：“bak_user_20160901”。
8. 表引擎取决于实际应用场景；日志及报表类表建议用myisam，与交易，审核，金额相关的表建议用innodb引擎。如无说明，建表时一律采用innodb引擎。
9. （强制）涉及CRUD操作的表，必须加上审计字段创建人，更新人，创建时间，更新时间。

三、建索引规范

1. 主键：pk+”_”+表名。
2. 唯一建：uk+”_” +表名，当有多个唯一键的时候后面加uk+”_” +表名+”_”+”01,02,03”。
3. 索引：idx+”_” +表名，当有多个索引的时候后面加idx+”_” +表名+”_”+”01,02,03”。
4. 外键：fk+”_” +表名，当有多个外键的时候后面加fk+”_” +表名+”_”+”01,02,03”。

四、建视图规范

视图以”vw_”开头，针对不同的业务模块可以增加模块标识，如：”vw_cm”。

五、建函数规范

函数以“fn_”开头，参数以”p_”开头，变量以”v_” 开头，在函数体内需有注释，同时需要记录每个版本修改历史，示例如下：

CREATE FUNCTION `FN_CM_MOBILE_COUNT`(`P_COUNT_DATE` DATE, `P_EMP_CODE` VARCHAR(50))

RETURNS INT

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '手机号码统计 '

BEGIN

#author:

#date:

#version:

#parameters

#P_COUNT_DATE         date          计算日期

#P_EMP_CODE           varchar(50)   计算人

#modified list

#who             date           description

END

六、建存储过程规范

存储过程以”pro_”开头，参数以”p_”开头，变量以”v_” 开头，在函数体内需有注释，同时需要记录每个版本修改历史，对于存储过程需要有异常处理，特别是涉及到数据操作的情况，示例如下：

CREATE PROCEDURE `PRO_CM_MOBILE_COUNT`(IN `P_COUNT_DATE` DATE, IN `P_EMP_CODE` VARCHAR(50))

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '手机号码统计 '

BEGIN

#author:

#date:

#version:

#parameters

#P_COUNT_DATE         date          计算日期

#P_EMP_CODE           varchar(50)   计算人

#modified list

#who             date           description

END

七、字段命名规范

1. 一行记录必须表内唯一，表必须有主键。表的主键一般都约定成为第一个字段id，自增类型，是别的表的外键均使用xxx_id的方式来表明，一般禁止使用varchar类型作为主键语句设计。
2. 字段命名中单词之间采用下划线“_”进行分隔。
3. 数据库表字段应该是有意义而且易于理解的，最好是能够表达字段含义的英文字母
4. 不建议使用数据库关键字，如：name，time，datetime，password 等。
5. 布尔意义的字段以“is_”作为前缀，后接动词过去分词。
6. 一般情况下，表需包含create_by，create_time和modify_by，modify_time字段，即表必须包含记录创建人，创建时间和修改人，修改时间4个字段。
7. 如无说明，表必须包含is_del，用来标示数据是否被删除，原则上数据库数据不允许物理删除。
8. 字段名不能超过30个字符。
9. 建议各表之前相同意义的字段应同名。

八、字段设计规范

1. （强制）规则:用尽量少的存储空间来存数一个字段的数据。

比如能用int的就不用char或者varchar

能用tinyint的就不用int

能用varchar(20)的就不用varchar(255)

时间戳字段尽量用int型，如created:表示从'1970-01-01 08:00:00'开始的int秒数。

1. 如无特殊情况，所有字段都设置NOT NULL，并设置默认值。
2. （强制）对于精确浮点型数据存储，如金额类型，需要使用Decimal类型，禁止使用FLOAT和DOUBLE。
3. 枚举类型使用 Varchar(2)、Varchar(4)，且需要说明枚举类型的各个不同取值的含义，例如 00，01，0000，0001  。
4. 尽可能不使用TEXT、BLOB类型。
5. 单条记录大小禁止超过8k（列长度(中文)*3(UTF8)+列长度(英文)*1）。
6. （强制）禁止在数据库中存储明文密码。
7. INT：所有整数类型的字段只能使用INT或BIGINT，且明确标识出无符号型（UNSIGNED），除非确实会出现负数，仅仅当该字段数字取值会超过22亿，才使用BIGINT类型；定义中禁止添加长度。
8. （强制）DATE类型：所有只需要精确到天的字段全都使用DATE类型，而不用DATETIME或TIMESTAMP。
9. DATETIME类型：所有需要精确到时间（时分秒）的字段均使用DATETIME，不能使用TIMESTAMP。
10. CHAR类型：仅仅只有单个字符的字段才能使用CHAR（1），如：性别。
11. 禁示使用LOB字段类型。
12. （强制）每个列上必须有中文注释，说明字段的含义。
13. （强制）自增字段类型必须是整型，推荐类型为INT和BIGINT，并且自增字段必须是主键或主键的一部分。
14. （强制）日期类型的字段不能使用VARCHAR或者CHAR类型，只能使用DATE、DATETIME、TIMESTAMP。
15. 字段建议使用NOT NULL，用默认值代替NULL，MYSQL NULL类型与ORACLE的NULL有差异，会进行索引中，此外，NULL在索引中的处理也是特殊的，会占用额外的存储空间。

九、索引使用原则：

1、逻辑主键使用唯一的成组索引,对系统键(作为存储过程)采用唯一的非成组索引,对任何外键列采用非成组索引.考虑数据库的空间有多大,表如何进行访问,还有这些访问是否主要用作读写。

2、大多数数据库都索引自动创建的主键字段,但是可别忘了索引外键,它们也是经常使用的键，比如运行查询显示主表和所有关联表的某条记录就用得上。

3、（强制）不要索引blob/text等字段,不要索引大型字段(有很多字符)，这样作会让索引占用太多的存储空间。

4、不要索引常用的小型表。

不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。

1. （强制）唯一索引由3个以下字段组成。
2. （强制）索引中的字段数建议不超过5个。
3. 单表的索引数量控制在5个以内。
4. （强制）禁上使用外键，太容易引起死锁，应由程序保证约束。
5. （强制）索引的首字段必须在WHERE条件中。
6. （强制）对于确定需要组成组合索引的多个字段，建议将选择性高的字段靠在最前。
7. （强制）禁止对过长的VARCHAR字段建立索引，需DBA评估。

十、数据库脚本命名规范

1. 脚本每个组要有专人进行整理、规范和监控。
2. 脚本每个版本都需要独立存放，这样可以进行历史追索。如：
   1. V1.0
   2. V1.1
3. 脚本每个版本中需要有升级脚本和回滚脚本、备份脚本三个目录，回滚脚本为版本发布失败回滚时要执行的版本。
4. 脚本存放时需要有顺序，以方便测试和运维人员执行脚本。如：
   1. 1.建表脚本
   2. 2.数据脚本
   3. 3.视图脚本
   4. 4.函数
   5. 5.存储过程
5. 当有多个建表、视图、函数或存储过程时可以分成多个脚本，但要保证顺序的连续性。