MySQL VARCHAR的最大長度為65535 Bytes，而能儲存多少個字必須依字元集(Character Set)決定。
首先要知道VARCHAR(N)的N是指字數，而不是Bytes。
先從一個字只佔一個byte的latin1字元集來看。
SHOW CHARACTER SET WHERE charset = 'latin1';
mysql> SHOW CHARACTER SET WHERE charset = 'latin1';
+---------+----------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+----------------------+-------------------+--------+
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
+---------+----------------------+-------------------+--------+
若VARCHAR長度剛好是65535個字會報錯，因為VARCHAR需要多1到2個Bytes來儲存值的長度。
CREATE TABLE t1 ( c1 VARCHAR(65535) ) CHARSET=latin1;
mysql> CREATE TABLE t1 ( c1 VARCHAR(65535) ) CHARSET=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting B
LOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to
TEXT or BLOBs
而欄位允許為null的情況下會多佔1個byte，所以VARCHAR實際上最多能宣告
65535 - 2 - 1 = 65532。
CREATE TABLE t1 ( c1 VARCHAR(65532) ) CHARSET=latin1;
mysql> CREATE TABLE t1 ( c1 VARCHAR(65532) ) CHARSET=latin1;
Query OK, 0 rows affected (0.35 sec)
以上是字元集為latin1的情況，那若是用常用來儲存中文的utf8mb4字元集，其一個字需要4 Bytes的空間。
SHOW CHARACTER SET WHERE charset = 'utf8mb4';
mysql> SHOW CHARACTER SET WHERE charset = 'utf8mb4';
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+---------+---------------+--------------------+--------+
則同樣地用VARCHAR(65532)就會報錯。
CREATE TABLE t1 ( c1 VARCHAR(65532) ) CHARSET=utf8mb4;
mysql> CREATE TABLE t1 ( c1 VARCHAR(65532) ) CHARSET=utf8mb4;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 16383); use BLOB or TEXT instead
長度必須除以4，也就是 65532 / 4 = 16383。
宣告為VARCHAR(16383)則正確。
CREATE TABLE t1 ( c1 VARCHAR(16383) ) CHARSET=utf8mb4;
mysql> CREATE TABLE t1 ( c1 VARCHAR(16383) ) CHARSET=utf8mb4;
Query OK, 0 rows affected (0.25 sec)
反推驗證一下
16383 x 4 + 2 + 1 = 65535
以上是資料表中只有一個VARCHAR欄位的情況。
若資料表中宣告兩個VARCHAR(16383)欄位如下則會報錯。
CREATE TABLE t1 ( c1 VARCHAR(16383), c2 VARCHAR(16383) ) CHARSET=utf8mb4;
mysql> CREATE TABLE t1 ( c1 VARCHAR(16383), c2 VARCHAR(16383) ) CHARSET=utf8mb4;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting B
LOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to
TEXT or BLOBs
因為MySQL資料表的一個列(row)中的所有欄位的大小加總不能超過row size的限制，MySQL預設row size的大小為65535KB，而
((16383 + 16383) x 4) + (2 x 2) + 1 = 131069
已經超過了限制。
若改為一個為 VARCHAR(10000)，另一個為VARCHAR(6383)如下也是會發生錯誤。
CREATE TABLE t1 ( c1 VARCHAR(10000), c2 VARCHAR(6383) ) CHARSET=utf8mb4;
mysql> CREATE TABLE t1 ( c1 VARCHAR(10000), c2 VARCHAR(6383) ) CHARSET=utf8mb4;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting B
LOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to
TEXT or BLOBs
原因同之前所提到的VARCHAR需要多1到2個Bytes來儲存值的長度，及多1 byte儲存是否允許null，
((10000 + 6383) x 4) + (2 x 2) + (1 x 2) = 65538
超過了65535。
改成如下就可正常建立。
CREATE TABLE t1 ( c1 VARCHAR(10000), c2 VARCHAR(6382) ) CHARSET=utf8mb4;
mysql> CREATE TABLE t1 ( c1 VARCHAR(10000), c2 VARCHAR(6382) ) CHARSET=utf8mb4;
Query OK, 0 rows affected (0.38 sec)
因為
((10000 + 6382) * 4) + (2 x 2) + (1 x 2) = 65534
小於65535。
因此字元集(Character Set)若設為utf8mb4來儲存中文字，也就是每個UTF-8字元需要佔用4 bytes的空間，則單一個VARCHAR欄位最多可儲存16,383個字。
如果一張資料表有多個欄位，那VARCHAR最多可儲存的大小就要看其他欄位的大小來決定，全部欄位大小加總起來必須少於65535 Bytes。
參考：
沒有留言:
張貼留言