Max VARCHAR Size

The maximum length of a VARCHAR is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.

The VARCHAR data type has some overhead. If the length of VARCHAR is less than 255, one byte per row is used to store the actual length of the string. If the length of VARCHAR is greater than 255, the overhead cost of storing the string length is two bytes per row. Thus, the maximum length of a VARCHAR should be 65,533 bytes.

However, that is not the case:

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(65533) CHARSET latin1);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(65532) CHARSET latin1);
Query OK, 0 rows affected (0.16 sec)

Why is that? I believe that is because there is also a 1-byte overhead per row, though I have not confirmed that. There is some allusion to that on the Forge Internals page……but I have not confirmed that it is the case.

(one of the reasons I believe this is that there’s a different error message when the character set is multi-byte:

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(16384) CHARSET utf8);
ERROR 1074 (42000): Column length too big for column 'fld' (max = 16383); use BLOB or TEXT instead

This actually gives the maximum length you can use. Note that the maximum usable length still seems to be missing 1 byte from the theoretical max of 65,535:

mysql> SELECT 16383*4+2;
+-----------+
| 16383*4+2 |
+-----------+
|     65534 |
+-----------+
1 row in set (0.00 sec)

)

(and yes, you can use TEXT and BLOB fields if you actually want a variable-length string with more than 65,532 bytes, that’s not really the point of this article)

The maximum length of a VARCHAR is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.

The VARCHAR data type has some overhead. If the length of VARCHAR is less than 255, one byte per row is used to store the actual length of the string. If the length of VARCHAR is greater than 255, the overhead cost of storing the string length is two bytes per row. Thus, the maximum length of a VARCHAR should be 65,533 bytes.

However, that is not the case:

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(65533) CHARSET latin1);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(65532) CHARSET latin1);
Query OK, 0 rows affected (0.16 sec)

Why is that? I believe that is because there is also a 1-byte overhead per row, though I have not confirmed that. There is some allusion to that on the Forge Internals page……but I have not confirmed that it is the case.

(one of the reasons I believe this is that there’s a different error message when the character set is multi-byte:

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(16384) CHARSET utf8);
ERROR 1074 (42000): Column length too big for column 'fld' (max = 16383); use BLOB or TEXT instead

This actually gives the maximum length you can use. Note that the maximum usable length still seems to be missing 1 byte from the theoretical max of 65,535:

mysql> SELECT 16383*4+2;
+-----------+
| 16383*4+2 |
+-----------+
|     65534 |
+-----------+
1 row in set (0.00 sec)

)

(and yes, you can use TEXT and BLOB fields if you actually want a variable-length string with more than 65,532 bytes, that’s not really the point of this article)