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

1、精心整理平安金融科技數(shù)據(jù)庫(kù)（ mysql ）開發(fā)規(guī)范作者:簡(jiǎn)朝陽(yáng)lastupdated:25/02/1419:30:18 歷史修訂記錄：版本修訂人修訂時(shí)間修訂內(nèi)容1.0 1.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í)行過(guò)程中有任何疑問(wèn)，

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

3、_）分隔。對(duì)象名稱長(zhǎng)度若超過(guò)限制，則使用簡(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. 索引命名唯一索引以u(píng)k_tablename_columnnames方式命名普通索引以idx_tablename_columnnames方式命名

4、組合索引以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)字段名： delete_flag；字段 o

5、rder.order_id被 order_item引用；order_item表中與之對(duì)應(yīng)的字段命名必須為：order_id 2.對(duì)象設(shè)計(jì)規(guī)范總則所有表、字段必須添加能夠清楚表示其含義的注釋。狀態(tài)類字段的注釋中必須明確列出各狀態(tài)值的說(shuō)明。mysql數(shù)據(jù)庫(kù)中僅可以使用下文提及的數(shù)據(jù)類型。精心整理2.1. 數(shù)據(jù)類型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 、 smallint 、mediumint、int

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

7、確到天的字段使用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-31 timestamp 該類型僅允許raw_update_time字段使用，其它字段不允許使用該類型。取值范圍：1970-01-0100:00:00到2037-01-0100:00:00 2.2. 表設(shè)計(jì)必含字段idint ：主鍵使用mysql的自增類型raw_add_timedatetime：創(chuàng)建日期（大字段拆分表除外

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

9、以更名歸檔。線上表中若有不再使用的字段，為保證數(shù)據(jù)完整，禁止刪除，而是進(jìn)行更名歸檔，名稱統(tǒng)一增加droped_前綴2.3. 約束使用主鍵不能包含業(yè)務(wù)含義。主鍵在任何情況下不允許被更新。除主鍵外，需存在唯一性約束的，可通過(guò)創(chuàng)建以u(píng)k_ 為前綴的唯一索引實(shí)現(xiàn)。任何情況不在數(shù)據(jù)庫(kù)創(chuàng)建外鍵約束，外鍵規(guī)則由應(yīng)用控制。所有非空列須在建表之初明確標(biāo)識(shí)notnull,上線之后，不允許再變更。禁止任何業(yè)務(wù)邏輯通過(guò)封裝在數(shù)據(jù)庫(kù)中的procedure/function/trigger實(shí)現(xiàn)。禁止應(yīng)用程序使用view 。禁止業(yè)務(wù)邏輯使用數(shù)據(jù)庫(kù)的計(jì)劃任務(wù)。3.sql 編寫規(guī)范3.1. 綁定變量與替代變量原則所有 quer

10、y的 where條件中的變量，都需要使用綁定變量來(lái)實(shí)現(xiàn)，此要求并不完全是基于性能的考慮，更多是基于安全方面的考慮。3.2. 數(shù)據(jù)類型轉(zhuǎn)換原則避免因數(shù)據(jù)類型轉(zhuǎn)換導(dǎo)致執(zhí)行計(jì)劃有誤。說(shuō)明where條件中的過(guò)濾字段如需轉(zhuǎn)換類型，只可轉(zhuǎn)換過(guò)濾值，不可轉(zhuǎn)換被過(guò)濾字段。表連接操作中，作為連接條件的字段的數(shù)據(jù)類型嚴(yán)格一致。如果表連接字段數(shù)據(jù)類型不一致，在sql 中用顯示用類型轉(zhuǎn)換，具體情況咨詢dba 。示例正確用法1：selectcol1,col2fromtbl1,tbl2 精心整理wheretbl1.col3=tbl2.col4; 其中 tbl1.col3與tbl2.col4數(shù)據(jù)類型嚴(yán)格一致。正確用法2：s

11、electcol1，col2fromtbl count( ) 使用除非是明確目的是統(tǒng)計(jì)某個(gè)字段上值不為空的記錄的數(shù)目，否者只允許之用count(*),而不允許使用count(column_name)或者 count(1)。3.3.select*from 使用原則為避免查詢中無(wú)用字段參與排序操作而導(dǎo)致的性能降低及潛在的安全隱患。禁止使用select*from 。說(shuō)明任何情況都要明確列出查詢需要返回的字段，禁止使用select* 返回所有字段。3.4.insertintotablenamevalues()使用原則為避免增加或刪除字段帶來(lái)的sql 報(bào)錯(cuò)。禁止使用省略字段名的insertinto語(yǔ)句。

12、說(shuō)明任何情況都要明確列出需要寫入的字段名稱。3.5. 表連接原則規(guī)范連接語(yǔ)法以方便sql 腳本的閱讀及提升連接操作性能。說(shuō)明非外連接查詢中，連接表在from子句中列出，并以逗號(hào)分隔；連接條件在where子句中列出，而不允許使用 join on 方式實(shí)現(xiàn)join 。外連接查詢中，可使用leftjoinon 語(yǔ)法；外連接一律使用 leftjoin表示?？梢愿膶憺檫B接的子查詢禁止使用子查詢方式，而應(yīng)改寫為連接方式。示例規(guī)范精心整理selectcol1,col2fromtbl1,tbl2wheretbl1.col4=tbl2.col3; selectcol1,col2fromtbl1leftouter

13、jointbl2ontbl1.col4=tbl2.col3; selectcol1,col2fromtbl1leftouterjointbl2ontbl1.col4=tbl2.col3; selectcol1fromtbl1,tbl2wheretbl1.col2=tbl2.col3; selectcol1fromtbl1leftjointbl2ontbl1.col2=tbl2.col3wheretbl2.col3isnull; 非規(guī)范：selectcol1,col2fromtbl1jointbl2ontbl1.col4=tbl2.col3; selectcol1fromtbl1wherecol2in(selectcol3fromtbl2); selectcol1fromtbl1wherecol2notin(selectcol3fromtbl2); 3.6. 分頁(yè)查詢說(shuō)明分頁(yè)查詢必須帶有唯一的排序條件，除非業(yè)務(wù)邏輯明確要求隨機(jī)展現(xiàn)數(shù)據(jù)。對(duì)于多表連接的分頁(yè)語(yǔ)句，如果過(guò)濾條件在單個(gè)表上，則先分頁(yè)，后連接。見示例通過(guò) limit進(jìn)行分頁(yè)操作的時(shí)候，limit參數(shù)為 start （起始記錄數(shù)），page_offset(每頁(yè)記錄數(shù) )。示例規(guī)范化用法：selecta.col1,a