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

一、MySQL建表語(yǔ)句

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

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

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

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

1、CHAR

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

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

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

2、VARCHAR

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

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

MySQL 8.0 版本字符集默認(rèn)設(shè)置成 UTF8MB4，UTF8MB4 字符集 1 個(gè)字符最大存儲(chǔ) 4 個(gè)字節(jié)，8.0 版本之前默認(rèn)的字符集為L(zhǎng)atin1。

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

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

枚舉類型設(shè)計(jì)

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

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

這里博主要跟這些資料唱個(gè)反調(diào)，我們?cè)诠こ讨械臓顟B(tài)，基本都是我們手動(dòng)set的，這里博主認(rèn)為如果使用了 ENUM 字符串枚舉類型恰恰不利于互聯(lián)網(wǎng)的高速擴(kuò)展的設(shè)計(jì)原則。

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

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

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

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

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

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

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

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

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

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

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

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

而事實(shí)上真的是這樣嗎？

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

`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，我們?cè)诖鎯?chǔ)金額的時(shí)候一般是分為單位，例如100,.00就是 1 元，當(dāng)我們下單金額例如100元，我們的庫(kù)里就會(huì)落 10000.00，但是這比訂單購(gòu)買了1個(gè)item商品3件sku，這100元就要分?jǐn)偨o這3件sku商品，這時(shí)候?qū)τ诜謹(jǐn)偟挠?jì)算，在代碼中int、long類型沒(méi)有BigDecimal 計(jì)算的精準(zhǔn)。

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

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

因?yàn)闃I(yè)務(wù)絕大部分場(chǎng)景都需要將日期精確到秒，所以在表結(jié)構(gòu)設(shè)計(jì)中，常見(jiàn)使用的日期類型為DATETIME 和 TIMESTAMP。

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

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

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

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

`CREATE_TIME` datetime(0) NOT NULL COMMENT '創(chuàng)建時(shí)間',
`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 '更新時(shí)間',
`UPDATE_BY` varchar(32) CHARACTER NOT NULL COMMENT '更新人',

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

五、MySQL高擴(kuò)展JSON設(shè)計(jì)與實(shí)戰(zhàn)

關(guān)系型的結(jié)構(gòu)化存儲(chǔ)存在一定的弊端，因?yàn)樗枰A(yù)先定義好所有的列以及列對(duì)應(yīng)的類型。但是業(yè)務(wù)在發(fā)展過(guò)程中，或許需要擴(kuò)展單個(gè)列的描述功能。

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

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

但是這里，博主并不推薦大家這么做，因?yàn)镴SON類型及其難維護(hù)，并且寫(xiě)sql的時(shí)候很麻煩

我舉個(gè)例子，我想插入一條信息，我需要

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

INSERT INTO 表名 VALUES ( , @item_info);

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

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

總結(jié)

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

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

最新評(píng)論