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

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

新需求也就意味著新設(shè)計(jì)，原來的表設(shè)計(jì)也不能滿足新需求，需要設(shè)計(jì)表了。

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

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

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

1.命名

(1) 字段命名

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

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

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

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

就拿用戶名來說舉個(gè)例子：

正例：

用戶名：username

反例：

用戶名：yong_hu_name,name等

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

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

(2) 大小寫

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

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

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

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

正例：

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

反例：

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

(3) 分隔符

在給字段起名時(shí)，很多場(chǎng)景下單個(gè)單詞無法滿足我們的命名要求，那么多個(gè)單詞怎么連接呢？

推薦使用_下劃線進(jìn)行連接。

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

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

正例：

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

反例：

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

(4) 關(guān)鍵字

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

比如涉及到關(guān)鍵字的，可以假如業(yè)務(wù)來進(jìn)行區(qū)分。

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

(5) 索引名

索引的命名由索引的類型來分類，因?yàn)樗饕泻芏喾N，主鍵、唯一、普通、聯(lián)合、空間等，

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

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

(6) 表名

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

比如訂單相關(guān)的表用order_前綴。

2.字段類型

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

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

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

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

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

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

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

3.字段長度

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

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

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

當(dāng)超過4個(gè)字節(jié)時(shí)按照實(shí)際情況展示。

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

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

4.字段個(gè)數(shù)

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

所以我們?cè)诮ū頃r(shí)最好控制一下字段數(shù)量，我上家單位涉及的業(yè)務(wù)類型的表，那真的是字段巨多，對(duì)于這種場(chǎng)景，我們可以大表拆分小表，每個(gè)表擁有一個(gè)共同的唯一ID做主鍵進(jìn)行關(guān)聯(lián)。

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

5.主鍵

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

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

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

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

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

6.外鍵

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

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

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

7.索引

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

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

需要注意的是對(duì)于重復(fù)性較高的字段也不建議創(chuàng)建索引，因?yàn)檫@樣沒意義。

8.唯一索引

這里為什么會(huì)單獨(dú)把唯一索引揪出來呢，還是因?yàn)橛锌影。蠹以谑褂梦ㄒ凰饕龝r(shí)，如果是單個(gè)字段倒還好，如果是多個(gè)字段的，那你一定要注意了，如果有 null 值的出現(xiàn)，唯一性約束可能會(huì)失效哦，對(duì)于唯一索引的坑下一篇文章單獨(dú)拿出來講講。

9.NOT NULL

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

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

定義為NOT NULL也有好處，當(dāng) INSERT 時(shí)如果漏掉了某個(gè)字段的值，直接報(bào)錯(cuò)提醒出來，多么明顯的報(bào)錯(cuò)。

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

10.存儲(chǔ)引擎

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

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

11.時(shí)間字段

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

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

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

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

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

(1) datetime

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

(2) timestamp

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

需要注意的是，在給時(shí)間設(shè)置默認(rèn)值時(shí)，不要設(shè)置0000-00-00 00:00:00 ，防止查詢時(shí)時(shí)間轉(zhuǎn)換報(bào)錯(cuò)。

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

12.金額字段

金額字段想到的就是浮點(diǎn)類型 float，double，decimal等。

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

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

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

13.json字段

這個(gè)字段一直是我不想用的，因?yàn)榧嫒莶缓谩Ｈ绻笃谛枰袚Q數(shù)據(jù)庫，假如正好你切換的數(shù)據(jù)庫不支持json類型，那么恭喜你，改代碼吧。

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

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

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

14.大字段

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

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

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

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

15.冗余字段

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

相當(dāng)于空間換時(shí)間的概念，犧牲這一點(diǎn)空間，減少的卻是 join 查詢的時(shí)間，對(duì)查詢性能的提升很有幫助。

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

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

16.注釋

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

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

17.字符集

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

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

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

18.排序規(guī)則

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

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

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

總結(jié)

本文總結(jié)了工作多年建表的一點(diǎn)心得，希望對(duì)你有所幫助，下面我們一起回顧一下：

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