Is a VARCHAR(20000) valid in MySQL?

Question

I’m in need of some clarification of the maximum length of a varchar field in MySQL.

I’ve always thought the max length was 255 (255 what? Characters I’ve assumed, but this might be a source of my confusion). Taking a look at the tables of a database set up by an external company we’re working with, I see a field set-up as varchar(20000), holding chunks of xml longer than 255 characters. Why does this work? Is 20000 a valid value?

A bit of googling has revealed that in mysql varchar has a limit of 65,535 bytes, and I see varchar(65535) in use, so how does the 255 limit relate to this?

Answer 1

Note the MySQL Versions.

http://dev.mysql.com/doc/refman/5.0/en/char.html

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.

What version of MySQL do I have?

Run the following query...

select version() as myVersion

Answer 2

It is valid since version 5.0.3, when they changed the maximum length from 255 to 65535.

CHAR has always been 255 max.

http://dev.mysql.com/doc/refman/5.0/en/char.html

Answer 3

If you are expecting too big inputs, its a better idea to use TEXT, MEDIUMTEXT or LONGTEXT instead.

Answer 4

Though the index can not be built on varchar(2000)

Answer 5

This question already has an answer at What is the MySQL VARCHAR max size?

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.

Answer 6

The MySQL manual states

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.

So it depends on the version you're using.