MySQL 資料庫設計初步規格V1.0

 

資料庫設計規格：

1

a)         表名、列名必須有註釋。

b)         命名應使用具意義的英文詞彙或縮寫，多個單字組成的，全部大寫，以"_"分隔開來，只能使用英文字母，數字和底線，不留空格。例如USER_DETALL，不允許使用關鍵字TYPE或STATUS等作為欄位名稱。

c)         命名長度不超過15個字為宜（避免超過20），且應反映資料集所在的業務範圍，或商業功能，如POWER_USER(使用者中心)等。

d)         當欄位類型為枚舉或布林型時，使用 CHAR(1)（或CHAR(2)）類型，填入預設值，狀態欄位的預設值不能為null一般是設定成0或- 1，狀態欄位的描述寫成 comment '團購券狀態：1.已購買；2.已使用；3.退款中；4已退款'。

 

e)         在設計時盡量包含日期欄位：CREATE_DATE(建立日期)，且UPDATE_DATE(更新日期)等。 mysql中對日期約定一種輸入方式，如'2014-12-31 00:00:00.0'

f)          預設值，數位類型值為0，字串預設值為''，日期為'1900-01-01 00:00:00.0'。

g)         主鍵欄位ID用bigint，若create語句中有AUTO_INCREMENT=6653864標示，請取下。

h)         日期欄位的預設值也不能為null，一般設定成1970-12-31 00:00:00.0。

 

i)           手機字段，且郵箱字段等會被擷取的字段不允許為null，預設值為空串’’。數字類型欄位不允許為null預設值為0。

j)           將中字元編碼為utf8，而預設儲存引擎為INNODB

PS：每個表格必須有主鍵字段，必須不允許輸入日期。

1.2 索引設計

1)         普通索引，並以IDX_開頭連接欄位名稱。

2)         值範圍重複的比例較少的，而建立索引字段，例如  CREATE_DATE(輸入時間)欄位；值範圍重複比率的欄位不需要建立索引例如IS_RETURN(是否已退款）欄位。

3)         主鍵欄位不需要建立unique key，且主鍵欄位也不需要單獨建置索引。

4)         WHERE條件後面的經常被詢問的字段，並以索引，例如ORDER_GOODS 表的ORDER_SN(商品編號)等。

5)         範圍欄位不需加上索引，例如SHOP_MALL表的IS_DEL欄位等。

6)         建立索引的字段，且要求且不能有null值，否則會影響索引的效率。

 

1.3表結構範例

建表語句範例：SH   `ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '店鋪ID',

  `SHOP_NAME` VARCHAR(50) DEFAULT '' COMMENT '店舖名稱',

  `LEGAL_PERSON_MOBILE`VAR,1113) 行動裝置？   ` SCORE` BIGINT(20) DEFAULT 0 COMMENT '積分',

  ......

  `MANAGER_NAME` VARCHAR(20) DEFAULT '' COMMENT 'B ` VARCHAR(500) DEFAULT '' COMMENT '店鋪簡介',

  `HAS_WAREHOUSE` CHAR(1) DEFAULT '0' COMMENT '是否有倉庫, 0:沒有; 1：有

  `DESCRIPTION_FIT` DECIMAL(3,1) DEFAULT 0 COMMENT '描述相符--透過對所有訂單商品的評估計算平均值取一位小數得到',

  `BACKGRO'' COMMENT '店鋪標題圖片',

  `CREATED_DATE` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',

  `UPDATED_DATE1. '更新時間',

  PRIMARY KEY (`ID`),

  KEY IDX_MOB(LEGAL_PERSON_MOBILE),

) EY IDX_UPTIME(UPDATED_DATE)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='GAY店舖'

 

加入欄位範例：

''NEEE7S ASUU​​DD獎COMMENT '廣場名稱簡寫' AFTER FULL_NAME ; 

修改表格欄位範例：

_

ALTER TABLE

comment '交易狀態   0:待支付/退款,1:等待第三方管道回檔,2:支付/退款成功,3:支付/退款失敗,4:支付/退款確認成功,5:支付/退款確認失敗,6;交易關閉,7:待收款(如果是此狀態-需要確認收款帳號是否正常),8:支付/退款確認成功-不可再進行其他操作,9:驗簽失敗,10:同步確認/買家已付款-等待賣家發貨WAIT_SELLER_SEND_GOODS,11:同步確認/賣家已發貨等待買家確認WAIT_BUYER_CONFIRM_GOODS' AFTER DESCRIPTION;

SQL編寫

2.1，盡量使用單表查詢，避免多表JOIN。 JOIN的後續ON條件不能用OR判斷，例如SELECT A.C1,B.C2 FROM A,B ON(A.ID=B.PID OR B.TAG=A.TAR_GET);  OR效能非常低，PS：我們線上一些打開緩慢的功能模組都是因為這個OR的寫法導致的。

 

2.2，寫到應用程式的SQL語句，禁止一切DDL操作，例如：create,drop,alter,grant,remove；如有特殊需要，請與dba協商同意方可使用。

 

2.3，寫SQL的時候一定要為每個欄位指定表名做前綴。例如select ub.id,ub.name from user_business ub where ub.create_date > '';在iBatis的SQLMap檔案中綁定變數使用"#var_name"表示，替代變數使用"$var_name$"；所有需要動態order by條件的查詢，在使用替代變數過程中，需要將可能傳入的內容以枚舉寫死在程式碼中，禁止接受外部傳入內容。

 

2.4，如果需要事務的支持，在使用了innodb的情況下，在資料庫連接時候，先關閉自動提交，如: set auto_commit=0; 事務處理的情況下，執行insert、delete、update後，commit；在exception程式碼區塊，必須寫上rollback操作。

 

2.5，不要寫select *類似的程式碼，需要製定的欄位名稱。

 

2.6，mysql的日期和字符是相同的，所以不需要像oracle那樣做另外的轉換，比如：

select  e.username from .loyday e. 1998-12-31 11:30:45'。

 

2.7，避免在where字句中對字段施加函數，如果是業務要求的除外，但需要在編寫時候諮詢DBA。例如DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m-%d') >= DATE_FORMAT('2014-10-01', '%Y-%m-%d')，就需要改正掉。

 

2.8，避免多餘的排序，使用group by的時候，預設會進行排序，當你不需要排序的時候，可以使用order by null；

.當表格連接時候，用於連接的兩個表格的欄位如果資料類型不一致，則必須在一邊加上類型轉換的函數。杜絕mysql做隱式類型轉換的情況。

 

2.10，禁止在應用程式裡面執行對資料庫的批次更新sql操作，如果需要請發email由dba判斷在適當的時間段裡面在IDC的庫上手動人工執行。

3，基本原則

PS：表結構變更須由庫表所屬團隊啟動。

 

1，所有在測試環境執行的表格結構變更的SQL必須經過DBA review。

2，不允許物理刪除，不得使用存儲過程、觸發器以及視圖，特殊情況和業務場景申請DBA

 

 自己的業務場景的，歡迎大家提出更好的建議，自己也會根據業務的發展，不斷總結擴展最契合的資料庫規格。

 以上是MySQL 資料庫設計初步規範V1.0的內容，更多相關內容請關注PHP中文網（www.php.cn）！