04.MySQL數據類型詳解

MySQL數據類型詳解

文章目錄

MySQL數據類型
數據類型分類
數值類型
- tinyint類型
- bit類型
- float類型
- decimal類型
字符串類型
- char類型
- varchar類型
- char和varchar比較
時間日期類型
enum和set類型
數據類型選擇的進階技巧
常見誤區與解決方案
性能優化與最佳實踐

MySQL數據類型

數據類型的作用

數據類型不僅是存儲數據的容器，更是數據庫設計的基石。它決定了三個核心要素：

存儲空間：例如，存一個整數和一篇長文顯然需要不同的空間分配策略。
二進制解析方式：同一串二進制數據，用INT解讀是數字，用CHAR解讀可能變成亂碼。
取值范圍限制：年齡不可能是負數，性別只能是有限選項，這些都需要數據類型來規范。

此外，數據類型還影響索引效率、查詢優化器的選擇，甚至影響數據庫的擴展性和維護成本。例如，錯誤地使用VARCHAR(255)存儲固定長度的MD5值會導致空間浪費和查詢效率下降。

數據類型分類

MySQL的數據類型大致可分為四類，以下是詳細分類表：

分類	數據類型	說明
數值類型	BIT(M)	位類型，M默認1，范圍1-64位
	BOOL	布爾值（實際是TINYINT(1)）
	TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT	整型家族，字節數從1到8遞增
	FLOAT/DOUBLE/DECIMAL	浮點數和精確小數
字符串類型	CHAR(L)	固定長度字符串（最大255）
	VARCHAR(L)	可變長度字符串（最大65535字節）
	BLOB/TEXT	大文本/二進制數據
日期時間類型	DATE/DATETIME	日期格式YYYY-MM-DD，時間戳
	TIMESTAMP	自動更新的時間戳
集合類型	ENUM	枚舉（單選）
	SET	集合（多選）

冷知識：MySQL沒有獨立的布爾類型，用TINYINT(1)代替，0代表FALSE，1代表TRUE。但在某些ORM框架中，會自動將TINYINT(1)映射為布爾值。

數值類型

tinyint類型

有符號 vs 無符號

有符號：范圍-128~127（占1字節）
無符號：范圍0~255（同樣占1字節）

實戰案例：
假設設計一個用戶積分表：

CREATE TABLE user_points (user_id INT,points TINYINT UNSIGNED
);

如果積分上限為200，用TINYINT UNSIGNED足夠；但若未來需要支持更高積分（如500），則必須改為SMALLINT UNSIGNED，否則插入500會報錯。

為何慎用無符號？

無符號類型看似能節省空間，但可能埋下隱患：

擴展性差：如年齡字段若用TINYINT UNSIGNED（0-255），當遇到異常值（如輸入300）會直接報錯，而有符號類型可臨時存儲負數用于標記異常。
兼容性問題：某些編程語言或框架對無符號類型支持不佳，可能引發轉換錯誤。

bit類型

位類型的高級應用

BIT類型適合存儲開關狀態或權限位掩碼。例如，一個用戶權限字段：

CREATE TABLE user_permissions (user_id INT,perms BIT(8) -- 每位代表一種權限
);

插入權限：

INSERT INTO user_permissions VALUES (1, b'00000011'); -- 同時有第1和第2位權限

通過位運算查詢權限：

SELECT * FROM user_permissions WHERE perms & b'00000001'; -- 查找有第一位權限的用戶

顯示問題與解決方案

BIT類型顯示時按ASCII碼轉換可能導致混亂。例如：

INSERT INTO user_permissions VALUES (2, 10); -- 10對應ASCII換行符

查詢結果可能顯示為空白或特殊字符。解決方案：

應用層處理：將BIT轉換為整數或自定義字符串映射。
使用INT代替BIT：對于不超過32位的權限，直接用INT存儲更直觀。

float類型

精度陷阱與四舍五入

FLOAT(M,D)的M是總位數，D是小數位數。例如FLOAT(4,2)存儲范圍是-99.99到99.99，但實際可插入范圍是-99.994到99.994，超出時會四舍五入或報錯。

CREATE TABLE measurements (val FLOAT(4,2)
);
INSERT INTO measurements VALUES (99.994); -- 存儲為99.99
INSERT INTO measurements VALUES (99.995); -- 存儲為100.0，觸發報錯

何時選擇FLOAT vs DECIMAL？

FLOAT：適合科學計算，允許一定誤差（如傳感器數據）。
DECIMAL：金融場景必須使用，如存儲賬戶余額，避免浮點誤差。

decimal類型

精確計算的王者

DECIMAL的存儲機制使其成為金融系統的首選。例如：

CREATE TABLE accounts (balance DECIMAL(10,2)
);

存儲100.01時，DECIMAL確保精確到分，而FLOAT可能存儲為100.009999。

存儲開銷對比：
DECIMAL每4字節存9個數字，小數點單獨占1字節。例如DECIMAL(10,2)占用5字節（9個數字+1字節小數點），而FLOAT固定占4字節。

字符串類型

char類型

定長存儲的適用場景

CHAR(L)適用于長度固定的字符串，如身份證號（18位）、手機號（11位）。例如：

CREATE TABLE users (id_card CHAR(18)
);

插入不足18位時會自動補空格，查詢時尾部空格被自動去除。

性能優勢：

定長存儲便于快速定位，適合頻繁更新的字段。
作為主鍵時，CHAR比VARCHAR更高效（如UUID）。

varchar類型

變長存儲的靈活性

VARCHAR(L)適合長度波動大的字段，如用戶名、地址。例如：

CREATE TABLE addresses (street VARCHAR(100)
);

存儲"Main St"僅占用7字節（數據+1字節長度標識），而CHAR(100)會占用100字節。

編碼對最大長度的影響：

UTF8MB4下，VARCHAR(21844) ≈ 65532字節（21844 × 3字節/字符 + 2字節長度標識）
GBK下，VARCHAR(32766) ≈ 65532字節（32766 × 2字節/字符 + 2字節長度標識）

最佳實踐：

定義表時顯式指定字符集：CREATE TABLE example (...) CHARSET=utf8mb4;
避免過度使用VARCHAR(255)：根據實際數據長度選擇合適值，節省空間并提高緩存效率。

char和varchar比較

特性	CHAR	VARCHAR
空間占用	固定L字符	實際長度+1~2字節
速度	快（定長）	稍慢（需讀長度）
適用場景	身份證號、手機號	名字、地址等變長字段

選擇建議：

長度固定的字段（如MD5值）用CHAR
長度波動大的字段（如文章內容）用VARCHAR

擴展案例：
存儲IP地址時，CHAR(15)（如"192.168.1.1"）比VARCHAR更高效，因為IPv4地址固定為15字符以內。

時間日期類型

三大時間類型對比

類型	格式	占用空間	特點
DATE	YYYY-MM-DD	3字節	只存日期
DATETIME	YYYY-MM-DD HH:MM:SS	8字節	范圍1000-9999年
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4字節	自動更新，默認當前時間

實戰案例：
設計評論表時，使用TIMESTAMP記錄發布時間：

CREATE TABLE comments (id INT,content TEXT,create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入數據時無需指定create_time，自動填充當前時間。更新記錄時，可設置TIMESTAMP自動更新：

ALTER TABLE comments ADD update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

時區問題：

DATETIME不存儲時區信息，顯示依賴客戶端設置。
TIMESTAMP存儲UTC時間，查詢時自動轉換為當前時區。

enum和set類型

枚舉類型（ENUM）

單選場景優化

ENUM適合選項固定的單選字段，如訂單狀態：

CREATE TABLE orders (status ENUM('pending', 'processing', 'shipped', 'canceled')
);

插入非枚舉值會報錯，確保數據一致性。

內部存儲機制：
ENUM存儲為數字索引（1-based），如ENUM('a','b','c')中，a=1，b=2，c=3。可通過數字訪問：

SELECT * FROM orders WHERE status = 3; -- 查找所有已取消訂單

但不推薦此方式，可讀性差。

集合類型（SET）

多選場景優化

SET適合多選字段，如用戶興趣標簽：

CREATE TABLE users (interests SET('sports', 'music', 'reading')
);

插入多選值：

INSERT INTO users VALUES ('sports,music');

位運算原理：
SET用位圖存儲，每個選項對應一個二進制位：

sports = 1 (0b0001)
music = 2 (0b0010)
reading = 4 (0b0100)

查詢包含"music"的用戶：

SELECT * FROM users WHERE interests & 2;

局限性：

最多64個選項。
修改枚舉/集合列表需ALTER TABLE，不適合動態選項。

數據類型選擇的技巧

1. 空間與性能的權衡

數值類型：優先使用最小能滿足需求的類型。例如，年齡字段用TINYINT而非INT。
字符串類型：避免濫用VARCHAR(255)，根據實際數據長度選擇，減少內存占用。
日期類型：若只需日期（如生日），用DATE而非DATETIME，節省5字節存儲。

2. 金融場景的必殺技

涉及金額字段必須使用DECIMAL，避免FLOAT/DOUBLE的精度問題。例如：

CREATE TABLE transactions (amount DECIMAL(15,4) -- 精確到分，保留4位小數
);

3. JSON類型的應用

MySQL 5.7+支持JSON類型，適合存儲半結構化數據：

CREATE TABLE settings (user_id INT,preferences JSON
);
INSERT INTO settings VALUES (1, '{"theme": "dark", "notifications": true}');

查詢JSON字段：

SELECT * FROM settings WHERE JSON_EXTRACT(preferences, '$.theme') = 'dark';

常見誤區與解決方案

誤區1：盲目使用INT存儲一切數值

問題：用INT存儲IP地址（如192.168.1.1轉為3232235779），查詢時需轉換回字符串。
解決方案：使用INET_ATON()和INET_NTOA()函數，或直接用CHAR(15)存儲。

誤區2：過度依賴ENUM/SET

問題：ENUM選項頻繁變動時需頻繁執行ALTER TABLE。
解決方案：用外鍵關聯獨立的狀態表，如：

CREATE TABLE order_statuses (id TINYINT PRIMARY KEY,name VARCHAR(20)
);
CREATE TABLE orders (status_id TINYINT,FOREIGN KEY (status_id) REFERENCES order_statuses(id)
);