說(shuō)明
- 本文包含使用 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)屈暗，請(qǐng)聯(lián)系 DBA Team 取得幫助。
概述
- 禁止明文傳播數(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審核后添加弃甥。
- 禁止使用悲觀鎖定爽室，即讀鎖 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è)單詞之間用下劃線（_）分隔唱较。
- 對(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. 索引命名
- 唯一索引 以 uk_tablename_columnnames 方式命名
- 普通索引 以 idx_tablename_columnnames 方式命名
- 組合索引 以 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咐蚯；
- 字段 order.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ù)類型
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、SMALLINT弥搞、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ù)可能超過(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)慎使用
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-31'
- TIMESTAMP
該類型僅允許raw_update_time字段使用，其它字段不允許使用該類型虏冻。取值范圍：'1970-01-01 00:00:00'到'2037-01-01 00:00:00'
2.2. 表設(shè)計(jì)
- 必含字段
id INT：主鍵使用MySQL的自增類型
raw_add_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è)字段都必須是沒(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予以更名歸檔。
- 線上表中若有不再使用的字段褥傍，為保證數(shù)據(jù)完整儡嘶，禁止刪除，而是進(jìn)行更名歸檔恍风，名稱統(tǒng)一增加"droped_"前綴
2.3. 約束使用
2.3.1. 主鍵
- 主鍵不能包含業(yè)務(wù)含義蹦狂。
- 主鍵在任何情況下不允許被更新。
2.3.2. 唯一約束
- 除主鍵外朋贬，需存在唯一性約束的凯楔，可通過(guò)創(chuàng)建以"uk_"為前綴的唯一索引實(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ǔ)過(guò)程虐骑、觸發(fā)器、視圖削解、計(jì)劃任務(wù)
- 禁止任何業(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. 綁定變量與替代變量
原則
所有 Query 的 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：
select col1, col2 from tbl1, tbl2
where tbl1.col3 = tbl2.col4;
其中"tbl1.col3"與"tbl2.col4"數(shù)據(jù)類型嚴(yán)格一致。
正確用法2：
select col1嗓奢，col2 from tbl
where 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 …"使用
原則
為避免查詢中無(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 方式實(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;
select 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ǔ)句，如果過(guò)濾條件在單個(gè)表上叫潦，則先分頁(yè)蝇完，后連接。見(jiàn)示例
- 通過(guò)limit進(jìn)行分頁(yè)操作的時(shí)候矗蕊，limit參數(shù)為start（起始記錄數(shù)）短蜕，page_offset(每頁(yè)記錄數(shù))。
示例
- 規(guī)范化用法：
select a.col1, a.col2傻咖，b.col1, b.col2
from (select t.col1, t.col2
from tbl1 t where t.col3 = exp
order by t.col4 limit start, page_offerset) a,tbl2 b
where a.col3 = b.col3;
4. 數(shù)據(jù)庫(kù)級(jí)別規(guī)范
4.1. 連接池的使用
應(yīng)用程序連接數(shù)據(jù)庫(kù)必須使用連接池朋魔。
4.2. 存儲(chǔ)引擎的選擇
盡量不要使用memory引擎的表，除非能把握好對(duì)內(nèi)存表的控制卿操。
