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

1、平安金融科技 數(shù)據(jù)庫(kù)（MySQL）開發(fā)規(guī)范作者: 簡(jiǎn)朝陽(yáng)Last Updated: 25/02/14 19:30:18歷史修訂記錄：版本修訂人修訂時(shí)間修訂內(nèi)容1.01.1李海軍2013-03-11增加部分說(shuō)明及修改1.2李海軍2013-07-29增加連接池使用說(shuō)明和memory引擎的控制1.3 李海軍2014-02-25增加了char類型，修改了timestamp的使用場(chǎng)合。說(shuō)明 本規(guī)范包含 平安金融科技 使用 MySQL 數(shù)據(jù)庫(kù)時(shí)所需要遵循的所有對(duì)象設(shè)計(jì)（數(shù)據(jù)庫(kù)，表，字段），所需要遵循的命名，對(duì)象設(shè)計(jì)，SQL 編寫等的規(guī)范約定。 所有內(nèi)容都為必須嚴(yán)格執(zhí)行的項(xiàng)目，執(zhí)行過程中有任何疑問，請(qǐng)聯(lián)系

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

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

4、mnnames 方式命名 組合索引以 idx_tablename_column1_column2. 方式命名示例 站點(diǎn)名：maymay 模塊名：order ； 數(shù)據(jù)表：item； 字段組成：order_item_id,add_time,raw_update_time,c1,c2,c3,c4,c5 標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)名：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)字段名：delet

5、e_flag； 字段 order.order_id被order_item引用； order_item 表中與之對(duì)應(yīng)的字段命名必須為：order_id2. 對(duì)象設(shè)計(jì)規(guī)范總則 所有表、字段必須添加能夠清楚表示其含義的注釋。狀態(tài)類字段的注釋中必須明確列出各狀態(tài)值的說(shuō)明。 MySQL數(shù)據(jù)庫(kù)中僅可以使用下文提及的數(shù)據(jù)類型。2.1. 數(shù)據(jù)類型2.1.1. 數(shù)值類型 DECIMAL(M,D)當(dāng)表示定點(diǎn)小數(shù)的情況下使用該類型，禁止使用浮點(diǎn)類型，會(huì)帶來(lái)不精確。定點(diǎn)數(shù)在MySQL內(nèi)部以字符串形式存儲(chǔ)，比浮點(diǎn)數(shù)更精確，適合用來(lái)表示貨幣等精度高的數(shù)據(jù)。 INT系列所有整數(shù)類型字段使用INT（TINYINT、SMALL

6、INT、MEDIUMINT、INT、BIGINT）,根據(jù)所存放的數(shù)據(jù)大小選擇合適的子類型，且所有INT類型都不使用長(zhǎng)度限制。2.1.2. 字符串類型 VARCHAR所有可變長(zhǎng)度的字段均使用VARCHAR類型，對(duì)于有限類別的字段（如性別、狀態(tài)等），均建議使用VARCHAR類型存儲(chǔ)能明顯表現(xiàn)其意義的字符串。 TEXT系列僅當(dāng)需存儲(chǔ)的字節(jié)數(shù)可能超過20000時(shí)，使用TEXT系列類型（TEXT、MEDIUMTEXT、LONGTEXT）。并和原表進(jìn)行分拆，與原表主鍵組成新表存儲(chǔ)，且每個(gè)表只允許有一個(gè)TEXT系列類型字段。 CHAR僅當(dāng)字段確定為定長(zhǎng)，且將來(lái)不會(huì)修改長(zhǎng)度時(shí)，使用CHAR類型。上線以后不允許

7、修改字段類型。謹(jǐn)慎使用2.1.3. 時(shí)間類型 DATE只需要精確到天的字段使用DATE類型。精確到天的取當(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-31TIMESTAMP該類型僅允許raw_update_time字段使用，其它字段不允許使用該類型。取值范圍：1970-01-01 00:00:00到2037-01-01 00:00:002.2. 表設(shè)計(jì) 必含字段id INT：主鍵使用MySQL的自增類型raw_add_

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

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

10、的procedure/function/trigger實(shí)現(xiàn)。 禁止應(yīng)用程序使用view。 禁止業(yè)務(wù)邏輯使用數(shù)據(jù)庫(kù)的計(jì)劃任務(wù)。3. SQL編寫規(guī)范3.1. 綁定變量與替代變量原則所有 Query 的 Where 條件中的變量，都需要使用綁定變量來(lái)實(shí)現(xiàn)，此要求并不完全是基于性能的考慮，更多是基于安全方面的考慮。3.2. 數(shù)據(jù)類型轉(zhuǎn)換原則避免因數(shù)據(jù)類型轉(zhuǎn)換導(dǎo)致執(zhí)行計(jì)劃有誤。說(shuō)明 where條件中的過濾字段如需轉(zhuǎn)換類型，只可轉(zhuǎn)換過濾值，不可轉(zhuǎn)換被過濾字段。 表連接操作中，作為連接條件的字段的數(shù)據(jù)類型嚴(yán)格一致。 如果表連接字段數(shù)據(jù)類型不一致，在SQL中用顯示用類型轉(zhuǎn)換，具體情況咨詢DBA。示例正確用法1

11、：select col1, col2 from tbl1, tbl2where tbl1.col3 = tbl2.col4;其中tbl1.col3與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 使用原

12、則為避免查詢中無(wú)用字段參與排序操作而導(dǎo)致的性能降低及潛在的安全隱患。禁止使用select * from 。說(shuō)明任何情況都要明確列出查詢需要返回的字段，禁止使用select * 返回所有字段。3.4. insert into tablename values()使用原則為避免增加或刪除字段帶來(lái)的SQL報(bào)錯(cuò)。禁止使用省略字段名的insert into語(yǔ)句。說(shuō)明任何情況都要明確列出需要寫入的字段名稱。3.5. 表連接原則規(guī)范連接語(yǔ)法以方便SQL腳本的閱讀及提升連接操作性能。說(shuō)明 非外連接查詢中，連接表在from子句中列出，并以逗號(hào)分隔；連接條件在where子句中列出，而不允許使用join on 方式

13、實(shí)現(xiàn)join。 外連接查詢中，可使用left join on語(yǔ)法；外連接一律使用left join表示。 可以改寫為連接的子查詢禁止使用子查詢方式，而應(yīng)改寫為連接方式。示例 規(guī)范select col1, col2 from tbl1, tbl2 where tbl1.col4 = tbl2.col3;select col1, col2 from tbl1 left outer join tbl2 on tbl1.col4 = tbl2.col3;select col1, col2 from tbl1 left outer join tbl2 on tbl1.col4 = tbl2.col3;s

14、elect col1 from tbl1,tbl2 where tbl1.col2 = tbl2.col3;select col1 from tbl1 left join tbl2 on tbl1.col2 = tbl2.col3 where tbl2.col3 is null; 非規(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è)查詢說(shuō)明 分頁(yè)查詢必須帶有唯一的排序條件，除非業(yè)務(wù)邏輯明確要求隨機(jī)展現(xiàn)數(shù)據(jù)。對(duì)于多表連接的分頁(yè)語(yǔ)句，如果過濾條件在單個(gè)表上，則先分頁(yè)，后連接。見示例 通過limit進(jìn)行分頁(yè)操作的時(shí)候，limit參數(shù)為start（起始記錄數(shù)），page_offset(每頁(yè)記錄數(shù))。示例 規(guī)范化用法：select a.col1