Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

People also ask

Why we use VARCHAR 255 in SQL?

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.

Should I always use VARCHAR 255?

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.

Does length of VARCHAR matter?

Yes, is matter when you indexing multiple columns. Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters.

What does 255 mean in SQL?

255 is used because it's the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255.


255 is used because it's the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255.

When used this way, VarChar only uses the number of bytes + 1 to store your text, so you might as well set it to 255, unless you want a hard limit (like 50) on the number of characters in the field.


Historically, 255 characters has often been the maximum length of a VARCHAR in some DBMSes, and it sometimes still winds up being the effective maximum if you want to use UTF-8 and have the column indexed (because of index length limitations).


Probably because both SQL Server and Sybase (to name two I am familiar with) used to have a 255 character maximum in the number of characters in a VARCHAR column. For SQL Server, this changed in version 7 in 1996/1997 or so... but old habits sometimes die hard.


I'm going to answer the literal question: no, there isn't a good reason you see VARCHAR(255) used so often (there are indeed reasons, as discussed in the other answers, just not good ones). You won't find many examples of projects that have failed catastrophically because the architect chose VARCHAR(300) instead of VARCHAR(255). This would be an issue of near-total insignificance even if you were talking about CHAR instead of VARCHAR.