本文作為MySQL系列第三篇文章，詳細講解了MySQL的建表語句、以及表結(jié)構(gòu)的設(shè)計規(guī)范和陷阱，對網(wǎng)絡(luò)上常見的資料給出的設(shè)計方案，做了博主自己的理解和反駁。

一、MySQL建表語句

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

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

二、MySQL建表字符串類型設(shè)計

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

1、CHAR

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

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

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

2、VARCHAR

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

隨著移動互聯(lián)網(wǎng)的飛速發(fā)展，推薦把 MySQL 的默認字符集設(shè)置為 UTF8MB4，否則，某些 emoji 表情字符無法在 UTF8 字符集下存儲。

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

鑒于目前默認字符集推薦設(shè)置為 UTF8MB4，所以在表結(jié)構(gòu)設(shè)計時，可以把 CHAR 全部用 VARCHAR 替換，底層存儲的本質(zhì)實現(xiàn)一模一樣。

3、枚舉類型設(shè)計實戰(zhàn)

枚舉類型設(shè)計

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

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

這里博主要跟這些資料唱個反調(diào)，我們在工程中的狀態(tài)，基本都是我們手動set的，這里博主認為如果使用了 ENUM 字符串枚舉類型恰恰不利于互聯(lián)網(wǎng)的高速擴展的設(shè)計原則。

在這里我推薦在工程中維護一個 ENUM 枚舉類，我們對數(shù)據(jù)庫操作的的時候狀態(tài)或者相關(guān)枚舉類型的字段從枚舉類中獲取，這樣方便維護，并且利于擴展。

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

三、MySQL建表ID和金額的設(shè)計與實戰(zhàn)

1、ID自增的設(shè)計

進行實戰(zhàn)設(shè)計之前，我們需要了解整型類型，

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

除了整型類型，數(shù)字類型還有浮點和高精度類型。MySQL 之前的版本中存在浮點類型 Float 和 Double，在真實的生產(chǎn)環(huán)境中不推薦使用，在計算時由于精度類型問題，會導致最終的計算結(jié)果出錯。

ID一般我們會設(shè)置為自增，結(jié)合 auto_increment，可以實現(xiàn)自增功能，但在表結(jié)構(gòu)設(shè)計時用自增做主鍵一般只會使用 BIGINT 類型做主鍵。

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

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

2、互聯(lián)網(wǎng)企業(yè)金額字段設(shè)計原理

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

而事實上真的是這樣嗎？

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

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

為什么我推薦使用DECIMAL而不是BIGINT，我們在存儲金額的時候一般是分為單位，例如100,.00就是 1 元，當我們下單金額例如100元，我們的庫里就會落 10000.00，但是這比訂單購買了1個item商品3件sku，這100元就要分攤給這3件sku商品，這時候?qū)τ诜謹偟挠嬎悖诖a中int、long類型沒有BigDecimal 計算的精準。

四、MySQL建表時間類型設(shè)計與實戰(zhàn)

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

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

這里面TIMESTAMP有一個大坑，TIMESTAMP 其實際存儲的內(nèi)容為‘1970-01-01 00:00:00’到現(xiàn)在的毫秒數(shù)。在 MySQL 中，由于類型 TIMESTAMP 占用 4 個字節(jié)，因此其存儲的時間上限只能到‘2038-01-19 03:14:07’。

我們工程中，生產(chǎn)環(huán)境等等一般使用的是DATETIME， DATETIME 最終展現(xiàn)的形式為：YYYY-MM-DD HH：MM：SS，固定占用 8 個字節(jié)。

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

每個表都要有一個時間字段， 在做表結(jié)構(gòu)設(shè)計規(guī)范時，強烈建議你每張業(yè)務核心表都增加一個 DATETIME 類型的 last_modify_date 字段，并設(shè)置修改自動更新機制， 即便標識每條記錄最后修改的時間。開發(fā)人員可以知道每次操作記錄更新的時間，以便做后續(xù)的處理。

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

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

五、MySQL高擴展JSON設(shè)計與實戰(zhàn)

關(guān)系型的結(jié)構(gòu)化存儲存在一定的弊端，因為它需要預先定義好所有的列以及列對應的類型。但是業(yè)務在發(fā)展過程中，或許需要擴展單個列的描述功能。

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

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

但是這里，博主并不推薦大家這么做，因為JSON類型及其難維護，并且寫sql的時候很麻煩

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

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

INSERT INTO 表名 VALUES ( , @item_info);

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

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

總結(jié)

本文作為MySQL系列第三篇文章，詳細講解了MySQL的建表語句、以及表結(jié)構(gòu)的設(shè)計規(guī)范和陷阱，對網(wǎng)絡(luò)上常見的資料給出的設(shè)計方案，做了博主自己的理解和反駁。

到此這篇關(guān)于MySQL中建表與常見的類型設(shè)計陷阱詳解的文章就介紹到這了,更多相關(guān)MySQL建表 類型設(shè)計內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論