最近這段時間同事們都休高溫假，苦了我們這邊今年的新員工了，經(jīng)常讀本號文章的小伙伴應該也看出來了吧，已經(jīng)接近一個月沒有原創(chuàng)了，沒辦法，一個人干n個人的活，工作趕進度，干貨文章也就擱置了，只能慢慢的補上來。

正好最近公司新需求來了，又是假期沒人的時候，所以我這塊磚就被搬出來了。

新需求也就意味著新設計，原來的表設計也不能滿足新需求，需要設計表了。

省略復雜的表設計溝通階段，本文只記錄表在設計過程中需要注意的18個規(guī)范。

在建表的過程中如果不注意本文所述的18條小細節(jié)，等待后面開發(fā)，甚至是系統(tǒng)上線，再進行表的修改那代價你就品把。

所以本文就拿我多年工作中踩過的坑，實際的使用經(jīng)驗來進行分享，非常的有借鑒意義，希望能對你有所幫助，少走彎路少踩坑，省下的時間自由摸魚。

1.命名

(1) 字段命名

中國人的命名習慣就是中式英語，所以起名時每個人就有每個人的特色，在進行表設計時，盡可能的進行統(tǒng)一，要么全部中文拼音，要么全部英文，切記不要混用，那樣真的太難受了。

給表、字段名給個好名字，真的太重要了，最好做到見名知意。

推薦一個起名網(wǎng)站，再不濟就去 ChatGPT 起名。

https://unbug.github.io/codelf/

就拿用戶名來說舉個例子：

正例：

用戶名：username

反例：

用戶名：yong_hu_name,name等

需要注意的是，見名知意不錯，切記名字也不可太長。

說完了起名，再說一下大小寫。

(2) 大小寫

在國產(chǎn)數(shù)據(jù)庫中，有的會默認搞成全大寫，小寫的有的反而不兼容會有 Bug 產(chǎn)生，而站在視覺感官上來說，小寫加下劃線的形式更加易讀，更加直觀，所以在命名時，還是那句話，統(tǒng)一規(guī)則。

要么全部大寫加下劃線，要么全部小寫加下劃線，禁止大小寫混用。

推薦全部小寫加下劃線的形式。

大小寫混用的拉出去砍了。

正例：

產(chǎn)品名稱：product_name,PRODUCT_NAME

反例：

產(chǎn)品名稱：product_NAME,PRODUCT_name

(3) 分隔符

在給字段起名時，很多場景下單個單詞無法滿足我們的命名要求，那么多個單詞怎么連接呢？

推薦使用_下劃線進行連接。

還有駝峰形式或者不使用連接符，這些都是禁止的，有的框架在使用駝峰時會遇到轉(zhuǎn)換問題。

使用連接符可讀性太差，這誰家好人能一眼讀出來一大長串啊是不是。

正例：

產(chǎn)品名稱：product_name,PRODUCT_NAME

反例：

產(chǎn)品名稱：productname,productName,product@name

(4) 關鍵字

上面說過起名要見名知意，但是也要避免與數(shù)據(jù)庫中的關鍵字沖突，比如工作中經(jīng)常用過的status。

比如涉及到關鍵字的，可以假如業(yè)務來進行區(qū)分。

創(chuàng)建時間：create_time
更新時間：update_time
刪除狀態(tài)：delete_status,deleted

(5) 索引名

索引的命名由索引的類型來分類，因為索引有很多種，主鍵、唯一、普通、聯(lián)合、空間等，

通過索引名稱可以一眼看出來是普通索引還是唯一索引，或者聯(lián)合索引那么這個索引的名稱就是規(guī)范的。

比如聯(lián)合索引按照字段順序進行命名，唯一索引加入前綴uniq。

(6) 表名

字段相關名稱的說完了，還有表名在提一下，表的命名中，除了體現(xiàn)當前表含義外最好加入業(yè)務前綴。

比如訂單相關的表用order_前綴。

2.字段類型

對于字段的類型，可選擇的太多了，時間類型我們可以使用date，datetime，timestamp，也可以用 bigint 等等。

字符類型的有varchar、char、text等，數(shù)字有int，bigint，tinyint，smallint等。

其實這么多都挑花眼了，不知道用哪個，還不如全用varchar，是不是你就是這樣做的？

如何選擇一個合適的字段類型，就變成了我們不得不考慮的問題了。

比如狀態(tài)值，10以內(nèi)的數(shù)字，每個數(shù)字1個字節(jié)就夠，使用tinyint即可，如果選了bigint，反而會白白浪費空間。

所以我們可以參考以下原則：

• 滿足業(yè)務需求的情況下，盡可能選擇占用存儲空間小的字段類型。
• 字段長度固定的可以選擇char，不固定的可以選擇varchar。
• 是否這種true或者false的字段，可以使用bit類型。
• 枚舉字段可以tinyint類型。
• 主鍵使用bigint類型。
• 金額字段可以使用decimal或者換算單位存bigint。
• 時間字段使用datetime或者timestamp或者轉(zhuǎn)換時間戳存bigint。

3.字段長度

上面字段類型的選擇中提到了長度，接下來我們就重點說一下長度的選擇。

varchar(255) 中 255 代表的是字符長度。而在 MySQL中，除了 varchar 和 char 代表的是字符長度之外，其他的類型都是字節(jié)長度。

bigint  的實際長度是 8 個字節(jié)，bigint(4) 代表當不滿 4 個字節(jié)的時候，前面填充0（前提是開啟了自動填充）。

當超過4個字節(jié)時按照實際情況展示。

比如現(xiàn)在的數(shù)據(jù)是 12345，展示的時候也是展示12345。

但是需要注意的是有的 MySQL 客戶端只會展示 4 個字節(jié)，比如展示成 1234，所以 bigint(4)中的 4 表示的是顯示長度，實際占用還是8個字節(jié)。

4.字段個數(shù)

大家在看數(shù)據(jù)庫表優(yōu)化時應該經(jīng)常聽到的就是減少表的字段個數(shù)，防止寬表的發(fā)生。

所以我們在建表時最好控制一下字段數(shù)量，我上家單位涉及的業(yè)務類型的表，那真的是字段巨多，對于這種場景，我們可以大表拆分小表，每個表擁有一個共同的唯一ID做主鍵進行關聯(lián)。

建議每個表的字段數(shù)量控制在20個，如果字段太多，表中數(shù)據(jù)存儲量大了之后嚴重影響查詢效率。

5.主鍵

不知道你們有沒有遇到過，我是遇到過表連個主鍵都沒有，全是普通的列，索引更不用說當然也沒有了。

之所以每個表都需要有個主鍵是因為，主鍵索引相比其他的索引在查詢時可以避免回表，提升查詢效率。而且主鍵索引也是唯一索引，可以作為業(yè)務的去重。

在單體數(shù)據(jù)庫中使用默認的自增ID做主鍵即可，效率還是很高的。在分布式環(huán)境中，最好還是使用遞增的分布式ID算法，保證全局唯一。

需要注意的是，主鍵建議保存與業(yè)務無關的值，方便后面擴展。

分布式ID生成算法可以看下之前的這篇文章：全網(wǎng)最全的分布式ID分析

6.外鍵

說完主鍵說一下外鍵，這個避免使用吧。

說實話，不好用，外鍵本來的作用是保證數(shù)據(jù)的一致，關聯(lián)表少的時候還沒啥，等關聯(lián)表數(shù)量上來之后，在進行刪除等操作時，性能是非常差的。

除了外鍵還有就是觸發(fā)器以及存儲過程，每次一見到開源框架中有這些就頭疼。

7.索引

表的主鍵索引是必須的，對于其他的索引，根據(jù)自己的業(yè)務場景進行添加即可，但是一個表的索引數(shù)量盡量也不要太多，建議單表索引數(shù)量不要超過5個。

創(chuàng)建索引時盡可能的考慮索引覆蓋、最左前綴、索引下推等優(yōu)化方案。

需要注意的是對于重復性較高的字段也不建議創(chuàng)建索引，因為這樣沒意義。

8.唯一索引

這里為什么會單獨把唯一索引揪出來呢，還是因為有坑啊，大家在使用唯一索引時，如果是單個字段倒還好，如果是多個字段的，那你一定要注意了，如果有 null 值的出現(xiàn)，唯一性約束可能會失效哦，對于唯一索引的坑下一篇文章單獨拿出來講講。

9.NOT NULL

建議大家在設計表時，能確保不會出現(xiàn) NULL值的列設置為 NOT NULL ，這是因為當存儲引擎是 Innodb 時，對于NULL值會占用更多的空間，且查詢時 NULL 值也會造成索引失效，查詢條件只能用時IS NULL或者IS NOT NULL 進行判斷。

因此建議能定義為NOT NULL，就定義為NOT NULL。

定義為NOT NULL也有好處，當 INSERT 時如果漏掉了某個字段的值，直接報錯提醒出來，多么明顯的報錯。

還有一種情況就是在現(xiàn)有的表中增加字段，此時歷史數(shù)據(jù)中對于新增加的字段是沒有值的，因為設置NOT NULL 的字段盡量也都賦一個默認值。

10.存儲引擎

這個應該沒什么說的了，大部分都是使用的 Innodb，如果不是，去看看改一下吧。如果你的業(yè)務場景適合其他引擎或者你們有自己開發(fā)的引擎，當我沒說。

如果你不知道為啥使用 Innodb，那么現(xiàn)在你知道了，因為 Innodb 支持事務，且性能越來越優(yōu)秀。

11.時間字段

下面 就是對數(shù)據(jù)庫中的個別容易產(chǎn)生 bug 的字段類型進行分析。

首先就是時間字段，畢竟時間類型太多了，我們存儲時間可以使用 date、datetime、timestamp、varchar、bigint等。

varchar 保存有點就是易讀，直接返回給前端，省去了轉(zhuǎn)換的過程。

date 只能保存日期，沒有時間，看需求。

datetime 與timestamp 更適合我們保存時間，但是他們也有區(qū)別。

(1) datetime

• datetime 存儲的時間范圍更廣，在MySQL中，可以表示從 1000-01-01 到 9999-12-31 之間的日期和時間。
• datetime 不涉及時區(qū)轉(zhuǎn)換。
• datetime 不支持自動更新。

(2) timestamp

• timestamp 存儲范圍較窄，在MySQL 中，可以表示從 1970-01-01 00:00:00 UTC 到 2038-01-19 03:14:07 UTC 的日期時間。
• timestamp 通常以 UTC 存儲，所以需要進行時區(qū)轉(zhuǎn)換， 比較適合存儲跨時區(qū)的數(shù)據(jù)。
• timestamp 在 MySQL 中還可以設置更新時間字段，設置為自動更新。

需要注意的是，在給時間設置默認值時，不要設置0000-00-00 00:00:00 ，防止查詢時時間轉(zhuǎn)換報錯。

出了上面幾種，還可以使用 bigint 存儲時間戳的形式，除了可讀性以及需要轉(zhuǎn)換外，好像也沒啥大問題，你們有用這種方式存儲時間的嗎。

12.金額字段

金額字段想到的就是浮點類型 float，double，decimal等。

而 float、double 會丟失精度就算了還是別用了，因此還是推薦你用 decimal ，但是需要注意 decimal 使用時的幾個坑，還不了解沒關系，鏈接我放下面了。

不掌握BigDecimal的四大坑你敢用嗎？

如果你還是不想用 decimal ，那么再推薦你一種，轉(zhuǎn)換為分或者更小的貨幣單位，使用 bigint 存儲。

13.json字段

這個字段一直是我不想用的，因為兼容不好。如果后期需要切換數(shù)據(jù)庫，假如正好你切換的數(shù)據(jù)庫不支持json類型，那么恭喜你，改代碼吧。

這段時間正好新需求，試了一下這個 json 字段，感覺用起來還是不錯的，前提是兼容 json 格式。

不好的地方就是對數(shù)據(jù)的處理查詢上還是沒有那么方便。

一句話，能不用還是不用吧，建議 json 類型直接存儲 varchar，然后代碼中轉(zhuǎn)換一下更好，畢竟不用考慮兼容問題啊。

14.大字段

如果你用了json，那么不可避免的會有大字段的可能，大字段的定義就是占用存儲空間多的字段。

對于大文本如果直接定義為 text 類型，可能會浪費存儲空間。如果業(yè)務可以對該字段進行一個最大長度的限制，那么我們可以使用 varchar 類型進行存儲，效率更高。

還有一個類型就是 blob ，直接存儲文件內(nèi)容。如果你們也這樣做，建議還是換了吧，這個設計有點不合理了，

上個存儲保存?zhèn)€文件地址多好。

15.冗余字段

在設計表的時候，為了查詢的性能考慮，可能會冗余一些信息字段，比如說某個表中需要記錄用戶的 userId，當我們需要用戶名稱的時候，還需要通過 userId 進行關聯(lián)查詢獲取 username ，那么我們就可以冗余 username 到我們的表中，提升我們的查詢效率。

相當于空間換時間的概念，犧牲這一點空間，減少的卻是 join 查詢的時間，對查詢性能的提升很有幫助。

不能光說好的地方，也有壞的地方，有存儲的地方就得有維護，容易造成數(shù)據(jù)的不一致。

所以在使用中也是根據(jù)自己的業(yè)務綜合評估，選擇一個更適合自己業(yè)務的方法。

16.注釋

表注釋以及字段注釋，與代碼開發(fā)中的代碼注釋沒差別，都得寫清楚啊，假如是個狀態(tài)值1、2、3、4、5的，不寫注釋時間長了你知道是什么意思嗎？

需要注意一點哈，寫的注釋注意與代碼中的保持同步，別到了最后一個字段好多個含義，最后弄的自己都要分不清哪個是什么意思那不悲催了。

17.字符集

說了那么多表里面的東西，在說一下底層最基礎的編碼。MySQL 中支持的編碼類型還是很多的，不過這邊建議使用utf8mb4，因為 utf8 是沒法存儲 emoji 表情的，所以被替代也是個趨勢吧，使用 ut8mb4 能省去很多的麻煩。

常用的 gbk、utf8、utf8mb4區(qū)別如下：

• gbk 包含了 GB2312 標準中的所有字符，不支持 Unicode 標準，所以只能在中國使用，在處理多語言時能力有限。
• utf8是一種可變長度的 Unicode 編碼方法，兼容性也好，是一種廣泛使用的標準，支持多種語言。缺點就是不支持emoji 表情。
• utf8mb4 是 utf8 的一個擴展，也是 MySQL 中的推薦字符集，尤其是支持表情符號和特殊字符。

18.排序規(guī)則

上面說了字符集，排序規(guī)則與字符集也是息息相關的。在 mysql 中，如果你的字符集設置的是 utf8mb4，那么你的排序規(guī)則也是 utf8mb4 開頭的，常用的就是utf8mb4_general_ci，utf8mb4_bin。

• utf8mb4_general_ci 的排序規(guī)則對大小寫是不敏感的，簡單地說就是a與A相等，他會認為這倆是相同的字符。
• utf8mb4_bin是區(qū)分大小寫的，a與A會被認為是不同的字符。

所以排序規(guī)則還是要根據(jù)我們的業(yè)務場景進行選擇，比如用戶的登錄密碼。

總結

本文總結了工作多年建表的一點心得，希望對你有所幫助，下面我們一起回顧一下：

• 表的字符集、排序規(guī)則統(tǒng)一，根據(jù)自己的業(yè)務需要選擇合適的編碼。
• 在起名上見名知意，不管是表名還是字段名、索引名，統(tǒng)一起名規(guī)則。
• 在字段上，控制表字段個數(shù)，防止寬表的產(chǎn)生，字段類型上滿足業(yè)務的前提下選擇占用存儲空間少的字段，避免大字段的產(chǎn)生，可以使用冗余字段加速查詢，對于不了解的類型少用或不用。
• 關于索引方面，每個表必須有主鍵索引，其次唯一索引使用時注意避坑。
• 最后使用支持事務的 Innodb 引擎。