Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Why use VARCHAR(20) instead of VARCHAR(255)? [duplicate]

Possible Duplicate:
Are there disadvantages to using a generic varchar(255) for all text-based fields?

In MYSQL you can choose a length for the VARCHAR field type. Possible values are 1-255.

But what are its advantages if you use VARCHAR(255) that is the maximum instead of VARCHAR(20)? As far as I know, the size of the entries depends only on the real length of the inserted string.

size (bytes) = length+1

So if you have the word "Example" in a VARCHAR(255) field, it would have 8 bytes. If you have it in a VARCHAR(20) field, it would have 8 bytes, too. What is the difference?

I hope you can help me. Thanks in advance!

like image 350
caw Avatar asked Aug 11 '09 18:08

caw


People also ask

When should I use VARCHAR 255?

1 byte for the length, and the actual storage for the rest. 255 is the max limit, not an allocation. 255 is not the max limit. There is a variable MAX you can use which Varchar(max) stores a maximum of 2 147 483 647 characters.

Why is VARCHAR length 255?

Storage Information : 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. Make sure you are aware of the effects of a multi-byte character set. VARCHAR(255) stores 255 characters, which may be more than 255 bytes.

Should I specify length of VARCHAR?

Always specify a length to any text-based datatype such as NVARCHAR or VARCHAR .

What does VARCHAR 10 mean?

So in NVARCHAR(10) , you get up to 10 code units (i.e. 20 bytes). Whereas in UTF-8 the code units are 1-byte, so VARCHAR(10) means you get up to 10 code units (i.e. 10 bytes).


1 Answers

Check out: Reference for Varchar

In short there isn't much difference unless you go over the size of 255 in your VARCHAR which will require another byte for the length prefix.

The length indicates more of a constraint on the data stored in the column than anything else. This inherently constrains the MAXIMUM storage size for the column as well. IMHO, the length should make sense with respect to the data. If your storing a Social Security # it makes no sense to set the length to 128 even though it doesn't cost you anything in storage if all you actually store is an SSN.

like image 145
RC. Avatar answered Oct 14 '22 01:10

RC.