【MySQL】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);      

`ITEM_INFO` varchar(1000) CHARACTER DEFAULT NULL COMMENT '商品資訊',      

總結