Confusion about varchar datatype.

Question

Hi there,

My server has my SQL version of 5.0.91-community, now i have to store a long string of approx about 500 character more or less, i thought of going with text data type but then someone told me it slows the performance, i wanted to know more about varchar and it's limit.

i used to think that varchar is only limited to 255 characters, but then i read it somewhere it is capable of storing more then that in the newer version i.e >= 5.0.3 , as i am using 5.0.91 what do you think i should use? if i use it like varchar(1000) is it still valid?

thank you.

Answer 1

From The CHAR and VARCHAR Types

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Answer 2

The documentation is here,

• varchar has a max size of 65,535 in MySQL 5.0.3 and later , before 5.0.3 the limit was 255

Note that the effective size is less,

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.

You have to specify the max size, e.g. varchar(1000). Just stating varchar isn't enough.

Answer 3

In MySQL, VARCHAR accepts maximum of 65535 chars.

Answer 4

According to the MySQL doc:

TEXT differs from VARCHAR in the following ways:

• There is no trailing-space removal for TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARCHAR, for which trailing spaces are removed when values are stored.
• For indexes on TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional.
• TEXT columns cannot have DEFAULT values.

Apart from these differences, using VARCHAR like using TEXT, so the question of size is not what should make you choose between those two, unless you really need to store no more characters than 1000.

Answer 5

You can assure yourself very easy. Mysql documentation is openly accessed and it says

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

as for the performance issues, it doesn't matter. Not data type but data relations affect performance.