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

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

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

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

4、mnnames 方式命名 組合索引以 idx_tablename_column1_column2. 方式命名示例 站點名：maymay 模塊名：order ； 數(shù)據(jù)表：item； 字段組成：order_item_id,add_time,raw_update_time,c1,c2,c3,c4,c5 標準數(shù)據(jù)庫名：db_maymay_order； 標準數(shù)據(jù)表名：order_item； 歷史數(shù)據(jù)表名：order_item_his； 索引需求：c1唯一，c2和c3 組合索引：uk_order_item_c1, idx_order_item_c2_c3 字段實際意義：是否已刪除； 標準字段名：delet

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

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

7、修改字段類型。謹慎使用2.1.3. 時間類型 DATE只需要精確到天的字段使用DATE類型。精確到天的取當前時期的操作使用CURDATE()函數(shù)實現(xiàn)。 -DATETIME需要精確到時間（時、分、秒）的字段使用DATETIME類型。精確到秒的取當前時間的操作使用NOW()函數(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. 表設計 必含字段id INT：主鍵使用MySQL的自增類型raw_add_

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

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

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

11、：select col1, col2 from tbl1, tbl2where tbl1.col3 = tbl2.col4;其中tbl1.col3與tbl2.col4數(shù)據(jù)類型嚴格一致。正確用法2：select col1，col2 from tblwhere gmt_create = str_to_date(20100526 00:00:00,%Y%m%d %H:%i:%s);count()使用除非是明確目的是統(tǒng)計某個字段上值不為空的記錄的數(shù)目，否者只允許之用 count(*),而不允許使用count(column_name) 或者 count(1)。3.3. select * from 使用原

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

13、實現(xiàn)join。 外連接查詢中，可使用left join on語法；外連接一律使用left join表示。 可以改寫為連接的子查詢禁止使用子查詢方式，而應改寫為連接方式。示例 規(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è)務邏輯明確要求隨機展現(xiàn)數(shù)據(jù)。對于多表連接的分頁語句，如果過濾條件在單個表上，則先分頁，后連接。見示例 通過limit進行分頁操作的時候，limit參數(shù)為start（起始記錄數(shù)），page_offset(每頁記錄數(shù))。示例 規(guī)范化用法：select a.col1