数据库设计规范5727

****************************************************************************/数据库设计规范/****************************************************************************一、命名规范说明：指数据库对象如表（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_id4.索引命名(1)规则1:表名称_字段名_INDMySQL：idx_表名称_字段名示例：employee表first_name上的index：employee_fid_ind示例（MySQL）：prepay表agtphone_id和stream_id上的index：idx_prepay_aid_sid5.约束命名(1)规则1:主键约束：表名称_PK。(2)规则2:唯一MySQL：除主键外，需存在唯一但应用中需要同时有唯一性检查逻辑。约束：表名称_字段名_UK。性约束的，可通过创建以“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:定长字符串，只允许使用(7)CLOB:当超过4000字节时使用，CLOB字段必须拆分到一张独立的表中，用主键与主表关联。clob字段，建议不要使用，而用多个varchar2(4000)代替，因为clob字段更新(8)BLOB,RAW,LONG:不允许使用。MySQL中，仅可以使用下文提及的数据类型，所有表的注释。(1)DECIMAL(M,D):当表适合用来表示货币等精度高的数据。(2)INT系列:所有整数类型字段使用INT（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）,根据所大小选择合适的子类型，且所有INT类型存放的数据都不使用长度限制。(3)CHAR:当且仅当字段存储单个字符的情况下，可使用CHAR(1)类型。超过一个字符的CHAR(1)。时，空间不重用。、字段必须添加能够清楚表示其含义状态类字段的注释中必须明确列出各状态值的说明。示定点小数的情况下使用该类型。定点数在MySQL内部以字符串形式存储，比浮点数更精确，字段，使用可变长度类型。(4)VARCHAR:所有可变长度的字段别、状态等），均使用VARCHAR类型段长度超过4000时，须和原表进行分拆，与(5)TEXT系列:仅当需能超过20000时，使用TEXT系列类型MEDIUMTEXT、LONGTEXT）。并和原表进行分拆，与原表主键组成新表MySQL数据库都会使用UTF8字符VARCHAR最大只能存放64K数据。集，而(6)DATE:只需要精确到天的字段使用DATE类型。精确到“天”的取当前时期的操作使用CURDATE()函数实现。(7)DATETIME:需要精确到时间的取当前时间的操作使用NOW()函数实2.表设计约定(1)规则1:表必须要有主键。(2)规则2:一个字段只表(3)规则3:总是包含两个日期字段该包含有额外的业务逻辑。(4)规则4:MySQL中，ll_create、ll_modified使用(5)规则5:禁止使用复杂数据类型（数组，自定义类型等）。(6)规则6:MySQL中，附属表拆分后，附属表id与主表id保持一致。不允许均使用显表现其意义的字符串。当原表主键组成新表VARCHAR类型，对于有限类别的字段（如性存储能明VARCHAR字存储。存储的字节数可（TEXT、存储。因为所有（时、分、秒）的字段使用DATETIME类型。精确到“秒”现。示一个含义。:ll_create(创建日期),ll_modified(修改日期)，且这两个字段不应DATETIME类型。在附属表新增主键字段。(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)规则(3)规则3:索引字段选择注意事项：①选择在where子句中出现，且选择性和过滤性高的字段创建索引。2:不创建unique类型的索引。②用来和其他表关联的字段，如果使用nestloopjoin，建议创建索引。③索引的首字段，必须在where条件中。④复合索引，将选择性高的字段放在前面。(4)规则4:索引使用注意事项：①考虑DML操作(insert，delete，update)导致索引维护成本。②在索引字段上施加函数或者隐式类型转换会导致索引失效。③查询结果集（Cardinality）比较小的来判断isnull或者isnotnull。5.过程，触发器，视图使用原则(1)规则1：避免将业务逻辑封装在数据库procedure和function，业务逻辑应该用程序实现。(2)规则2：不允许使用trigger实现业务逻辑。增量数据同步和记录数据变化除外，比如搜索引擎增量build，易百米同步等。(3)规则3:不允许view与table关联。情况下，适用索引查询。④索引不能用procedure/function中，不允许应用调用数据库中的(4)规则4:MySQL，禁止procedure或function或任何业务逻辑通过封装在数据库中的trigger实现。(5)规则5:MySQL，禁止应用程序使用view。(6)规则(7)规则7:使用SVN来管理这些脚本。6.DB_LINK使用规(1)规则1：只有DBA拥有dblink使用权限，不允许应用(2)规则2：DB_LINK只读，不允许通过DB_LINK修改数据。数据7.物化视图使用规(1)规则1因oracle内部bug，故规定6:MySQL，禁止业务逻辑使用计划任。范调用dblink。仓库同步除外。范物化视图内不允许使用多表join。三、SQL编写规范1.基本SQL编写规范(1)规则1:SQL中直接使用表名，不使用schema作为前缀，跨schema访问需要创建synonym（数据(2)规则2:必须使用绑定变量,避免常量的直接引用。说明：频繁的硬解析会影响数据库(3)规则3:避免在where子句中对索引字段仓库，CRM数据库不适用）。性能。施加函数，这样将导致索引失效。示例：错误的写法：selectto_char(hire_date,'yyyymmdd')='20120601'；employee_id,first_namefromemployeeemployeewherewhere正确的写法：selectemployee_id,first_namefromhire_date=to_char('20120601','yyyymmdd');(4)规则4:Like不允许使用全模糊，只允许使用右模糊查询。说明：全模糊查询无法使用INDEX，可能造成性能问题。示例：全模糊查询：selectemployee_id,first_namefromemployeewherenamelike'%joe%';右模糊查询：selectemployee_id,first_namefromemployeewherenamelike'%joe%';(5)规则5:如果使用Oracle数据库，使用Oracle的外连接，而不是标准的ANSI99外连接语法。说明：ANSI99外连接语法是指innerjoin、leftjoin、rightjoin、fullouterjoin，Oracle使用（+）来表示外连接。示例：错误的写法：Selectemployee_id,first_namefromemployeealeftjoinondepartmentba.department_id=b.department_id;正确的写法：Selectemployee_id,first_namefromemployeea,departmentbwherea.department_id=b.department_id(+);(6)规则6:Insert语句必须写(7)规则7:严格要求使用正确书写SQL时，上字段名。类型的变量，杜绝隐式类型转换。必须确定表中各个字段的数据类型，防止发生隐式类型转换。示例：ID是NUMBER型错误的写法：Selectemployee_id,first_namefromemployeewhereemployee_id='123';--这里会造成oracle将id先转换成varchar类型，导致索引失效。正确的写法：Selectemployee_id,first_namefromemployeewhereemployee_id=123;反之亦然，字符型数据一定加上单引号。时间类型的字段，必须使用TO_DATE进行赋值（当前时间可直接用sysdate表示）示例：错误的写法（使用date类型的变量）：selectagtphone,namefromlog_account_infowhereaccountno=#accountnot:varchar#andoptime>=#dateBegin:date#正确的写法：selectagtphone,namefromlog_account_infowhereaccountno=#accountno:varchar#andoptime>=to_date(#dateBegin:varchar#,'yyyy-mm-ddhh24:mi:ss')(8)规则2.select语句中(1)规则1:尽量不要使用select*。8:MySQL，任何过滤字段值必须加上单引号。*号的问题(2)规则2:表连接时，不允许使用select*。(3)规则3:单表查询，允许使用select*，但以下几种情况禁用：①表中包含clob字段。②表中包含长度较大的字段，比如varchar2(2000)以上的字段，但要取出该字段的值。(4)规则4:MySQL中，当结果集中“select*”取出所有字段。5:MySQL中，连接查询中，禁“select*”。3.NULL的使用说明:NULL的含义是"不确定"，而不是"空"(1)规则1:查询时，使用isnull或者isnotnull(2)规则2:更新时，使用该SQL实际上并不需无需列出被查询表的所有字段，且查询中存在“orderby”语句时。禁止使用(5)规则止使用等于号。示例：updatetablenamesetcolumn_name=null；4.聚合函数常见问题(1