数据库设计规范

**************************************************************************** 一、 命名规范

说明：指数据库对象如表（TABLE）、序列（SEQUENCE）、过程（PROCEDURE）、触发器（TRIGGER）等的命名约定。 1. 基本命名原则

(1)规则1:命名使用具有意义的英文词汇，词汇中间以下划线分隔。 (2)规则2:命名只能使用英文字母，数字，下划线，并以英文字母开头。 (3)规则3:避免用ORACLE、MySQL的保留字如desc，关键字如index。 2. 表命名

(1)规则1:同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。 (2)规则2:长度不超过25个字符。 3. 字段命名

(1)规则1:表达其实际含义的英文单词或简写。

MySQL：布尔意义的字段以“is_”作为前缀，后接动词过去分词。 (2)规则2:各表之间相同意义的字段应同名。

MySQL：各表之间相同意义的字段，以去掉模块前缀的表名_字段名命名。 (3)规则3:外键字段用表名_字段名表示其关联关系。 MySQL：禁止使用外键。

示例：log_prepay.id=log_account.prepay_id 示例（MySQL）：字段prepay_group.id被prepay_contact引用；prepay_contact表中与之对应的字段命名为：group_id 4. 索引命名

(1)规则1:表名称_字段名_IND MySQL：idx_表名称_字段名

示例：employee表first_name上的index：employee_fid_ind 示例（MySQL）：prepay表agtphone_id和stream_id上的index：idx_prepay_aid_sid 5. 约束命名

(1)规则1:主键约束：表名称_PK。

(2)规则2:唯一约束：表名称_字段名_UK。

MySQL：除主键外，需存在唯一性约束的，可通过创建以“uk_”为前缀的唯一索引实现，但应用中需要同时有唯一性检查逻辑。 6. 触发器命名

(1)规则1: TRG_表名_操作。 7. 函数过程命名

(1)规则1:采用动词+名词的形式表达其含义。 8. 序列命名

(1)规则1:SEQ_表名。

二、 对象设计使用规范

1. 常用数据类型

说明：以下为常用数据类型介绍，表设计时根据实际应用选用。

(1)NUMBER(p,s):固定精度的精确数字类型，整数或定点数使用此类型。

(2)NUMBER:不固定精度的精确数字类型，不确定数值精度，主键或浮点数使用此类型。 (3)DATE:仅需要精确到秒时，选择DATE而不是TIMESTAMP类型。 (4)TIMESTAMP:扩展日期类型，仅当需要更高时间精度时使用此类型。 (5)VARCHAR2:变长字符串，最长4000个字节。 (6)CHAR:定长字符串，只允许使用CHAR(1)。

(7)CLOB:当超过4000字节时使用，CLOB字段必须拆分到一张独立的表中，用主键与主表关联。clob字段，建议不要使用，而用多个varchar2(4000)代替，因为clob字段更新时，空间不重用。(8)BLOB,RAW,LONG:不允许使用。

MySQL中，仅可以使用下文提及的数据类型，所有表、字段必须添加能够清楚表示其含义的注释。状态类字段的注释中必须明确列出各状态值的说明。

(1) DECIMAL(M,D): 当表示定点小数的情况下使用该类型。定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

(2)INT系列: 所有整数类型字段使用INT（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）,根据所存放的数据大小选择合适的子类型，且所有INT类型都不使用长度限制。 (3)CHAR: 当且仅当字段存储单个字符的情况下，可使用CHAR(1)类型。超过一个字符的字段，使用可变长度类型。

(4)VARCHAR: 所有可变长度的字段均使用VARCHAR类型，对于有限类别的字段（如性别、状态等），均使用VARCHAR类型存储能明显表现其意义的字符串。当VARCHAR字段长度超过4000时，须和原表进行分拆，与原表主键组成新表存储。

(5)TEXT系列: 仅当需存储的字节数可能超过20000时，使用TEXT系列类型（TEXT、MEDIUMTEXT、LONGTEXT）。并和原表进行分拆，与原表主键组成新表存储。因为所有MySQL数据库都会使用UTF8字符集，而VARCHAR最大只能存放64K数据。

(6)DATE: 只需要精确到天的字段使用DATE类型。精确到“天”的取当前时期的操作使用CURDATE()函数实现。

(7)DATETIME: 需要精确到时间（时、分、秒）的字段使用DATETIME类型。精确到“秒”的取当前时间的操作使用NOW()函数实现。 2. 表设计约定

(1)规则1:表必须要有主键。

(2)规则2:一个字段只表示一个含义。

(3)规则3:总是包含两个日期字段:ll_create(创建日期),ll_modified(修改日期)，且这两个字段不应该包含有额外的业务逻辑。

(4)规则4:MySQL中，ll_create、ll_modified使用DATETIME类型。 (5)规则5:禁止使用复杂数据类型（数组，自定义类型等）。

(6)规则6: MySQL中，附属表拆分后，附属表id与主表id保持一致。不允许在附属表新增主键字段。

(7)规则7: MySQL中，存在过期概念的表，在其设计之初就必须有过期机制，且有明确的过期时间。过期数据必须迁移至历史表中。

(8)规则8: MySQL中，不再使用的表，必须通知DBA予以更名归档。

(9)规则9: MySQL中，线上表中若有不再使用的字段，为保证数据完整，禁止删除。 3. 约束使用约定

(1)规则1:主键必须是无意义的，由序列或主键生成器(MySQL中,主键由主键生成器或自

增长字段产生)产生，禁止使用组合主键。

(2)规则2:表中除主键以外还存在唯一约束的，必须在数据库中创建唯一约束。 (3)规则3:主键字段不允许更新。

(4)规则4:不创建外键约束，外键约束由应用控制。 4. 索引设计约定

(1)规则1:不使用Bitmap类型的索引。 (2)规则2:不创建unique类型的索引。 (3)规则3:索引字段选择注意事项： ①选择在where子句中出现，且选择性和过滤性高的字段创建索引。 ②用来和其他表关联的字段，如果使用nest loop join，建议创建索引。 ③索引的首字段，必须在where条件中。 ④复合索引，将选择性高的字段放在前面。 (4)规则4:索引使用注意事项： ①考虑DML操作(insert，delete，update)导致索引维护成本。 ②在索引字段上施加函数或者隐式类型转换会导致索引失效。 ③查询结果集（Cardinality）比较小的情况下，适用索引查询。 ④索引不能用来判断is null或者is not null。 5. 过程，触发器，视图使用原则

(1)规则1：避免将业务逻辑封装在数据库procedure/function中，不允许应用调用数据库中的procedure和function，业务逻辑应该用程序实现。

(2)规则2：不允许使用trigger实现业务逻辑。增量数据同步和记录数据变化除外，比如搜索引擎增量build，易百米同步等。 (3)规则3: 不允许view与table关联。

(4)规则4:MySQL，禁止任何业务逻辑通过封装在数据库中的procedure或function或trigger实现。

(5)规则5:MySQL， 禁止应用程序使用view。 (6)规则6: MySQL，禁止业务逻辑使用计划任。 (7)规则7: 使用SVN来管理这些脚本。 6. DB_LINK使用规范

(1)规则1：只有DBA拥有dblink使用权限，不允许应用调用dblink 。

(2)规则2：DB_LINK只读，不允许通过DB_LINK修改数据。数据仓库同步除外。 7. 物化视图使用规范

(1)规则1 因oracle内部bug，故规定物化视图内不允许使用多表join。

三、SQL编写规范 1. 基本SQL编写规范

(1)规则1:SQL中直接使用表名，不使用schema作为前缀，跨schema访问需要创建synonym（数据仓库，CRM数据库不适用）。

(2)规则2:必须使用绑定变量, 避免常量的直接引用。 说明：频繁的硬解析会影响数据库性能。

(3)规则3:避免在where子句中对索引字段施加函数，这样将导致索引失效。 示例：

错误的写法：

select employee_id, first_name from to_char(hire_date,'yyyymmdd')='20120601'； 正确的写法：

select employee_id, first_name from hire_date=to_char('20120601','yyyymmdd');

(4)规则4:Like不允许使用全模糊，只允许使用右模糊查询。 说明：全模糊查询无法使用INDEX，可能造成性能问题。 示例：

全模糊查询：

employee where

employee where

select employee_id, first_name from employee where name like '%joe%'; 右模糊查询：

select employee_id, first_name from employee where name like '%joe%'; (5)规则5:如果使用Oracle数据库，使用Oracle的外连接，而不是标准的ANSI 99外连接语法。 说明：ANSI 99外连接语法是指inner join、left join、right join、full outer join，Oracle使用（+）来表示外连接。 示例：

错误的写法：

Select employee_id, first_name from employee a left join on department b a.department_id = b.department_id; 正确的写法：

Select employee_id, first_name from employee a , department b where a.department_id = b.department_id(+); (6)规则6:Insert语句必须写上字段名。

(7)规则7:严格要求使用正确类型的变量，杜绝隐式类型转换。

书写SQL时，必须确定表中各个字段的数据类型，防止发生隐式类型转换。 示例：

ID是NUMBER型 错误的写法：

Select employee_id, first_name from employee where employee_id = '123';--这里会造成oracle将id先转换成varchar类型，导致索引失效。 正确的写法：Select employee_id, first_name from employee where employee_id = 123; 反之亦然，字符型数据一定加上单引号。

时间类型的字段，必须使用TO_DATE进行赋值（当前时间可直接用sysdate表示） 示例：

错误的写法（使用date类型的变量）： select agtphone, name from log_account_info

where accountno = #accountnot :varchar# and optime >= #dateBegin :date# 正确的写法：

select agtphone, name from log_account_info where accountno = #accountno:varchar# and to_date(#dateBegin :varchar#, 'yyyy-mm-dd hh24:mi:ss')

optime >=

(8)规则8:MySQL，任何过滤字段值必须加上单引号。 2. select语句中*号的问题

(1)规则1:尽量不要使用select *。

(2)规则2:表连接时，不允许使用select * 。

(3)规则3:单表查询，允许使用select * ，但以下几种情况禁用： ①表中包含clob字段。 ②表中包含长度较大的字段，比如varchar2(2000)以上的字段，但该SQL实际上并不需要取出该字段的值。 (4)规则4:MySQL中，当结果集中无需列出被查询表的所有字段，且查询中存在“order by”语句时。禁止使用“select *”取出所有字段。

(5)规则5:MySQL中，连接查询中，禁止使用“select *”。 3. NULL的使用

说明: NULL的含义是\"不确定\"，而不是\"空\"

(1)规则1: 查询时，使用is null或者is not null (2)规则2: 更新时，使用等于号。 示例：

update tablename set column_name = null ； 4. 聚合函数常见问题

(1)规则1:计数使用count（*），而不是count(1)或count(pk)。 说明: count(column_name)计算该列不为NULL的记录条数

count(distinct column_name)计算该列不为NULL的不重复值数量

count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用nvl(sum(),0)来避免返回NULL

(2)规则2:用于排序的字段，如果有null值，请注意处理null值的顺序。如果null值排在前面，排序语句添加 nulls first关键字，如果null值排在后面，排序语句添加 nulls last关键字。