目錄

一、為什么需要數據類型與約束？

二、MySQL 數據類型全覽

1. 數值類型：精確 VS 近似

2. 日期時間類型：別讓“0000-00-00”出現

3. 字符串類型：CHAR、VARCHAR、TEXT、BLOB

4. JSON 類型：文檔與關系共舞

5. 空間類型：GIS 場景

6. 二進制與位類型

三、約束：給數據裝上“安檢門”

1. 列級約束：NOT NULL、DEFAULT、UNIQUE、CHECK

2. 主鍵與復合主鍵

3. 外鍵：一把雙刃劍

4. 表級 CHECK 與觸發器

5. 視圖與權限：最后一道軟性約束

四、聯動設計：把類型與約束串成故事

1. 用戶表

2. 訂單表

3. 支付表

4. 索引與性能

五、最佳實踐 5 條

六、結語

---

一、為什么需要數據類型與約束？

想象你在咖啡館點單：
“我要一杯拿鐵，中杯，加一份濃縮，半糖，少冰。”
如果服務員只在本子上寫“拿鐵”，結果可能端來超大杯、全糖、冰多到溢出。
數據庫同理：字段若不聲明“多大”“什么格式”“能否為空”，就會像“拿鐵”一樣失控——數字被截斷、日期變 0000-00-00、字符串亂碼、金額出現負值……
數據類型解決“存得對”，約束保證“存得準”。二者共同構成 MySQL 的第一道防線，也是性能與可維護性的根基。

二、MySQL 數據類型全覽

1. 數值類型：精確 VS 近似

大類	典型	字節	范圍（有符號）	場景
整數	TINYINT	1	-128~127	性別、布爾
整數	INT	4	-21 億~21 億	主鍵、計數器
大整數	BIGINT	8	很大	雪花 ID
定點	DECIMAL(M,D)	變長	精確小數	金額
浮點	FLOAT/DOUBLE	4/8	近似值	溫度、GPS

陷阱與建議：

• 金額永遠用 DECIMAL，不要用 DOUBLE。DOUBLE 的二進制浮點誤差會讓 0.1+0.2≠0.3。

• 主鍵自增別用 BIGINT(20)“嚇唬”自己，除非預估 9.22e18 行，否則 INT 足夠。

• UNSIGNED 讓上限翻倍，但 BIGINT UNSIGNED 與 Java long 互轉時會溢出，需在 ORM 層注意。

示例：

CREATE TABLE goods (id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,price     DECIMAL(10,2) NOT NULL,stock     INT UNSIGNED DEFAULT 0
);

2. 日期時間類型：別讓“0000-00-00”出現

類型	字節	范圍	精確度	備注
DATE	3	1000-01-01~9999-12-31	天	生日
DATETIME	8	同上	秒	默認無時區
DATETIME(fsp)	8+小數	同上	微秒	MySQL 5.6+
TIMESTAMP	4	1970-2038	秒	自動時區轉換
TIME	3	-838:59:59~838:59:59	秒	時長
YEAR	1	1901-2155	年	幾乎不用

陷阱：

• 舊版本 MySQL 允許 0000-00-00，但 JDBC、Python 驅動會拋異常；務必 sql_mode=NO_ZERO_DATE。

• TIMESTAMP 受時區影響，跨地域系統用 DATETIME+fsp 更穩。

• 存儲毫秒級時間戳可直接用 BIGINT 存 Unix 毫秒，避免 DATETIME 精度不夠或 TIMESTAMP 2038 問題。

示例：

CREATE TABLE event_log (id        BIGINT PRIMARY KEY,happen_at DATETIME(3) NOT NULL,INDEX idx_happen (happen_at)
);

3. 字符串類型：CHAR、VARCHAR、TEXT、BLOB

• CHAR(n) 定長，最大 255，尾部空格自動截斷；適合短且等長碼值，如國家代碼 CHAR(2)。

• VARCHAR(n) 變長，最大 65535 字節，受行大小 65535 限制；utf8mb4 下一個字符 4 字節，所以 VARCHAR(16383) 是極限。

• TEXT 家族（TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT）存大文本，不能設默認值，不能完整索引（需前綴索引）。

• BLOB 家族存二進制，如圖片、PDF；同樣不能設默認值，讀寫會走磁盤臨時文件，慎用。

陷阱：

• VARCHAR(255) 不等于 255 字符，而是 255 字節；utf8mb4 下最多 63 個漢字。

• 用 TEXT 存 JSON 不如直接用 JSON 類型（見下）。

• 大字段會觸發“行溢出”，InnoDB 把值存到頁外，隨機 IO 增加。

示例：

CREATE TABLE article (id      BIGINT PRIMARY KEY,title   VARCHAR(200) NOT NULL,body    MEDIUMTEXT,cover   LONGBLOB
) CHARSET=utf8mb4;

4. JSON 類型：文檔與關系共舞

MySQL 5.7+ 原生 JSON，二進制存儲、可部分更新。支持函數 ->、->>、JSON_EXTRACT()、JSON_SET()。
優點：schema-less，適合動態字段。缺點：無法直接建外鍵、無法默認值。
示例：

CREATE TABLE user_ext (user_id BIGINT PRIMARY KEY,profile JSON,CHECK (JSON_VALID(profile))
);-- 查詢
SELECT profile->>'$.nickname' AS nick
FROM user_ext
WHERE JSON_CONTAINS(profile->'$.tags', '"vip"');

5. 空間類型：GIS 場景

• GEOMETRY、POINT、LINESTRING、POLYGON……

• 需表引擎 InnoDB 或 MyISAM，建 SPATIAL INDEX。

• 8.0 引入 SRID 強制坐標系，避免“經緯度顛倒”。

示例：

CREATE TABLE shop (id   BIGINT PRIMARY KEY,loc  POINT NOT NULL SRID 4326,SPATIAL INDEX idx_loc (loc)
);

6. 二進制與位類型

• BINARY/VARBINARY：與 CHAR/VARCHAR 類似，但存字節而非字符，適合存哈希。

• BIT：最大 64 位，存布爾標志位，省空間但可讀性差。

• ENUM/SET：背后用 1~8 字節存位圖，可節省空間，但遷移成本高，不建議濫用。

三、約束：給數據裝上“安檢門”

1. 列級約束：NOT NULL、DEFAULT、UNIQUE、CHECK

• NOT NULL：拒絕 NULL；NULL 與任何值比較都未知，導致索引失效。

• DEFAULT：顯式優于隱式；DEFAULT CURRENT_TIMESTAMP 記錄創建時間。

• UNIQUE：允許 NULL，但 NULL≠NULL，因此可出現多條 NULL；8.0.13 前不能有重復 NULL。

• CHECK：8.0.16 原生支持，之前僅解析忽略；可寫表達式 CHECK (age BETWEEN 0 AND 150)。

示例：

CREATE TABLE member (id     BIGINT PRIMARY KEY,email  VARCHAR(255) NOT NULL UNIQUE,age    TINYINT CHECK (age BETWEEN 0 AND 150),status ENUM('NEW','VIP','BAN') DEFAULT 'NEW'
);

2. 主鍵與復合主鍵

• 主鍵 = NOT NULL + UNIQUE；InnoDB 聚簇索引，整張表物理順序按主鍵排序。

• 業務主鍵 vs 代理主鍵：自增 BIGINT 簡單，但分布式場景用雪花 ID、UUID；后者無序會導致頁分裂。

• 復合主鍵 (tenant_id, id) 可做分庫分表“聯合主鍵”，但所有二級索引都需回表兩次。

3. 外鍵：一把雙刃劍

CREATE TABLE orders (id      BIGINT PRIMARY KEY,user_id BIGINT NOT NULL,FOREIGN KEY (user_id) REFERENCES user(id)ON UPDATE CASCADEON DELETE RESTRICT
);

優點：保證引用完整性；缺點：高并發寫入時級聯更新/刪除會鎖多表，互聯網大廠常關閉外鍵，靠業務層保證。

4. 表級 CHECK 與觸發器

• 如果 CHECK 表達式復雜（如跨列、跨行），可用 BEFORE INSERT 觸發器。

• 觸發器可寫業務邏輯，但隱藏、難調試，盡量收斂到“數據校驗”而非“業務流程”。

示例：

DELIMITER $$
CREATE TRIGGER trg_order_amount
BEFORE INSERT ON orders
FOR EACH ROW
BEGINIF NEW.amount <= 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'amount must be positive';END IF;
END$$
DELIMITER ;

5. 視圖與權限：最后一道軟性約束

• 通過只讀視圖屏蔽危險列；

• 用 DEFINER 與 SQL SECURITY INVOKER 做行級安全。

四、聯動設計：把類型與約束串成故事

場景：設計“用戶-訂單-支付”核心表，要求：

• 用戶手機號唯一；

• 訂單金額必須大于 0；

• 支付記錄必須與訂單同幣種；

• 支持軟刪除。

1. 用戶表

CREATE TABLE user (id        BIGINT PRIMARY KEY,phone     CHAR(11) NOT NULL UNIQUE,nickname  VARCHAR(50),deleted   TINYINT(1) DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARSET=utf8mb4;

2. 訂單表

CREATE TABLE orders (id        BIGINT PRIMARY KEY,user_id   BIGINT NOT NULL,amount    DECIMAL(10,2) NOT NULL CHECK (amount > 0),currency  CHAR(3) DEFAULT 'CNY',status    ENUM('PENDING','PAID','CLOSED') DEFAULT 'PENDING',deleted   TINYINT(1) DEFAULT 0,FOREIGN KEY (user_id) REFERENCES user(id)ON DELETE RESTRICTON UPDATE CASCADE
);

3. 支付表

CREATE TABLE payment (id        BIGINT PRIMARY KEY,order_id  BIGINT NOT NULL,currency  CHAR(3) NOT NULL,pay_amount DECIMAL(10,2) NOT NULL CHECK (pay_amount > 0),paid_at   DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (order_id) REFERENCES orders(id)ON DELETE RESTRICT,CONSTRAINT chk_currency_matchCHECK (currency = (SELECT currency FROM orders WHERE id = order_id))
);

注意：MySQL 8.0.16+ 才支持子查詢 CHECK；低版本需觸發器實現。

4. 索引與性能

• 外鍵會自動創建索引，但 deleted 列需手動加聯合索引 (deleted, status) 以便軟刪除列表查詢。

• DECIMAL 精確字段可用“整數分”代替：存分為 INT，避免浮點運算。

五、最佳實踐 5 條

1. 先選類型，再加約束：類型決定存儲空間與運算方式，約束只是“護欄”。

2. 金額用 DECIMAL(10,2) + CHECK>0；時間用 DATETIME(3) + DEFAULT CURRENT_TIMESTAMP(3)。

3. 枚舉值用 TINYINT 或 VARCHAR 存代碼，留擴展空間；ENUM 僅用于非常穩定的狀態機。

4. 外鍵在 OLTP 關閉，在 OLAP 打開；或只在測試環境打開做 CI。

5. 上線前跑 pt-osc 或 gh-ost 做無鎖變更，防止加約束導致表鎖。

六、結語

數據類型與約束是 MySQL 的地基。
類型選錯，查詢再花哨也如沙上建塔；約束缺失，業務再健壯也靠運氣生存。
愿你在每一次 CREATE TABLE 時，都能像挑剔的點單顧客，把“中杯、半糖、少冰”說得清清楚楚，讓數據庫端出的每一杯“數據拿鐵”都剛剛好。