數(shù)據(jù)庫設(shè)計規(guī)范5727

****************************************************************************/數(shù)據(jù)庫設(shè)計規(guī)范/****************************************************************************一、命名規(guī)范說明：指數(shù)據(jù)庫對象如表（TABLE）、序列（SEQUENCE）、過程（PROCEDURE）、觸發(fā)器（TRIGGER）等的命名約定。1.基本命名原則(1)規(guī)則1:命名使用具有意義的英文詞匯，詞匯中間以下劃線分隔。(2)規(guī)則2:命名只能使用英文字母，數(shù)字，下劃線，并以英文字母開頭。(3)規(guī)則3:避免用ORACLE、MySQL的保留字如desc，關(guān)鍵字如index。2.表命名(1)規(guī)則1:同一個模塊的表盡可能使用相同的前綴，表名稱盡可能表達含義。(2)規(guī)則2:長度不超過25個字符。3.字段命名(1)規(guī)則1:表達其實際含義的英文單詞或簡寫。MySQL：布爾意義的字段以“is_”作為前綴，后接動詞過去分詞。(2)規(guī)則2:各表之間相同意義的字段應(yīng)同名。MySQL：各表之間相同意義的字段，以去掉模塊前綴的表名_字段名命名。(3)規(guī)則3:外鍵字段用表名_字段名表示其關(guān)聯(lián)關(guān)系。MySQL：禁止使用外鍵。示例：log_prepay.id=log_account.prepay_id示例（MySQL）：字段prepay_group.id被prepay_contact引用；prepay_contact表中與之對應(yīng)的字段命名為：group_id4.索引命名(1)規(guī)則1:表名稱_字段名_INDMySQL：idx_表名稱_字段名示例：employee表first_name上的index：employee_fid_ind示例（MySQL）：prepay表agtphone_id和stream_id上的index：idx_prepay_aid_sid5.約束命名(1)規(guī)則1:主鍵約束：表名稱_PK。(2)規(guī)則2:唯一MySQL：除主鍵外，需存在唯一但應(yīng)用中需要同時有唯一性檢查邏輯。約束：表名稱_字段名_UK。性約束的，可通過創(chuàng)建以“uk_”為前綴的唯一索引實現(xiàn)，6.觸發(fā)器命名(1)規(guī)則1:TRG_表名_操作。7.函數(shù)過程命名(1)規(guī)則1:采用動詞+名詞的形式表達其含義。8.序列命名(1)規(guī)則1:SEQ_表名。二、對象設(shè)計使用規(guī)范1.常用數(shù)據(jù)類型說明：以下為常用數(shù)據(jù)類型介紹，表設(shè)計時根據(jù)實際應(yīng)用選用。(1)NUMBER(p,s):固定精度的精確數(shù)字類型，整數(shù)或定點數(shù)使用此類型。(2)NUMBER:不固定精度的精確數(shù)字類型，不確定數(shù)值精度，主鍵或浮點數(shù)使用此類型。(3)DATE:僅需要精確到秒時，選擇DATE而不是TIMESTAMP類型。(4)TIMESTAMP:擴展日期類型，僅當需要更高時間精度時使用此類型。(5)VARCHAR2:變長字符串，最長4000個字節(jié)。(6)CHAR:定長字符串，只允許使用(7)CLOB:當超過4000字節(jié)時使用，CLOB字段必須拆分到一張獨立的表中，用主鍵與主表關(guān)聯(lián)。clob字段，建議不要使用，而用多個varchar2(4000)代替，因為clob字段更新(8)BLOB,RAW,LONG:不允許使用。MySQL中，僅可以使用下文提及的數(shù)據(jù)類型，所有表的注釋。(1)DECIMAL(M,D):當表適合用來表示貨幣等精度高的數(shù)據(jù)。(2)INT系列:所有整數(shù)類型字段使用INT（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）,根據(jù)所大小選擇合適的子類型，且所有INT類型存放的數(shù)據(jù)都不使用長度限制。(3)CHAR:當且僅當字段存儲單個字符的情況下，可使用CHAR(1)類型。超過一個字符的CHAR(1)。時，空間不重用。、字段必須添加能夠清楚表示其含義狀態(tài)類字段的注釋中必須明確列出各狀態(tài)值的說明。示定點小數(shù)的情況下使用該類型。定點數(shù)在MySQL內(nèi)部以字符串形式存儲，比浮點數(shù)更精確，字段，使用可變長度類型。(4)VARCHAR:所有可變長度的字段別、狀態(tài)等），均使用VARCHAR類型段長度超過4000時，須和原表進行分拆，與(5)TEXT系列:僅當需能超過20000時，使用TEXT系列類型MEDIUMTEXT、LONGTEXT）。并和原表進行分拆，與原表主鍵組成新表MySQL數(shù)據(jù)庫都會使用UTF8字符VARCHAR最大只能存放64K數(shù)據(jù)。集，而(6)DATE:只需要精確到天的字段使用DATE類型。精確到“天”的取當前時期的操作使用CURDATE()函數(shù)實現(xiàn)。(7)DATETIME:需要精確到時間的取當前時間的操作使用NOW()函數(shù)實2.表設(shè)計約定(1)規(guī)則1:表必須要有主鍵。(2)規(guī)則2:一個字段只表(3)規(guī)則3:總是包含兩個日期字段該包含有額外的業(yè)務(wù)邏輯。(4)規(guī)則4:MySQL中，ll_create、ll_modified使用(5)規(guī)則5:禁止使用復(fù)雜數(shù)據(jù)類型（數(shù)組，自定義類型等）。(6)規(guī)則6:MySQL中，附屬表拆分后，附屬表id與主表id保持一致。不允許均使用顯表現(xiàn)其意義的字符串。當原表主鍵組成新表VARCHAR類型，對于有限類別的字段（如性存儲能明VARCHAR字存儲。存儲的字節(jié)數(shù)可（TEXT、存儲。因為所有（時、分、秒）的字段使用DATETIME類型。精確到“秒”現(xiàn)。示一個含義。:ll_create(創(chuàng)建日期),ll_modified(修改日期)，且這兩個字段不應(yīng)DATETIME類型。在附屬表新增主鍵字段。(7)規(guī)則7:MySQL中，存在過期概念的表，在其設(shè)計之初就必須有過期機制，且有明確的過期時間。過期數(shù)據(jù)必須遷移至歷史表中。(8)規(guī)則8:MySQL中，不再使用的表，必須通知DBA予以更名歸檔。(9)規(guī)則9:MySQL中，線上表中若有不再使用的字段，為保證數(shù)據(jù)完整，禁止刪除。3.約束使用約定(1)規(guī)則1:主鍵必須是無意義的，由序列或主鍵生成器(MySQL中,主鍵由主鍵生成器或自增長字段產(chǎn)生)產(chǎn)生，禁止使用組合主鍵。(2)規(guī)則2:表中除主鍵以外還存在唯一約束的，必須在數(shù)據(jù)庫中創(chuàng)建唯一約束。(3)規(guī)則3:主鍵字段不允許更新。(4)規(guī)則4:不創(chuàng)建外鍵約束，外鍵約束由應(yīng)用控制。4.索引設(shè)計約定(1)規(guī)則1:不使用Bitmap類型的索引。(2)規(guī)則(3)規(guī)則3:索引字段選擇注意事項：①選擇在where子句中出現(xiàn)，且選擇性和過濾性高的字段創(chuàng)建索引。2:不創(chuàng)建unique類型的索引。②用來和其他表關(guān)聯(lián)的字段，如果使用nestloopjoin，建議創(chuàng)建索引。③索引的首字段，必須在where條件中。④復(fù)合索引，將選擇性高的字段放在前面。(4)規(guī)則4:索引使用注意事項：①考慮DML操作(insert，delete，update)導(dǎo)致索引維護成本。②在索引字段上施加函數(shù)或者隱式類型轉(zhuǎn)換會導(dǎo)致索引失效。③查詢結(jié)果集（Cardinality）比較小的來判斷isnull或者isnotnull。5.過程，觸發(fā)器，視圖使用原則(1)規(guī)則1：避免將業(yè)務(wù)邏輯封裝在數(shù)據(jù)庫procedure和function，業(yè)務(wù)邏輯應(yīng)該用程序?qū)崿F(xiàn)。(2)規(guī)則2：不允許使用trigger實現(xiàn)業(yè)務(wù)邏輯。增量數(shù)據(jù)同步和記錄數(shù)據(jù)變化除外，比如搜索引擎增量build，易百米同步等。(3)規(guī)則3:不允許view與table關(guān)聯(lián)。情況下，適用索引查詢。④索引不能用procedure/function中，不允許應(yīng)用調(diào)用數(shù)據(jù)庫中的(4)規(guī)則4:MySQL，禁止procedure或function或任何業(yè)務(wù)邏輯通過封裝在數(shù)據(jù)庫中的trigger實現(xiàn)。(5)規(guī)則5:MySQL，禁止應(yīng)用程序使用view。(6)規(guī)則(7)規(guī)則7:使用SVN來管理這些腳本。6.DB_LINK使用規(guī)(1)規(guī)則1：只有DBA擁有dblink使用權(quán)限，不允許應(yīng)用(2)規(guī)則2：DB_LINK只讀，不允許通過DB_LINK修改數(shù)據(jù)。數(shù)據(jù)7.物化視圖使用規(guī)(1)規(guī)則1因oracle內(nèi)部bug，故規(guī)定6:MySQL，禁止業(yè)務(wù)邏輯使用計劃任。范調(diào)用dblink。倉庫同步除外。范物化視圖內(nèi)不允許使用多表join。三、SQL編寫規(guī)范1.基本SQL編寫規(guī)范(1)規(guī)則1:SQL中直接使用表名，不使用schema作為前綴，跨schema訪問需要創(chuàng)建synonym（數(shù)據(jù)(2)規(guī)則2:必須使用綁定變量,避免常量的直接引用。說明：頻繁的硬解析會影響數(shù)據(jù)庫(3)規(guī)則3:避免在where子句中對索引字段倉庫，CRM數(shù)據(jù)庫不適用）。性能。施加函數(shù)，這樣將導(dǎo)致索引失效。示例：錯誤的寫法：selectto_char(hire_date,'yyyymmdd')='20120601'；employee_id,first_namefromemployeeemployeewherewhere正確的寫法：selectemployee_id,first_namefromhire_date=to_char('20120601','yyyymmdd');(4)規(guī)則4:Like不允許使用全模糊，只允許使用右模糊查詢。說明：全模糊查詢無法使用INDEX，可能造成性能問題。示例：全模糊查詢：selectemployee_id,first_namefromemployeewherenamelike'%joe%';右模糊查詢：selectemployee_id,first_namefromemployeewherenamelike'%joe%';(5)規(guī)則5:如果使用Oracle數(shù)據(jù)庫，使用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)規(guī)則6:Insert語句必須寫(7)規(guī)則7:嚴格要求使用正確書寫SQL時，上字段名。類型的變量，杜絕隱式類型轉(zhuǎn)換。必須確定表中各個字段的數(shù)據(jù)類型，防止發(fā)生隱式類型轉(zhuǎn)換。示例：ID是NUMBER型錯誤的寫法：Selectemployee_id,first_namefromemployeewhereemployee_id='123';--這里會造成oracle將id先轉(zhuǎn)換成varchar類型，導(dǎo)致索引失效。正確的寫法：Selectemployee_id,first_namefromemployeewhereemployee_id=123;反之亦然，字符型數(shù)據(jù)一定加上單引號。時間類型的字段，必須使用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)規(guī)則2.select語句中(1)規(guī)則1:盡量不要使用select*。8:MySQL，任何過濾字段值必須加上單引號。*號的問題(2)規(guī)則2:表連接時，不允許使用select*。(3)規(guī)則3:單表查詢，允許使用select*，但以下幾種情況禁用：①表中包含clob字段。②表中包含長度較大的字段，比如varchar2(2000)以上的字段，但要取出該字段的值。(4)規(guī)則4:MySQL中，當結(jié)果集中“select*”取出所有字段。5:MySQL中，連接查詢中，禁“select*”。3.NULL的使用說明:NULL的含義是"不確定"，而不是"空"(1)規(guī)則1:查詢時，使用isnull或者isnotnull(2)規(guī)則2:更新時，使用該SQL實際上并不需無需列出被查詢表的所有字段，且查詢中存在“orderby”語句時。禁止使用(5)規(guī)則止使用等于號。示例：updatetablenamesetcolumn_name=null；4.聚合函數(shù)常見問題(1