MySQL 資料型別定義直接影響儲存空間、效能與資料正確性。本文透過實測 SQL 驗證 CHAR、VARCHAR、TEXT/BLOB、DECIMAL 的長度限制、字元集影響、行大小上限、儲存空間計算等核心特性。
CHAR(M) — 定長字串
M 為字元數，最大 255。- 儲存長度 =
M × 字元集位元組數（utf8 為 3，utf8mb4 為 4）。
- 不足右側補空格，讀取時尾部空格會被移除（
PAD_CHAR_TO_FULL_LENGTH 模式除外）。
mysql> CREATE TABLE t1(name CHAR(256)) DEFAULT CHARSET=utf8;
ERROR 1074: Column length too big for column 'name' (max = 255)
mysql> CREATE TABLE t1(name CHAR(255)) DEFAULT CHARSET=utf8;
Query OK
mysql> INSERT INTO t1 VALUES(REPEAT('整',255));
mysql> SELECT LENGTH(name), CHAR_LENGTH(name) FROM t1;
+--------------+-------------------+
| LENGTH(name) | CHAR_LENGTH(name) |
+--------------+-------------------+
| 765 | 255 |
+--------------+-------------------+
VARCHAR(M) — 變長字串
M 為字元數，受行大小上限 65535 位元組限制。- 實際儲存 = 實際長度 + 1~2 位元組長度前綴（<255 用 1 位元組，≥255 用 2 位元組）。
- 字元集影響最大字元數：latin1(1B) → 65532，utf8(3B) → 21844，utf8mb4(4B) → 16383。
-- latin1 環境
mysql> CREATE TABLE t1(name VARCHAR(65532)); -- OK
mysql> CREATE TABLE t1(name VARCHAR(65533)); -- ERROR 1118 Row size too large
-- utf8 環境
mysql> CREATE TABLE t1(name VARCHAR(21844)) DEFAULT CHARSET=utf8; -- OK
mysql> CREATE TABLE t1(name VARCHAR(21845)) DEFAULT CHARSET=utf8; -- ERROR 1118
-- 行大小包含所有非 BLOB/TEXT 欄位
mysql> CREATE TABLE t1(name VARCHAR(65528), hiredate DATETIME) DEFAULT CHARSET=latin1;
ERROR 1118 -- DATETIME 佔 5 位元組，超過 65535
TEXT / BLOB — 大物件
- 不計入行大小 65535 限制（僅儲存 20 位元組指標在行內）。
- 長度前綴自動分級：
| 型別 | 最大位元組數 | 長度前綴 |
|---|
| TINYTEXT/BLOB | 255 (2^8-1) | 1 位元組 |
| TEXT/BLOB | 65,535 (2^16-1) | 2 位元組 |
| MEDIUMTEXT/BLOB | 16,777,215 (2^24-1) | 3 位元組 |
| LONGTEXT/BLOB | 4,294,967,295 (2^32-1) | 4 位元組 |
mysql> CREATE TABLE t1(name TEXT(255)); -- 實際為 TINYTEXT
mysql> CREATE TABLE t2(name TEXT(256)); -- 實際為 TEXT
mysql> SHOW CREATE TABLE t1\G -- 顯示 TINYTEXT
mysql> SHOW CREATE TABLE t2\G -- 顯示 TEXT
-- utf8 環境實測
mysql> CREATE TABLE t1(name TEXT) DEFAULT CHARSET=utf8;
mysql> INSERT INTO t1 VALUES(REPEAT('整',21846));
ERROR 1406: Data too long for column 'name' at row 1
mysql> INSERT INTO t1 VALUES(REPEAT('整',21845));
Query OK -- 實際字元上限 = 65535 / 3 = 21845
DECIMAL(P,S) — 精確定點數
- 每 9 位數字佔 4 位元組，整數部與小數部分別計算。
- 餘數位元組對照表：
| 剩餘位數 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|
| 位元組 | 0 | 1 | 1 | 2 | 2 | 3 | 3 | 4 | 4 | 4 |
mysql> CREATE TABLE t2(id DECIMAL(6,5)); -- 整數1位+小數5位 = 1+3=4 bytes
mysql> CREATE TABLE t3(id DECIMAL(9,0)); -- 整數9位 = 4 bytes
mysql> CREATE TABLE t4(id DECIMAL(8,3)); -- 整數5位+小數3位 = 3+2=5 bytes
-- 透過 InnoDB Table Monitor 驗證 (輸出至 error log)
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
-- 查看 error log 中的 TABLE SPACE INFO 區塊確認欄位長度
核心結論對照表
| 型別 | 長度單位 | 最大字元數 | 受行大小限制 | 適用場景 |
|---|
| CHAR | 字元 | 255 | 是 | 固定長度代碼、標識碼 |
| VARCHAR | 字元 | 視字元集而定 | 是 | 變長業務字串、主流選擇 |
| TEXT | 位元組 | 分級上限 | 否(僅指標) | 長文本、日誌、JSON 文檔 |
| DECIMAL | 數字位數 | 65 位數字 | 是 | 金融金額、精確計算 |
選型建議
- 首選 VARCHAR：變長、空間效率高、支援索引前綴。
- 定長代碼用 CHAR：如 ISO 國家碼、MD5 雜湊（固定 32 字元）。
- 超長文本用 TEXT：超過 VARCHAR 上限或不想佔用行大小配額。
- 金額必用 DECIMAL：
FLOAT/DOUBLE 有精度誤差，DECIMAL(19,4) 為常見金融規格。
- utf8mb4 環境：VARCHAR 最大字元數約為 latin1 的 1/4，設計表結構時需預估。
透過實測驗證型別邊界，能避免「Column length too big」、「Row size too large」、「Data too long」等上線驚喜。建議將型別選型規範納入團隊開發規範，並配合 pt-table-checksum 定期驗證主從資料一致性。
