What is the MySQL VARCHAR max size?
I would like to know what the max size is for a MySQL VARCHAR type.
I read that the max size is limited by the row size which is about 65k. I tried setting the field to varchar(20000) but it says that that's too large.
I could set it to varchar(10000). What is the exact max I can set it to?
Answer
Keep in mind that MySQL has a maximum row size limit
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, not counting BLOB and TEXT types. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row. Read more about Limits on Table Column Count and Row Size.
Maximum size a single column can occupy, is different before and after MySQL 5.0.3
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
However, note that the limit is lower if you use a multi-byte character set like utf8 or utf8mb4.
Use TEXT types inorder to overcome row size limit.
The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.
More details on BLOB and TEXT Types
- Ref for MySQLv8.0 https://dev.mysql.com/doc/refman/8.0/en/blob.html
- Ref for MySQLv5.7 http://dev.mysql.com/doc/refman/5.7/en/blob.html
- Ref for MySQLv5.6 http://dev.mysql.com/doc/refman/5.6/en/blob.html
- Ref for MySQLv5.5 http://dev.mysql.com/doc/refman/5.5/en/blob.html
- Ref for MySQLv5.1 http://dev.mysql.com/doc/refman/5.1/en/blob.html
- Ref for MySQLv5.0 http://dev.mysql.com/doc/refman/5.0/en/blob.html
Even more
Checkout more details on Data Type Storage Requirements which deals with storage requirements for all data types.