Max VARCHAR Size

Nov 8, 2008 / By Sheeri Cabral

Tags:

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.

(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)

5 Responses to “Max VARCHAR Size”

  • Mark Atwood says:

    The additional overhead you discovered is the null mask. There will be one byte of null mask for every 8 potentially nullable columns. Your test table has 8 or less nullable columns, so you got one byte. If you have 24 columns that can have nulls, you would have 3 bytes. And so forth.

  • Arjen Lentz says:

    …and then there’s UTF8…

    TEXT/BLOB types of course cause disk I/O when tmp tables are needed during query execution, so using longer VARCHARs can generally be a good thing.

  • […] sobre el tamaño del VARCHAR, en pythian group (en […]

  • MySQL VarChar 255 and above | Jisku.com - Developers Network says:

    […] MySQL VarChar 255 and above I am currently doing screen scraping and storing in to a MySQL database, I used some of my columns as answer_content varchar(255) NOT NULL,but the problem is some of the data is getting truncated, I was reading following blog post. […]

  • […] I am currently doing screen scraping and storing in to a MySQL database, I used some of my columns as answer_content varchar(255) NOT NULL,but the problem is some of the data is getting truncated, I was reading following blog post. […]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>