閉眼建表所需的 18 條軍規(guī)
最近這段時間同事們都休高溫假，苦了我們這邊今年的新員工了，經(jīng)常讀本號文章的小伙伴應(yīng)該也看出來了吧，已經(jīng)接近一個月沒有原創(chuàng)了，沒辦法，一個人干n個人的活，工作趕進(jìn)度，干貨文章也就擱置了，只能慢慢的補(bǔ)上來。
正好最近公司新需求來了，又是假期沒人的時候，所以我這塊磚就被搬出來了。
新需求也就意味著新設(shè)計(jì)，原來的表設(shè)計(jì)也不能滿足新需求，需要設(shè)計(jì)表了。
省略復(fù)雜的表設(shè)計(jì)溝通階段，本文只記錄表在設(shè)計(jì)過程中需要注意的18個規(guī)范。
在建表的過程中如果不注意本文所述的18條小細(xì)節(jié)，等待后面開發(fā)，甚至是系統(tǒng)上線，再進(jìn)行表的修改那代價你就品把。
所以本文就拿我多年工作中踩過的坑，實(shí)際的使用經(jīng)驗(yàn)來進(jìn)行分享，非常的有借鑒意義，希望能對你有所幫助，少走彎路少踩坑，省下的時間自由摸魚。
1.命名
(1) 字段命名
中國人的命名習(xí)慣就是中式英語，所以起名時每個人就有每個人的特色，在進(jìn)行表設(shè)計(jì)時，盡可能的進(jìn)行統(tǒng)一，要么全部中文拼音，要么全部英文，切記不要混用，那樣真的太難受了。
給表、字段名給個好名字，真的太重要了，最好做到見名知意。
推薦一個起名網(wǎng)站，再不濟(jì)就去 ChatGPT 起名。
https://unbug.github.io/codelf/
就拿用戶名來說舉個例子：
正例：
用戶名：username反例：
用戶名：yong_hu_name,name等需要注意的是，見名知意不錯，切記名字也不可太長。
說完了起名，再說一下大小寫。
(2) 大小寫
在國產(chǎn)數(shù)據(jù)庫中，有的會默認(rèn)搞成全大寫，小寫的有的反而不兼容會有 Bug 產(chǎn)生，而站在視覺感官上來說，小寫加下劃線的形式更加易讀，更加直觀，所以在命名時，還是那句話，統(tǒng)一規(guī)則。
要么全部大寫加下劃線，要么全部小寫加下劃線，禁止大小寫混用。
推薦全部小寫加下劃線的形式。
大小寫混用的拉出去砍了。
正例：
產(chǎn)品名稱：product_name,PRODUCT_NAME反例：
產(chǎn)品名稱：product_NAME,PRODUCT_name(3) 分隔符
在給字段起名時，很多場景下單個單詞無法滿足我們的命名要求，那么多個單詞怎么連接呢？
推薦使用_下劃線進(jìn)行連接。
還有駝峰形式或者不使用連接符，這些都是禁止的，有的框架在使用駝峰時會遇到轉(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)建時間：create_time
更新時間：update_time
刪除狀態(tài)：delete_status,deleted(5) 索引名
索引的命名由索引的類型來分類，因?yàn)樗饕泻芏喾N，主鍵、唯一、普通、聯(lián)合、空間等，
通過索引名稱可以一眼看出來是普通索引還是唯一索引，或者聯(lián)合索引那么這個索引的名稱就是規(guī)范的。
比如聯(lián)合索引按照字段順序進(jìn)行命名，唯一索引加入前綴uniq。
(6) 表名
字段相關(guān)名稱的說完了，還有表名在提一下，表的命名中，除了體現(xiàn)當(dāng)前表含義外最好加入業(yè)務(wù)前綴。
比如訂單相關(guān)的表用order_前綴。
2.字段類型
對于字段的類型，可選擇的太多了，時間類型我們可以使用date，datetime，timestamp，也可以用 bigint 等等。
字符類型的有varchar、char、text等，數(shù)字有int，bigint，tinyint，smallint等。
其實(shí)這么多都挑花眼了，不知道用哪個，還不如全用varchar，是不是你就是這樣做的？
如何選擇一個合適的字段類型，就變成了我們不得不考慮的問題了。
比如狀態(tài)值，10以內(nèi)的數(shù)字，每個數(shù)字1個字節(jié)就夠，使用tinyint即可，如果選了bigint，反而會白白浪費(fèi)空間。
所以我們可以參考以下原則：
- 滿足業(yè)務(wù)需求的情況下，盡可能選擇占用存儲空間小的字段類型。
- 字段長度固定的可以選擇char，不固定的可以選擇varchar。
- 是否這種true或者false的字段，可以使用bit類型。
- 枚舉字段可以tinyint類型。
- 主鍵使用bigint類型。
- 金額字段可以使用decimal或者換算單位存bigint。
- 時間字段使用datetime或者timestamp或者轉(zhuǎn)換時間戳存bigint。
3.字段長度
上面字段類型的選擇中提到了長度，接下來我們就重點(diǎn)說一下長度的選擇。
varchar(255) 中 255 代表的是字符長度。而在 MySQL中，除了 varchar 和 char 代表的是字符長度之外，其他的類型都是字節(jié)長度。
bigint 的實(shí)際長度是 8 個字節(jié)，bigint(4) 代表當(dāng)不滿 4 個字節(jié)的時候，前面填充0（前提是開啟了自動填充）。
當(dāng)超過4個字節(jié)時按照實(shí)際情況展示。
比如現(xiàn)在的數(shù)據(jù)是 12345，展示的時候也是展示12345。
但是需要注意的是有的 MySQL 客戶端只會展示 4 個字節(jié)，比如展示成 1234，所以 bigint(4)中的 4 表示的是顯示長度，實(shí)際占用還是8個字節(jié)。
4.字段個數(shù)
大家在看數(shù)據(jù)庫表優(yōu)化時應(yīng)該經(jīng)常聽到的就是減少表的字段個數(shù)，防止寬表的發(fā)生。
所以我們在建表時最好控制一下字段數(shù)量，我上家單位涉及的業(yè)務(wù)類型的表，那真的是字段巨多，對于這種場景，我們可以大表拆分小表，每個表擁有一個共同的唯一ID做主鍵進(jìn)行關(guān)聯(lián)。
建議每個表的字段數(shù)量控制在20個，如果字段太多，表中數(shù)據(jù)存儲量大了之后嚴(yán)重影響查詢效率。
5.主鍵
不知道你們有沒有遇到過，我是遇到過表連個主鍵都沒有，全是普通的列，索引更不用說當(dāng)然也沒有了。
之所以每個表都需要有個主鍵是因?yàn)椋麈I索引相比其他的索引在查詢時可以避免回表，提升查詢效率。而且主鍵索引也是唯一索引，可以作為業(yè)務(wù)的去重。
在單體數(shù)據(jù)庫中使用默認(rèn)的自增ID做主鍵即可，效率還是很高的。在分布式環(huán)境中，最好還是使用遞增的分布式ID算法，保證全局唯一。
需要注意的是，主鍵建議保存與業(yè)務(wù)無關(guān)的值，方便后面擴(kuò)展。
分布式ID生成算法可以看下之前的這篇文章：全網(wǎng)最全的分布式ID分析
6.外鍵
說完主鍵說一下外鍵，這個避免使用吧。
說實(shí)話，不好用，外鍵本來的作用是保證數(shù)據(jù)的一致，關(guān)聯(lián)表少的時候還沒啥，等關(guān)聯(lián)表數(shù)量上來之后，在進(jìn)行刪除等操作時，性能是非常差的。
除了外鍵還有就是觸發(fā)器以及存儲過程，每次一見到開源框架中有這些就頭疼。
7.索引
表的主鍵索引是必須的，對于其他的索引，根據(jù)自己的業(yè)務(wù)場景進(jìn)行添加即可，但是一個表的索引數(shù)量盡量也不要太多，建議單表索引數(shù)量不要超過5個。
創(chuàng)建索引時盡可能的考慮索引覆蓋、最左前綴、索引下推等優(yōu)化方案。
需要注意的是對于重復(fù)性較高的字段也不建議創(chuàng)建索引，因?yàn)檫@樣沒意義。
8.唯一索引
這里為什么會單獨(dú)把唯一索引揪出來呢，還是因?yàn)橛锌影。蠹以谑褂梦ㄒ凰饕龝r，如果是單個字段倒還好，如果是多個字段的，那你一定要注意了，如果有 null 值的出現(xiàn)，唯一性約束可能會失效哦，對于唯一索引的坑下一篇文章單獨(dú)拿出來講講。
9.NOT NULL
建議大家在設(shè)計(jì)表時，能確保不會出現(xiàn) NULL值的列設(shè)置為 NOT NULL ，這是因?yàn)楫?dāng)存儲引擎是 Innodb 時，對于NULL值會占用更多的空間，且查詢時 NULL 值也會造成索引失效，查詢條件只能用時IS NULL或者IS NOT NULL 進(jìn)行判斷。
因此建議能定義為NOT NULL，就定義為NOT NULL。
定義為NOT NULL也有好處，當(dāng) INSERT 時如果漏掉了某個字段的值，直接報錯提醒出來，多么明顯的報錯。
還有一種情況就是在現(xiàn)有的表中增加字段，此時歷史數(shù)據(jù)中對于新增加的字段是沒有值的，因?yàn)樵O(shè)置NOT NULL 的字段盡量也都賦一個默認(rèn)值。
10.存儲引擎
這個應(yīng)該沒什么說的了，大部分都是使用的 Innodb，如果不是，去看看改一下吧。如果你的業(yè)務(wù)場景適合其他引擎或者你們有自己開發(fā)的引擎，當(dāng)我沒說。
如果你不知道為啥使用 Innodb，那么現(xiàn)在你知道了，因?yàn)?Innodb 支持事務(wù)，且性能越來越優(yōu)秀。
11.時間字段
下面 就是對數(shù)據(jù)庫中的個別容易產(chǎn)生 bug 的字段類型進(jìn)行分析。
首先就是時間字段，畢竟時間類型太多了，我們存儲時間可以使用 date、datetime、timestamp、varchar、bigint等。
varchar 保存有點(diǎn)就是易讀，直接返回給前端，省去了轉(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 存儲，所以需要進(jìn)行時區(qū)轉(zhuǎn)換， 比較適合存儲跨時區(qū)的數(shù)據(jù)。
- timestamp 在 MySQL 中還可以設(shè)置更新時間字段，設(shè)置為自動更新。
需要注意的是，在給時間設(shè)置默認(rèn)值時，不要設(shè)置0000-00-00 00:00:00 ，防止查詢時時間轉(zhuǎn)換報錯。
出了上面幾種，還可以使用 bigint 存儲時間戳的形式，除了可讀性以及需要轉(zhuǎn)換外，好像也沒啥大問題，你們有用這種方式存儲時間的嗎。
12.金額字段
金額字段想到的就是浮點(diǎn)類型 float，double，decimal等。
而 float、double 會丟失精度就算了還是別用了，因此還是推薦你用 decimal ，但是需要注意 decimal 使用時的幾個坑，還不了解沒關(guān)系，鏈接我放下面了。
如果你還是不想用 decimal ，那么再推薦你一種，轉(zhuǎn)換為分或者更小的貨幣單位，使用 bigint 存儲。
13.json字段
這個字段一直是我不想用的，因?yàn)榧嫒莶缓谩Ｈ绻笃谛枰袚Q數(shù)據(jù)庫，假如正好你切換的數(shù)據(jù)庫不支持json類型，那么恭喜你，改代碼吧。
這段時間正好新需求，試了一下這個 json 字段，感覺用起來還是不錯的，前提是兼容 json 格式。
不好的地方就是對數(shù)據(jù)的處理查詢上還是沒有那么方便。
一句話，能不用還是不用吧，建議 json 類型直接存儲 varchar，然后代碼中轉(zhuǎn)換一下更好，畢竟不用考慮兼容問題啊。
14.大字段
如果你用了json，那么不可避免的會有大字段的可能，大字段的定義就是占用存儲空間多的字段。
對于大文本如果直接定義為 text 類型，可能會浪費(fèi)存儲空間。如果業(yè)務(wù)可以對該字段進(jìn)行一個最大長度的限制，那么我們可以使用 varchar 類型進(jìn)行存儲，效率更高。
還有一個類型就是 blob ，直接存儲文件內(nèi)容。如果你們也這樣做，建議還是換了吧，這個設(shè)計(jì)有點(diǎn)不合理了，
上個存儲保存?zhèn)€文件地址多好。
15.冗余字段
在設(shè)計(jì)表的時候，為了查詢的性能考慮，可能會冗余一些信息字段，比如說某個表中需要記錄用戶的 userId，當(dāng)我們需要用戶名稱的時候，還需要通過 userId 進(jìn)行關(guān)聯(lián)查詢獲取 username ，那么我們就可以冗余 username 到我們的表中，提升我們的查詢效率。
相當(dāng)于空間換時間的概念，犧牲這一點(diǎn)空間，減少的卻是 join 查詢的時間，對查詢性能的提升很有幫助。
不能光說好的地方，也有壞的地方，有存儲的地方就得有維護(hù)，容易造成數(shù)據(jù)的不一致。
所以在使用中也是根據(jù)自己的業(yè)務(wù)綜合評估，選擇一個更適合自己業(yè)務(wù)的方法。
16.注釋
表注釋以及字段注釋，與代碼開發(fā)中的代碼注釋沒差別，都得寫清楚啊，假如是個狀態(tài)值1、2、3、4、5的，不寫注釋時間長了你知道是什么意思嗎？
需要注意一點(diǎn)哈，寫的注釋注意與代碼中的保持同步，別到了最后一個字段好多個含義，最后弄的自己都要分不清哪個是什么意思那不悲催了。
17.字符集
說了那么多表里面的東西，在說一下底層最基礎(chǔ)的編碼。MySQL 中支持的編碼類型還是很多的，不過這邊建議使用utf8mb4，因?yàn)?utf8 是沒法存儲 emoji 表情的，所以被替代也是個趨勢吧，使用 ut8mb4 能省去很多的麻煩。
常用的 gbk、utf8、utf8mb4區(qū)別如下：
- gbk 包含了 GB2312 標(biāo)準(zhǔn)中的所有字符，不支持 Unicode 標(biāo)準(zhǔn)，所以只能在中國使用，在處理多語言時能力有限。
- utf8是一種可變長度的 Unicode 編碼方法，兼容性也好，是一種廣泛使用的標(biāo)準(zhǔn)，支持多種語言。缺點(diǎn)就是不支持emoji 表情。
- utf8mb4 是 utf8 的一個擴(kuò)展，也是 MySQL 中的推薦字符集，尤其是支持表情符號和特殊字符。
18.排序規(guī)則
上面說了字符集，排序規(guī)則與字符集也是息息相關(guān)的。在 mysql 中，如果你的字符集設(shè)置的是 utf8mb4，那么你的排序規(guī)則也是 utf8mb4 開頭的，常用的就是utf8mb4_general_ci，utf8mb4_bin。
- utf8mb4_general_ci 的排序規(guī)則對大小寫是不敏感的，簡單地說就是a與A相等，他會認(rèn)為這倆是相同的字符。
- utf8mb4_bin是區(qū)分大小寫的，a與A會被認(rèn)為是不同的字符。
所以排序規(guī)則還是要根據(jù)我們的業(yè)務(wù)場景進(jìn)行選擇，比如用戶的登錄密碼。
總結(jié)
本文總結(jié)了工作多年建表的一點(diǎn)心得，希望對你有所幫助，下面我們一起回顧一下：
- 表的字符集、排序規(guī)則統(tǒng)一，根據(jù)自己的業(yè)務(wù)需要選擇合適的編碼。
- 在起名上見名知意，不管是表名還是字段名、索引名，統(tǒng)一起名規(guī)則。
- 在字段上，控制表字段個數(shù)，防止寬表的產(chǎn)生，字段類型上滿足業(yè)務(wù)的前提下選擇占用存儲空間少的字段，避免大字段的產(chǎn)生，可以使用冗余字段加速查詢，對于不了解的類型少用或不用。
- 關(guān)于索引方面，每個表必須有主鍵索引，其次唯一索引使用時注意避坑。
- 最后使用支持事務(wù)的 Innodb 引擎。
