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

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