Why should I ever choose any other length than 255 for varchar in MySQL?

mysql char varchar
Lizard picture Lizard · Jul 1, 2010 · Viewed 38.7k times · Source

I know the differnce between CHAR and VARCHAR,

CHAR - Fixed length

VARCHAR - Variable length (size + 1 byte)

But I wanted to know what was the purpse of the having the option for a varchar length e.g. VARCHAR(50), VARCHAR(100), VARCHAR(255)

This seems pointless to me because the actual space used depends on the value stored in the database.

So my questions are:

1) It is fine to set all my varchar's to 255 2) Why would you want to specify any other lenght?

Answer

MarkD picture MarkD · Jul 1, 2010

1) If you dont want to limit the maximum size of a stored varchar, then yes it is fine. That being said...

2) In many cases you want to set an upper limit for the size of a varchar. Lets say you are storing a mailing list, and have a limited amount of space for an address line. By setting an upper limit for your address field, you now allow the database to enforce a maximum address line length for you.

Related questions

MySQL - TEXT vs CHAR and VARCHAR

Reading this question, a doubt popped into my head: char and varchar can store up to 255 chars text can store up to 65k chars char size in bytes is number of chars varchar size in bytes is number of chars …

Read More
How does MySQL varchar know how many bytes indicate the length?

The MySQL manual and several StackOverflow answers make it clear that varchar uses: 1 byte for varchars with 0-255 characters in them 2 bytes for varchars with more than 255 characters in them. The first part makes sense. A single byte can store 256 …

Read More
Which is faster: char(1) or tinyint(1) ? Why?

MY PLATFORM: PHP & mySQL MY SITUATION: I came across a situation where I need to store a value for user selection in one of my columns of a table. Now my options would be to: Either declare the Column …

Read More