看了同事設計的庫表，我默默流下了眼淚。。

前言

對於後端開發同學來說，存取資料庫，是程式碼中必不可少的一個環節。

系統中收集到使用者的核心數據，為了安全性，我們一般會儲存到資料庫，比如：mysql，oracle等。

後端開發的日常工作，需要不斷的建庫和建表，來滿足業務需求。

通常情況下，建庫的頻率比建表要低很多，所以，我們這篇文章主要討論建表相關的內容。

如果我們在建表的時候不註意細節，等後面系統上線之後， 表的維護成本變得非常高，而且很容易踩坑。

今天就跟大家一起聊聊，資料庫建表的18個小技巧。

文章中介紹的很多細節，我在工作中踩過坑，並且實踐過的，非常有借鑒意義，希望對你會有所幫助。

1.名字

建表的時候，給 表 、 欄位 和 索引 起個好名字，真的太重要了。

1.1 見名知意

名字就像 表 、 欄位 和 索引 的一張臉，可以給人留下第一印象。

好的名字，言簡意賅，見名知意，讓人心情愉悅，能夠提高溝通和維護成本。

壞的名字，模擬兩可，不知所雲。而且顯得雜亂無章，看得讓人抓狂。

反例：

使用者名稱稱欄位定義成：yong_hu_ming、使用者_name、name、user_name_123456789


你看了可能會一臉懵逼，這是什麽騷操作？

正例：

使用者名稱稱欄位定義成：user_name


溫馨提醒一下，名字也不宜過長，盡量控制在 30 個字元以內。

1.2 大小寫

名字盡量都用 小寫字母 ，因為從視覺上，小寫字母更容易讓人讀懂。

反例：

欄位名：PRODUCT_NAME、PRODUCT_name


全部大寫，看起來有點不太直觀。而一部份大寫，一部份小寫，讓人看著更不爽。

正例：

欄位名：product_name


名字還是使用全小寫字母，看著更舒服。

1.3 分隔符

很多時候，名字為了讓人好理解，有可能會包含多個單詞。

那麽，多個單詞間的 分隔符 該用什麽呢？

反例：

欄位名：productname、productName、product name、product@name


單詞間沒有分隔，或者單詞間用駝峰標識，或者單詞間用空格分隔，或者單詞間用@分隔，這幾種方式都不太建議。

正例：

欄位名：product_name


強烈建議大家在單詞間用 _ 分隔。

1.4 表名

對於表名，在言簡意賅，見名知意的基礎之上，建議帶上 業務字首 。

如果是訂單相關的業務表，可以在表名前面加個字首： order_ 。

例如：order_pay、order_pay_detail等。

如果是商品相關的業務表，可以在表名前面加個字首： product_ 。

例如：product_spu，product_sku等。

這樣做的好處是為了方便歸類，把相同業務的表，可以非常快速的聚集到一起。

另外，還有有個好處是，如果哪天有非訂單的業務，比如：金融業務，也需要建一個名字叫做pay的表，可以取名：finance_pay，就能非常輕松的區分。

這樣就不會出現 同名表 的情況。

1.5 欄位名稱

欄位名稱 是開發人員發揮空間最大，但也最容易發生混亂的地方。

比如有些表，使用flag表示狀態，另外的表用status表示狀態。

可以統一一下，使用status表示狀態。

如果一個表使用了另一個表的主鍵，可以在另一張表的名後面，加 _id 或 _sys_no ，例如：

在product_sku表中有個欄位，是product_spu表的主鍵，這時候可以取名：product_spu_id或product_spu_sys_no。

還有建立時間，可以統一成：create_time，修改時間統一成：update_time。

刪除狀態固定為：delete_status。

其實還有很多公共欄位，在不同的表之間，可以使用全域統一的命名規則，定義成相同的名稱，以便於大家好理解。

1.6 索引名

在資料庫中，索引有很多種，包括：主鍵、普通索引、唯一索引、聯合索引等。

每張表的主鍵只有一個，一般使用： id 或者 sys_no 命名。

普通索引和聯合索引，其實是一類。在建立該類索引時，可以加 ix_ 字首，比如：ix_product_status。

唯一索引，可以加 ux_ 字首，比如：ux_product_code。

2.欄位型別

在設計表時，我們在選擇 欄位型別 時，可發揮空間很大。

時間格式的數據有：date、datetime和timestamp等等可以選擇。

字元型別的數據有：varchar、char、text等可以選擇。

數位型別的數據有：int、bigint、smallint、tinyint等可以選擇。

說實話，選擇很多，有時候是一件好事，也可能是一件壞事。

如何選擇一個 合適 的欄位型別，變成了我們不得不面對的問題。

如果欄位型別選大了，比如：原本只有1-10之間的10個數位，結果選了 bigint ，它占 8 個字節。

其實，1-10之間的10個數位，每個數位 1 個字節就能保存，選擇 tinyint 更為合適。

這樣會白白浪費7個字節的空間。

如果欄位型別擇小了，比如：一個18位元的id欄位，選擇了 int 型別，最終數據會保存失敗。

所以選擇一個合適的欄位型別，還是非常重要的一件事情。

以下原則可以參考一下：

1. 盡可能選擇占用儲存空間小的欄位型別，在滿足正常業務需求的情況下，從小到大，往上選。

2. 如果字串長度固定，或者差別不大，可以選擇char型別。如果字串長度差別較大，可以選擇varchar型別。

3. 是否欄位，可以選擇bit型別。

4. 列舉欄位，可以選擇tinyint型別。

5. 主鍵欄位，可以選擇bigint型別。

6. 金額欄位，可以選擇decimal型別。

7. 時間欄位，可以選擇timestamp或datetime型別。

3.欄位長度

前面我們已經定義好了 欄位名稱 ，選擇了合適的 欄位型別 ，接下來，需要重點關註的是 欄位長度 了。

比如：varchar(20)，biginit(20)等。

那麽問題來了， varchar 代表的是 字節 長度，還是 字元 長度呢？

答：在mysql中除了 varchar 和 char 是代表 字元 長度之外，其余的型別都是代表 字節 長度。

biginit(n) 這個 n 表示什麽意思呢？

假如我們定義的欄位型別和長度是：bigint(4)，bigint實際長度是 8 個字節。

現在有個數據a=1，a顯示4個字節，所以在不滿4個字節時前面填充0（前提是該欄位設定了zerofill內容），比如：0001。

當滿了4個字節時，比如現在數據是a=123456，它會按照實際的長度顯示，比如：123456。

但需要註意的是，有些mysql客戶端即使滿了4個字節，也可能只顯示4個字節的內容，比如會顯示成：1234。

所以bigint(4)，這裏的4表示顯示的長度為4個字節，實際長度還是占8個字節。

4.欄位個數

我們在建表的時候，一定要對 欄位個數 做一些限制。

我之前見過有人建立的表，有幾十個，甚至上百個欄位，表中保存的數據非常大，查詢效率很低。

如果真有這種情況，可以將一張 大表 拆成多張 小表 ，這幾張表的主鍵相同。

建議每表的欄位個數，不要超過 20 個。

5. 主鍵

在建立表時，一定要建立 主鍵 。

因為主鍵內建了主鍵索引，相比於其他索引，主鍵索引的查詢效率最高，因為它不需要回表。

此外，主鍵還是天然的 唯一索引 ，可以根據它來判重。

在 單個 資料庫中，主鍵可以透過 AUTO_INCREMENT ，設定成 自動增長 的。

但在 分布式 資料庫中，特別是做了分庫分表的業務庫中，主鍵最好由外部演算法(比如：雪花演算法）生成，它能夠保證生成的id是全域唯一的。

除此之外，主鍵建議保存跟業務無關的值，減少業務耦合性，方便今後的擴充套件。

不過我也見過，有些一對一的表關系，比如：使用者表和使用者擴充套件表，在保存數據時是一對一的關系。

這樣，使用者擴充套件表的主鍵，可以直接保存使用者表的主鍵。

6.儲存引擎

在 mysql8 以前的版本，預設的儲存引擎是 myslam ，而 mysql8 以後的版本，預設的儲存引擎變成了 innodb 。

之前我們還在建立表時，還一直糾結要選哪種儲存引擎？

myslam 的索引和數據分開儲存，而有利於查詢，但它不支持事務和外來鍵等功能。

而 innodb 雖說查詢效能，稍微弱一點，但它支持事務和外來鍵等，功能更強大一些。

以前的建議是：讀多寫少的表，用myslam儲存引擎。而寫多讀多的表，用innodb。

但雖說mysql對innodb儲存引擎效能的不斷最佳化，現在myslam和innodb查詢效能相差已經越來越小。

所以，建議我們在使用 mysql8 以後的版本時，直接使用預設的 innodb 儲存引擎即可，無需額外修改儲存引擎。

7. NOT NULL

在建立欄位時，需要選擇該欄位是否允許為 NULL 。

我們在定義欄位時，應該盡可能明確該欄位 NOT NULL 。

為什麽呢？

我們主要以innodb儲存引擎為例，myslam儲存引擎沒啥好說的。

主要有以下原因：

1. 在innodb中，需要額外的空間儲存null值，需要占用更多的空間。

2. null值可能會導致索引失效。

3. null值只能用 is null 或者 is not null 判斷，用 =號 判斷永遠返回false。

因此，建議我們在定義欄位時，能定義成NOT NULL，就定義成NOT NULL。

但如果某個欄位直接定義成NOT NULL，萬一有些地方忘了給該欄位寫值，就會 insert 不了數據。

這也算合理的情況。

但有一種情況是，系統有新功能上線，新增了欄位。上線時一般會先執行sql指令碼，再部署程式碼。

由於老程式碼中，不會給新欄位賦值，則insert數據時，也會報錯。

由此，非常有必要給NOT NULL的欄位設定預設值，特別是後面新增的欄位。

例如：

altertable product_sku addcolumn brand_id int(10) notnulldefault0;


8.外來鍵

在mysql中，是存在 外來鍵 的。

外來鍵存在的主要作用是：保證數據的 一致性 和 完整性 。

例如：

createtable class (
idint(10) primary key auto_increment,
cname varchar(15)
);


有個班級表 class。

然後有個student表：

createtable student(
idint(10) primary key auto_increment,
namevarchar(15) notnull,
gender varchar(10) notnull,
cid int,
foreignkey(cid) references class(id)
);


其中student表中的cid欄位，保存的 class表的id，這時透過 foreign key 增加了一個外來鍵。

這時，如果你直接透過student表的id刪除數據，會報異常：

a foreign key constraint fails


必須要先刪除 class表對於的cid那條數據，再刪除student表的數據才行，這樣能夠保證數據的一致性和完整性。

順便說一句：只有儲存引擎是innodb時，才能使用外來鍵。

如果只有兩張表的關聯還好，但如果有十幾張表都建了外來鍵關聯，每刪除一次主表，都需要同步刪除十幾張子表，很顯然效能會非常差。

因此，互聯網系統中，一般建議不使用外來鍵。因為這類系統更多的是為了效能考慮，寧可犧牲一點數據一致性和完整性。

除了 外來鍵 之外， 儲存過程 和 觸發器 也不太建議使用，他們都會影響效能。

9. 索引

在建表時，除了指定 主鍵索引 之外，還需要建立一些 普通索引 。

例如：

createtable product_sku(
idint(10) primary key auto_increment,
spu_id int(10) notnull,
brand_id int(10) notnull,
namevarchar(15) notnull
);


在建立商品表時，使用spu_id（商品組表）和brand_id（品牌表）的id。

像這類保存其他表id的情況，可以增加普通索引：

createtable product_sku (
idint(10) primary key auto_increment,
spu_id int(10) notnull,
brand_id int(10) notnull,
namevarchar(15) notnull,
KEY`ix_spu_id` (`spu_id`) USING BTREE,
KEY`ix_brand_id` (`brand_id`) USING BTREE
);


後面查表的時候，效率更高。

但索引欄位也不能建的太多，可能會影響保存數據的效率，因為索引需要額外的儲存空間。

建議單表的索引個數不要超過： 5 個。

如果在建表時，發現索引個數超過5個了，可以刪除部份 普通索引 ，改成 聯合索引 。

順便說一句：在建立聯合索引的時候，需要使用註意 最左匹配原則 ，不然，建的聯合索引效率可能不高。

對於數據重復率非常高的欄位，比如：狀態，不建議單獨建立普通索引。因為即使加了索引，如果mysql發現 全表掃描 效率更高，可能會導致索引失效。

如果你對索引失效問題比較感興趣，可以看看我的另一篇文章【 】，裏面有非常詳細的介紹。

10.時間欄位

時間欄位 的型別，我們可以選擇的範圍還是比較多的，目前mysql支持：date、datetime、timestamp、varchar等。

varchar 型別可能是為了跟介面保持一致，介面中的時間型別是String。

但如果哪天我們要透過時間範圍查詢數據，效率會非常低，因為這種情況沒法走索引。

date 型別主要是為了保存 日期 ，比如：2020-08-20，不適合保存 日期和時間 ，比如：2020-08-20 12:12:20。

而 datetime 和 timestamp 型別更適合我們保存 日期和時間 。

但它們有略微區別。

優先推薦使用 datetime 型別保存日期和時間，可以保存的時間範圍更大一些。

溫馨提醒一下，在給時間欄位設定預設值是，建議不要設定成： 0000-00-00 00:00:00 ，不然查詢表時可能會因為轉換不了，而直接報錯。

11.金額欄位

mysql中有多個欄位可以表示浮點數：float、double、decimal等。

而 float 和 double 可能會遺失精度，因此推薦大家使用 decimal 型別保存金額。

一般我們是這樣定義浮點數的：decimal(m,n)。

其中 n 是指 小數 的長度，而 m 是指 整數加小數 的總長度。

假如我們定義的金額型別是這樣的：decimal(10,2)，則表示整數長度是8位元，並且保留2位小數。

12. json欄位

我們在設計表結構時，經常會遇到某個欄位保存的數據值不固定的需求。

舉個例子，比如：做異步excel匯出功能時，需要在異步任務表中加一個欄位，保存使用者透過前端頁面選擇的查詢條件，每個使用者的查詢條件可能都不一樣。

這種業務場景，使用傳統的資料庫欄位，不太好實作。

這時候就可以使用MySQL的json欄位型別了，可以保存json格式的結構化數據。

保存和查詢數據都是非常方便的。

MySQL還支持按欄位名稱或者欄位值，查詢json中的數據。

13.唯一索引

唯一索引 在我們實際工作中，使用頻率相當高。

你可以給單個欄位，加唯一索引，比如：組織機構code。

也可以給多個欄位，加一個聯合的唯一索引，比如：分類編號、單位、規格等。

單個的唯一索引還好，但如果是聯合的唯一索引，欄位值出現null時，則唯一性約束可能會失效。

關於唯一索引失效的問題，感興趣的小夥伴可以看看我的另一篇文章【 】。

建立唯一索引時，相關欄位一定不能包含null值，否則唯一性會失效。

14.字元集

mysql中支持的 字元集 有很多，常用的有：latin1、utf-8、utf8mb4、GBK等。

這4種字元集情況如下：

latin1 容易出現亂碼問題，在實際計畫中使用比較少。

而 GBK 支持中文，但不支持國際通用字元，在實際計畫中使用也不多。

從目前來看，mysql的字元集使用最多的還是： utf-8 和 utf8mb4 。

其中 utf-8 占用3個字節，比 utf8mb4 的4個字節，占用更小的儲存空間。

但utf-8有個問題：即無法儲存emoji表情，因為emoji表情一般需要4個字節。

由此，使用utf-8字元集，保存emoji表情時，資料庫會直接報錯。

所以，建議在建表時字元集設定成： utf8mb4 ，會省去很多不必要的麻煩。

15. 排序規則

不知道，你關註過沒，在mysql中建立表時，有個 COLLATE 參數可以設定。

例如：

CREATETABLE`order` (
`id`bigintNOTNULL AUTO_INCREMENT,
`code`varchar(20) COLLATE utf8mb4_bin NOTNULL,
`name`varchar(30) COLLATE utf8mb4_bin NOTNULL,
PRIMARY KEY (`id`),
UNIQUEKEY`un_code` (`code`),
KEY`un_code_name` (`code`,`name`) USING BTREE,
KEY`idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin


它是用來設定 排序規則 的。

字元排序規則跟字元集有關，比如：字元集如果是 utf8mb4 ，則字元排序規則也是以： utf8mb4_ 開頭的，常用的有： utf8mb4_general_ci 、 utf8mb4_bin 等。

其中utf8mb4_general_ci排序規則，對字母的大小寫不敏感。說得更直白一點，就是不區分大小寫。

而utf8mb4_bin排序規則，對字元大小寫敏感，也就是區分大小寫。

說實話，這一點還是非常重要的。

假如order表中現在有一條記錄，name的值是大寫的YOYO，但我們用小寫的yoyo去查，例如：

select * fromorderwherename='yoyo';


如果字元排序規則是utf8mb4_general_ci，則可以查出大寫的YOYO的那條數據。

如果字元排序規則是utf8mb4_bin，則查不出來。

由此，字元排序規則一定要根據實際的業務場景選擇，否則容易出現問題。

16.大欄位

我們在建立表時，對一些特殊欄位，要額外關註，比如： 大欄位 ，即占用較多儲存空間的欄位。

比如：使用者的評論，這就屬於一個大欄位，但這個欄位可長可短。

但一般會對評論的總長度做限制，比如：最多允許輸入500個字元。

如果直接定義成 text 型別，可能會浪費儲存空間，所以建議將這類欄位定義成 varchar 型別的儲存效率更高。

當然，我還見過更大的欄位，即該欄位直接保存合約數據。

一個合約可能會占 幾Mb 。

在mysql中保存這種數據，從系統設計的角度來說，本身就不太合理。

像合約這種非常大的數據，可以保存到 mongodb 中，然後在mysql的業務表中，保存mongodb表的id。

17.冗余欄位

我們在設計表的時候，為了效能考慮，提升查詢速度，有時可以冗余一些欄位。

舉個例子，比如：訂單表中一般會有userId欄位，用來記錄使用者的唯一標識。

但很多訂單的查詢頁面，或者訂單的明細頁面，除了需要顯示訂單資訊之外，還需要顯示使用者ID和使用者名稱稱。

如果訂單表和使用者表的數據量不多，我們可以直接用userId，將這兩張表join起來，查詢出使用者名稱稱。

但如果訂單表和使用者表的數據量都非常多，這樣join是比較消耗查詢效能的。

這時候我們可以透過冗余欄位的方案，來解決效能問題。

我們可以在訂單表中，可以再加一個userName欄位，在系統建立訂單時，將userId和userName同時寫值。

當然訂單表中歷史數據的userName是空的，可以刷一下歷史數據。

這樣調整之後，後面只需要查詢訂單表，即可查詢出我們所需要的數據。

不過冗余欄位的方案，有利也有弊。

對查詢效能有利。

但需要額外的儲存空間，還可能會有數據不一致的情況，比如使用者名稱稱修改了。

我們在實際業務場景中，需要綜合評估，冗余欄位方案不適用於所有業務場景。

18.註釋

我們在做表設計的時候，一定要把表和相關欄位的註釋加好。

例如下面這樣的：

CREATETABLE`sys_dept` (
`id`bigintNOTNULL AUTO_INCREMENT COMMENT'ID',
`name`varchar(30) NOTNULLCOMMENT'名稱',
`pid`bigintNOTNULLCOMMENT'上級部門',
`valid_status`tinyint(1) NOTNULLDEFAULT1COMMENT'有效狀態 1:有效 0:無效',
`create_user_id`bigintNOTNULLCOMMENT'建立人ID',
`create_user_name`varchar(30) NOTNULLCOMMENT'建立人名稱',
`create_time` datetime(3) DEFAULTNULLCOMMENT'建立日期',
`update_user_id`bigintDEFAULTNULLCOMMENT'修改人ID',
`update_user_name`varchar(30) DEFAULTNULLCOMMENT'修改人名稱',
`update_time` datetime(3) DEFAULTNULLCOMMENT'修改時間',
`is_del`tinyint(1) DEFAULT'0'COMMENT'是否刪除 1：已刪除 0：未刪除',
PRIMARY KEY (`id`) USING BTREE,
KEY`index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8mb4 COMMENT='部門';


表和欄位的註釋，都列舉的非常詳細。

特別是有些狀態型別的欄位，比如：valid_status欄位，該欄位表示有效狀態， 1:有效 0:無效。

讓人可以一目了然，表和欄位是幹什麽用的，欄位的值可能有哪些。

最怕的情況是，你在表中建立了很多status欄位，每個欄位都有1、2、3、4、5、6、7、8、9等多個值。

沒有寫什麽註釋。

誰都不知道1代表什麽含義，2代表什麽含義，3代表什麽含義。

可能剛開始你還記得。

但系統上線使用一年半載之後，可能連你自己也忘記了這些status欄位，每個值的具體含義了，埋下了一個巨坑。

由此，我們在做表設計時，一定要寫好相關的註釋，並且經常需要更新這些註釋。

👇🏻 點選下方閱讀原文，獲取魚皮往期編程幹貨。

往期推薦