MySQL數(shù)據(jù)庫開發(fā)規(guī)范

1、封面作者：PanHongliang平安金融科技數(shù)據(jù)庫（MySQL ）開發(fā)規(guī)范作者:簡朝陽Last Updated: 25/02/1419:30:18歷史修訂記錄:版本修訂人修訂時(shí)間修訂內(nèi)容僅供個(gè)人學(xué)習(xí)1.01.1李海軍2013-03-11增加部分說明及修改1.2李海軍2013-07-29增加連接池使用說明和 memory 引擎的控制1.3李海軍2014-02-25增加了 char 類型，修改了 timestamp 的使 用場合。說明本規(guī)范包含平安金融科技使用MySQL數(shù)據(jù)庫時(shí)所需要遵循的所有對象設(shè)計(jì)（數(shù)據(jù)庫，表，字段），所需要遵循的命名，對象設(shè)計(jì)，SQL編寫等的規(guī)范約定。所有內(nèi)容都為必須嚴(yán)格執(zhí)

2、行的工程，執(zhí)行過程中有任何疑問，請聯(lián)系DBA Team取得幫助。概述禁止明文傳播數(shù)據(jù)庫帳號和密碼。禁止開發(fā)工程師通過應(yīng)用帳號登錄生產(chǎn)數(shù)據(jù)庫。禁止應(yīng)用在服務(wù)器安裝MySQL客戶端（可以安裝開發(fā)包）。禁止開發(fā)人員在SQL中添加Hint，Hint只能由DBA審核后添加禁止使用悲觀鎖定，即讀鎖selectfor update禁止在開發(fā)代碼中使用DDL語句，比如truncate,alter table等。禁止DML語句的where條件中包含恒真條件（如：1=1）。1.命名規(guī)范總則數(shù)據(jù)庫對象名僅可包含小寫英文字母、數(shù)字、下劃線（_）三類字符，并以英文字母開頭。數(shù)據(jù)庫對象命名禁止使用MySQL保留字。多個(gè)單

3、詞之間用下劃線（_）分隔。對象名稱長度若超過限制，則使用簡寫/縮寫命名。1.1.數(shù)據(jù)庫命名數(shù)據(jù)庫以db_前綴+ 站點(diǎn)名_前綴及其所服務(wù)的應(yīng)用名稱命名。1.2.表命名所屬同一模塊的表必須以模塊名作為前綴命名。歷史數(shù)據(jù)表在原表基礎(chǔ)上增加_his后綴命名。1.3.字段命名布爾意義的字段以_flag作為后綴，前接動(dòng)詞。如：表示邏輯刪除意義的字段可命名為delete_flag。各表間相同意義的字段（如：作為連接關(guān)系的引用字段）使用相同的字段名。order_item表中與之對應(yīng)的字段命名必須為：order_id1.4.索引命名示例站點(diǎn)名：maymay模塊名：order；數(shù)據(jù)表：item；字段組成：orde

4、r_item_id,add_time,raw_update_time,c1,c2,c3,c4,c5標(biāo)準(zhǔn)數(shù)據(jù)庫名：db_maymay_order；標(biāo)準(zhǔn)數(shù)據(jù)表名：order_item；歷史數(shù)據(jù)表名：order_item_his；索引需求：c1唯一，c2和c3組合索引：uk_order_item_c1, idx_order_item_c2_c3字段實(shí)際意義：是否已刪除；標(biāo)準(zhǔn)字段名：delete_flag；字段order.order_id被order_item引用；唯一索引以uk_tablename_columnnames方式命名普通索引以idx_tablename_columnnames方式命名組合

5、索引以idx_tablename_column1_column2.方式命名2.對象設(shè)計(jì)規(guī)范總則所有表、字段必須添加能夠清楚表示其含義的注釋。狀態(tài)類字段的注釋中必須明確列 出各狀態(tài)值的說明。MySQL數(shù)據(jù)庫中僅可以使用下文提及的數(shù)據(jù)類型。2.1.數(shù)據(jù)類型2.1.1.數(shù)值類型DECIMAL（M,D）當(dāng)表示定點(diǎn)小數(shù)的情況下使用該類型，禁止使用浮點(diǎn)類型，會(huì)帶來不精確。定點(diǎn)數(shù)在MySQL內(nèi)部以字符串形式存儲，比浮點(diǎn)數(shù)更精確，適合用來表示貨幣等精度高的數(shù)據(jù)。INT系列所有整數(shù)類型字段使用INT（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT） 根據(jù)所存放的數(shù)據(jù)大小選擇合適的子類

6、型，且所有INT類型都不使用長度限制。2.1.2.字符串類型VARCHAR所有可變長度的字段均使用VARCHAR類型，對于有限類別的字段（如性別、狀態(tài)等），均建議使用VARCHAR類型存儲能明顯表現(xiàn)其意義的字符串。TEXT系列 僅當(dāng)需存儲的字節(jié)數(shù)可能超過20000時(shí)，使用TEXT系列類型(TEXT、MEDIUMTEXT、LONGTEXT)。并和原表進(jìn)行分拆，與原表主鍵組成新表存儲，且每 個(gè)表只允許有一個(gè)TEXT系列類型字段。CHAR僅當(dāng)字段確定為定長，且將來不會(huì)修改長度時(shí)，使用CHAR類型。上線以后不允許修 改字段類型。謹(jǐn)慎使用2.1.3.時(shí)間類型DATE只需要精確到天的字段使用DATE類型。

7、精確到天的取當(dāng)前時(shí)期的操作使用CURDATE()函數(shù)實(shí)現(xiàn)。-DATETIME需要精確到時(shí)間(時(shí)、分、秒)的字段使用DATETIME類型。精確到秒的取當(dāng)前時(shí) 間的操作使用NOW()函數(shù)實(shí)現(xiàn)。取值范圍：1000-01-01到9999-12-31 TIMESTAMP該類型僅允許raw_update_time字段使用，其它字段不允許使用該類型。取值范圍：1970-01-01 00:00:00到2037-01-01 00:00:002.2.表設(shè)計(jì)必含字段id INT：主鍵使用MySQL的自增類型raw_add_time DATETIME：創(chuàng)建日期（大字段拆分表除外），必須使用數(shù)據(jù)庫時(shí)間（用now（）生成

8、）raw_update_time TIMESTAMP：修改日期（大字段拆分表除外,但內(nèi)容變化必須修改主表的update_time字段），由數(shù)據(jù)庫自動(dòng)變更，應(yīng)用不操作此字段以上3個(gè)字段都必須是沒有任何商業(yè)意義的與業(yè)務(wù)無關(guān)的字段，不允許賦予任何商業(yè)意義作為表間連接關(guān)系的字段，數(shù)據(jù)類型必須保持嚴(yán)格一致，避免索引無法正常使用。附屬表拆分后，附屬表關(guān)聯(lián)字段使用主表主鍵字段，且附屬表須有獨(dú)立主鍵（大字段 拆分的表不需要單獨(dú)的主鍵）存在過期概念的表，在其設(shè)計(jì)之初就必須有過期機(jī)制，且有明確的過期時(shí)間。過期數(shù) 據(jù)必須遷移至歷史表中。不再使用的表，必須通知DBA予以更名歸檔。 線上表中若有不再使用的字段，為保證數(shù)

9、據(jù)完整，禁止刪除，而是進(jìn)行更名歸檔，名 稱統(tǒng)一增加droped_前綴2.3.約束使用2.3.1.主鍵主鍵不能包含業(yè)務(wù)含義。主鍵在任何情況下不允許被更新。2.3.2.唯一約束2.3.3.外鍵任何情況不在數(shù)據(jù)庫創(chuàng)建外鍵約束，外鍵規(guī)則由應(yīng)用控制。除主鍵外，需存在唯一性約束的，可通過創(chuàng)建以uk_為前綴的唯一索引實(shí)現(xiàn)2.3.4.非空列所有非空列須在建表之初明確標(biāo)識NOT NULL,上線之后，不允許再變更。2.3.5.存儲過程、觸發(fā)器、視圖、計(jì)劃任務(wù)禁止任何業(yè)務(wù)邏輯通過封裝在數(shù)據(jù)庫中的procedure/function/trigger實(shí)現(xiàn)。 禁止應(yīng)用程序使用view。禁止業(yè)務(wù)邏輯使用數(shù)據(jù)庫的計(jì)劃任務(wù)。3

10、. SQL編寫規(guī)范3.1.綁定變量與替代變量原則所有Query的Where條件中的變量，都需要使用綁定變量來實(shí)現(xiàn)，此要求并不完全是基于性 能的考慮，更多是基于安全方面的考慮。3.2.數(shù)據(jù)類型轉(zhuǎn)換原則 避免因數(shù)據(jù)類型轉(zhuǎn)換導(dǎo)致執(zhí)行計(jì)劃有誤。說明where條件中的過濾字段如需轉(zhuǎn)換類型，只可轉(zhuǎn)換過濾值，不可轉(zhuǎn)換被過濾字段。表連接操作中，作為連接條件的字段的數(shù)據(jù)類型嚴(yán)格一致。如果表連接字段數(shù)據(jù)類型不一致，在SQL中用顯示用類型轉(zhuǎn)換，具體情況咨詢DBA。示例正確用法1：select col1, col2 from tbl1, tbl2where tbl1.col3 = tbl2.col4。其中tbl1.c

11、ol3與tbl2.col4數(shù)據(jù)類型嚴(yán)格一致。正確用法2：select col1，col2 from tblwhere gmt_create = str_to_date(20100526 00:00:00,%Y%m%d %H:%i:%s)。count()使用除非是明確目的是統(tǒng)計(jì)某個(gè)字段上值不為空的記錄的數(shù)目，否者只允許之用count(*),使用count(column_name)或者count(1)。3.3. select * from使用原則為避免查詢中無用字段參與排序操作而導(dǎo)致的性能降低及潛在的安全隱患。禁止使用from而不允許select *I!說明 任何情況都要明確列出查詢需要返回的字段

12、，禁止使用select *返回所有字段。3.4. insert intotablename values()使用原則為避免增加或刪除字段帶來的SQL報(bào)錯(cuò)。禁止使用省略字段名的insert into語句 說明任何情況都要明確列出需要寫入的字段名稱。3.5.表連接原則規(guī)范連接語法以方便SQL腳本的閱讀及提升連接操作性能。說明非外連接查詢中，連接表在from子句中列出，并以逗號分隔；連接條件在 子句中列岀，而不允許使用joinon方式實(shí)0現(xiàn)。外連接查詢中，可使用left joinon語法；外連接一律使用left join可以改寫為連接的子查詢禁止使用子查詢方式，而應(yīng)改寫為連接方式。示例規(guī)范selec

13、t col1, col2 from tbl1, tbl2 where tbl1.col4 = tbl2.col3 select col1, col2 from tbl1 left outer join tbl2on tbl1.col4 = tbl2.col3select col1, col2 from tbl1 left outer join tbl2 on tbl1.col4 = tbl2.col3。where表示。select col1 from tbl1,tbl2 where tbl1.col2 = tbl2.col3。select col1 from tbl1 left join tb

14、l2 on tbl1.col2 = tbl2.col3 where tbl2.col3 isnull。非規(guī)范：select col1, col2 from tbl1 join tbl2 on tbl1.col4 = tbl2.col3。select col1 from tbl1 where col2 in (select col3 from tbl2)。select col1 from tbl1 where col2 not in (select col3 from tbl2)。3.6.分頁查詢說明分頁查詢必須帶有唯一的排序條件，除非業(yè)務(wù)邏輯明確要求隨機(jī)展現(xiàn)數(shù)據(jù)。對于多表 連接的分頁語句，如果

15、過濾條件在單個(gè)表上，則先分頁，后連接。見示例通過limit進(jìn)行分頁操作的時(shí)候，limit參數(shù)為start(起始記錄數(shù))，page_offset(每頁記錄數(shù))。示例規(guī)范化用法：select a.col1, a.col2，b.col1, b.col2 from (select t.col1, t.col2 from tbll t where t.col3 = exp order byt.col4 limit start, page_offerset) a,tbl2 b where a.col3 = b.col34.數(shù)據(jù)庫級別規(guī)范4.1連接池的使用應(yīng)用程序連接數(shù)據(jù)庫必須使用連接池。4.2存儲引擎的選

16、擇盡量不要使用 memory 引擎的表，除非能把握好對內(nèi)存表的控制。版權(quán)申明本文部分內(nèi)容，包括文字、圖片、以及設(shè)計(jì)等在網(wǎng)上搜集整 理。版權(quán)為潘宏亮個(gè)人所有This article includes some parts, including text, pictures, and desig n.Copyright is Pan Hon glia ngs pers onal own ership.用戶可將本文的內(nèi)容或服務(wù)用于個(gè)人學(xué)習(xí)、研究或欣賞，以及 其他非商業(yè)性或非盈利性用途，但同時(shí)應(yīng)遵守著作權(quán)法及其他相關(guān) 法律的規(guī)定，不得侵犯本網(wǎng)站及相關(guān)權(quán)利人的合法權(quán)利。除此以 外，將本文任何內(nèi)容或服務(wù)用

17、于其他用途時(shí)，須征得本人及相關(guān)權(quán) 利人的書面許可，并支付報(bào)酬。Users may use the contents or services of this article for pers onalstudy, research or appreciati on, and other non-commercial or non-profitpurposes, but at the same time, they shall abide by the provisions ofcopyright law and other releva nt laws, and shall not infrin

18、ge upon thelegitimate rights of this website and its releva nt obligees. In addition,when any content or service of this article is used for other purposes,written permission and remun erati on shall be obta ined from the pers onconcerned and the releva nt obligee.轉(zhuǎn)載或引用本文內(nèi)容必須是以新聞性或資料性公共免費(fèi)信息為 使用目的的合理、善意引用，不得對本文內(nèi)容原意進(jìn)行曲解、修 改，并自負(fù)版權(quán)等法律責(zé)任。Reproducti on or quota