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

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 2

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

Answer 3

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 4

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.

Answer 5

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