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

2022-11-07 14:01:47

一、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建表型別設計內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！