網頁

2019/6/15

MySQL VARCHAR最大長度限制(maximum length)

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。


參考:

沒有留言:

張貼留言