Is a VARCHAR(20000) valid in MySQL?

Zoe picture Zoe · Aug 20, 2009 · Viewed 77.6k times · Source

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

Sampson picture Sampson · Aug 20, 2009

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