MySQL中建表與常見的類型設計陷阱詳解

Posted on by

本文作為MySQL系列第三篇文章，詳細講解瞭MySQL的建表語句、以及表結構的設計規范和陷阱，對網絡上常見的資料給出的設計方案，做瞭博主自己的理解和反駁。

一、MySQL建表語句

MySQL建表語句很簡單，CREATE TABLE 表名 ()，在其中設置表的列（屬性）即可。

CREATE TABLE `表名`  (
    // 定義屬性
    // 定義索引
) // 設置表屬性;

二、MySQL建表字符串類型設計

MySQL 數據庫的字符串類型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET，其中最常使用的是 CHAR、VARCHAR。

1、CHAR

CHAR(N) 用來保存固定長度的字符（在Unicode字符集，Utf-8、Utf-16、Utf-32是這樣的），N 的范圍是 0 ~ 255，請牢記，N 表示的是字符，而不是字節。

在表結構設計中還需要額外定義建表對應的字符集。多字節字符集 (MBCS)，通常指的是ANSI、中文編碼以及Shift-jis，jis，euc-jp，euc-kr等。Unicode字符集，Unicode字符集即平常說的寬字節，包含Utf-8、Utf-16、Utf-32。

常見的字符集有 GBK、UTF8，通常推薦把默認字符集設置為 UTF8。

2、VARCHAR

VARCHAR(N) 用來保存變長字符，N 的范圍為 0 ~ 65536， N 表示字符。在超出 65536 個字符的情況下，可以考慮使用更大的字符類型 TEXT 或 BLOB，兩者最大存儲長度為 4G，其區別是 BLOB 沒有字符集屬性，純屬二進制存儲。

隨著移動互聯網的飛速發展，推薦把 MySQL 的默認字符集設置為 UTF8MB4，否則，某些 emoji 表情字符無法在 UTF8 字符集下存儲。

MySQL 8.0 版本字符集默認設置成 UTF8MB4，UTF8MB4 字符集 1 個字符最大存儲 4 個字節，8.0 版本之前默認的字符集為Latin1。

鑒於目前默認字符集推薦設置為 UTF8MB4，所以在表結構設計時，可以把 CHAR 全部用 VARCHAR 替換，底層存儲的本質實現一模一樣。

3、枚舉類型設計實戰

枚舉類型設計

設計表結構時，你會遇到一些固定選項值的字段。例如狀態字段（***_state），有效的值為有限狀態，例如01（訂單初始狀態）、02（下單成功）、03（支付中）……。

很多學習資料和博客推薦在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚舉類型，隻允許有限的定義值插入。如果將參數 SQL_MODE 設置為嚴格模式，插入非定義數據就會報錯。

這裡博主要跟這些資料唱個反調，我們在工程中的狀態，基本都是我們手動set的，這裡博主認為如果使用瞭 ENUM 字符串枚舉類型恰恰不利於互聯網的高速擴展的設計原則。

在這裡我推薦在工程中維護一個 ENUM 枚舉類，我們對數據庫操作的的時候狀態或者相關枚舉類型的字段從枚舉類中獲取，這樣方便維護，並且利於擴展。

`TXN_TYPE` varchar(8) CHARACTER  NOT NULL COMMENT '交易類型|消費:SQT,退貨:SQRT',

三、MySQL建表ID和金額的設計與實戰

1、ID自增的設計

進行實戰設計之前，我們需要瞭解整型類型，

MySQL 數據庫支持 SQL 標準支持的整型類型：INT、SMALLINT、TINYINT、MEDIUMINT 和 BIGINT 整型類型。INT占用4字節，取值范圍是-2147483648 ~ 2147483647（2^31），BIGINT占用8字節，-9223372036854775808 ~9223372036854775807（2^63）

除瞭整型類型，數字類型還有浮點和高精度類型。MySQL 之前的版本中存在浮點類型 Float 和 Double，在真實的生產環境中不推薦使用，在計算時由於精度類型問題，會導致最終的計算結果出錯。

ID一般我們會設置為自增，結合 auto_increment，可以實現自增功能，但在表結構設計時用自增做主鍵一般隻會使用 BIGINT 類型做主鍵。

`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',

原因有幾點一是為瞭擴展性，int 的取值范圍不一定適用互聯網場景的增速，這裡面需要註意MySQL 8.0 版本前自增不持久化，自增值可能會存在回溯問題，例如 1/2/3/4，我把4刪點，再次插入的時候，主鍵ID還是 1/2/3/4，這就是回溯問題，解決辦法就是在使用的時候評估這個方案會不會有影響，或者直接升級MySQL。

2、互聯網企業金額字段設計原理

我們常常在其他博客看到這樣一種說法“在海量互聯網業務的設計標準中，並不推薦用 DECIMAL 類型，而是更推薦將 DECIMAL 轉化為 整型 BIGINT類型。”，他給出的理由是所有金額相關字段都是定長字段，占用 8 個字節，存儲高效。第二直接通過整型計算，效率更高。

而事實上真的是這樣嗎？

金額字段的取值范圍如果用 DECIMAL 表示的，則定義為 DECIMAL(16,2) ，這樣滿足的萬億以上的場景瞭。

`TRANS_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子單交易金額',
`CASH_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子單現金金額',
`POINT_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子單積分金額',

為什麼我推薦使用DECIMAL而不是BIGINT，我們在存儲金額的時候一般是分為單位，例如100,.00就是 1 元，當我們下單金額例如100元，我們的庫裡就會落 10000.00，但是這比訂單購買瞭1個item商品3件sku，這100元就要分攤給這3件sku商品，這時候對於分攤的計算，在代碼中int、long類型沒有BigDecimal 計算的精準。

四、MySQL建表時間類型設計與實戰

MySQL 數據庫中常見的日期類型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。

因為業務絕大部分場景都需要將日期精確到秒，所以在表結構設計中，常見使用的日期類型為DATETIME 和 TIMESTAMP。

這裡面TIMESTAMP有一個大坑，TIMESTAMP 其實際存儲的內容為‘1970-01-01 00:00:00’到現在的毫秒數。在 MySQL 中，由於類型 TIMESTAMP 占用 4 個字節，因此其存儲的時間上限隻能到‘2038-01-19 03:14:07’。

我們工程中，生產環境等等一般使用的是DATETIME， DATETIME 最終展現的形式為：YYYY-MM-DD HH：MM：SS，固定占用 8 個字節。

從 MySQL 5.6 版本開始，DATETIME 類型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存儲 6 位的毫秒值。同時，DATETIME 不存在時區轉化問題。一般是在國際化項目中，服務器端或者前端進行轉換，這樣查詢或者變更效率更高。

每個表都要有一個時間字段， 在做表結構設計規范時，強烈建議你每張業務核心表都增加一個 DATETIME 類型的 last_modify_date 字段，並設置修改自動更新機制， 即便標識每條記錄最後修改的時間。開發人員可以知道每次操作記錄更新的時間，以便做後續的處理。

`CREATE_TIME` datetime(0) NOT NULL COMMENT '創建時間',
`CREATE_BY` varchar(32) NOT NULL COMMENT ' 創建人',
`UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新時間',
`UPDATE_BY` varchar(32) CHARACTER NOT NULL COMMENT '更新人',

比如在電商的訂單表中，可以方便對支付超時的訂單做處理；在金融業務中，可以根據用戶資金最後的修改時間做相應的資金軋差等。

五、MySQL高擴展JSON設計與實戰

關系型的結構化存儲存在一定的弊端，因為它需要預先定義好所有的列以及列對應的類型。但是業務在發展過程中，或許需要擴展單個列的描述功能。

這時，如果能用好 JSON 數據類型，那就能打通關系型和非關系型數據的存儲之間的界限，為業務提供更好的架構選擇。JSON 類型的另一個好處是無須預定義字段，字段可以無限擴展。

`ITEM_INFO` JSON COMMENT '商品信息',

但是這裡，博主並不推薦大傢這麼做，因為JSON類型及其難維護，並且寫sql的時候很麻煩

我舉個例子，我想插入一條信息，我需要

SET @item_info = '{
	"item_id" : "12345",
	"item_amt" : "1024.00"
}';

INSERT INTO 表名 VALUES ( , @item_info);

一般在生產中我們這樣處理，在定義時，定義一個超大的字符串類型，在代碼中使用JSON轉換成一個JSON對象的字符串，保存。

`ITEM_INFO` varchar(1000) CHARACTER DEFAULT NULL COMMENT '商品信息',

總結

本文作為MySQL系列第三篇文章，詳細講解瞭MySQL的建表語句、以及表結構的設計規范和陷阱，對網絡上常見的資料給出的設計方案，做瞭博主自己的理解和反駁。

到此這篇關於MySQL中建表與常見的類型設計陷阱詳解的文章就介紹到這瞭,更多相關MySQL建表 類型設計內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：