237

In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for "shortish" text. Is there any good reason that a length of 255 is chosen so often, other than being a nice round number? Is it a holdout from some time in the past when there was a good reason (whether or not it applies today)?

I realize, of course, that a tighter limit would be more ideal, if you somehow know the maximum length of the string. But if you are using VARCHAR(255) that probably indicates that you don't know the max length, only that it is a "shortish" string.

Note: I found this question (varchar(255) v tinyblob v tinytext), which says that VARCHAR(n) requires n+1 bytes of storage for n<=255, n+2 bytes of storage for n>255. Is this the only reason? That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

Improve this question
1
  • This 255 limit on the length of VARCHAR columns is years obsolete. It's 64KiB now.
    – O. Jones
    Commented Oct 27, 2022 at 17:04

10 Answers 10

Reset to default
228

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.

Improve this answer
7
  • 9
    Does this hold true for DBs where varchars are UTF-8?
    – antak
    Commented Jul 17, 2015 at 2:27
  • 2
    @antak: In MySQL, using InnoDB, any key column cannot be larger than 767 bytes. If a VARCHAR column is UTF8 (meaning each char might take up to 3 bytes), the maximum allowed length of the column is floor(767/3) = 255. I'm assuming "767" was chosen for exactly that reason.
    – BlueRaja - Danny Pflughoeft
    Commented Oct 8, 2016 at 20:12
  • 4
    If the charset is utf8, varchar(85) is the limit over which crossing tips the length byte from one to two bytes. If it's utf8mb4, it's varchar(63). These are significant because they're the maximum to which a VARCHAR's length can be extended through the use of online ALTER TABLE. Consequently, I derived those numbers by creating a table with a varchar(2) charset utf8 column and seeing how far I was able to extend it given ALGORITHM=INPLACE.
    – antak
    Commented Apr 11, 2017 at 7:57
  • 1
    It makes even more sense when you consider that many "databases" Back In The Day were stored on magnetic tape. It was very common to read data in "blocks" that were sized in multiples of two. This way, data was stored most efficiently (and when you were running on an old mainframe, small efficiencies like that were make-it-or-break-it optimizations).
    – TMN
    Commented Jan 14, 2019 at 18:52
  • does it count the characters or bytes?
    – Stuepfnick
    Commented Feb 7 at 9:43
132

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).

Improve this answer
17
  • 4
    @CharlesBretana: if you read the rest of the sentence you quoted, you will find the exact explanation you are requesting.
    – chaos
    Commented Feb 17, 2016 at 16:07
  • 3
    @CharlesBretana: By "fake UTF-8" I mean MySQL's "utf8" encoding, which as I mentioned reserves (and is limited to) 3 bytes per character. This isn't a very good version of UTF-8; if you want decent UTF-8 in MySQL, you have to use its "utf8mb4" encoding. But people are much more likely to not know that and go with "utf8", and much more likely to want UTF-8 than any other encoding, so, presto, they wind up with a maximum indexable length of 255 characters in a VARCHAR. Your astoundment notwithstanding.
    – chaos
    Commented Feb 22, 2016 at 19:02
  • 4
    @CharlesBretana: I have now explained it three times and not a single thing has changed. MySQL's index length limit is still 767 bytes, the number of bytes needed to encode a 3-byte UTF-8 character is still 3, and floor(767 / 3) is still 255. Your determination to find something to be confused about beggars belief.
    – chaos
    Commented Feb 23, 2016 at 21:34
  • 1
    @CharlesBretana (Sorry for being late to this whole party) I'm no DB specialist, but I think what chaos is saying is : yes a 'Fake UTF-8' column can be of more than 255 characters long, but the index will only work on the first 255 characters of the varchar, making it effectively the maximum of a column if you want it fully indexed. Now that's only what I understood of his explainations, I may be wrong, I'm not an expert in SQL indexes at all.
    – Noémie Lord
    Commented Apr 6, 2017 at 15:43
  • 2
    @CharlesBretana If you look properly at Chaos' answer, you'll notice it it seperated into 2 parts : 1. The historical reason behind Varchar(255) being so common (it used to be the maximum on some older DBMS), 2. Even today, it is still an limitation for some because of the index limitations discussed previously, Part 1 and 2 are not linked. Part 1 is the actual answer to the question, part 2 is a side note which is still relevant to the question because it explains why even today it may still be a limitation. (CONTINUED ->)
    – Noémie Lord
    Commented Apr 6, 2017 at 18:03
26

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.

Improve this answer
0
22

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.

Improve this answer
2
  • 1
    1 byte out of 255 is 0.4%. Sometimes you care about the last half a percent or so. Sometimes you don't. If you hosting and perf costs run into the tens of dollars, you probably don't care. If they run into the millions, they probably do.
    – Edward Brey
    Commented Oct 14, 2017 at 14:58
  • It's more about standards in the same vein as linting than it is about preventing catastrophic failures. If you also don't think there is no good reasons for linting, then this view would be consistent with that.
    – Jon
    Commented May 11, 2023 at 3:21
15

When you say 2^8 you get 256, but the numbers in computers terms begins from the number 0. So, then you got the 255, you can probe it in a internet mask for the IP or in the IP itself.

255 is the maximum value of a 8 bit integer : 11111111 = 255

Does that help?

Improve this answer
2
  • 1
    With integers, you count starting from 0 and you end at 255. But with places in a string, you count starting from the 1st place, so doesn't it make sense to end at the 256th place, because you started at 1 instead of 0? I'm not agreeing with varchar(256) entirely just yet, because of string_length() results, but I really am not certain.
    – HoldOffHunger
    Commented Mar 16, 2016 at 23:18
  • 1
    @HoldOffHunger strings in a database can have a length of zero characters, so the permissible range of lengths when the length is stored in eight bits is between 0 and 255. If you wanted to say that strings all must have at least one character then you could support 256-character strings with an eight-bit length.
    – phoog
    Commented Jan 14, 2019 at 19:44
9

Note: I found this question (varchar(255) v tinyblob v tinytext), which says that VARCHAR(n) requires n+1 bytes of storage for n<=255, n+2 bytes of storage for n>255. Is this the only reason? That seems kind of arbitrary, since you would only be saving two bytes compared to VARCHAR(256), and you could just as easily save another two bytes by declaring it VARCHAR(253).

No. you don't save two bytes by declaring 253. The implementation of the varchar is most likely a length counter and a variable length, nonterminated array. This means that if you store "hello" in a varchar(255) you will occupy 6 bytes: one byte for the length (the number 5) and 5 bytes for the five letters.

Improve this answer
3
  • 3
    This statement is not true of all databases. many databases use varchar fields of the given size in the tables so that they don't have to move rows around when that field is changed for a row.
    – SingleNegationElimination
    Commented Aug 1, 2009 at 22:11
  • yes you are right. it's implementation dependent. You have to check the vendor manual to see what is the case
    – Stefano Borini
    Commented Dec 10, 2009 at 17:48
  • 6
    It may be permissible, but implementing VARCHAR that way defeats the whole point of using VARCHAR instead of CHAR.
    – dan04
    Commented Sep 7, 2010 at 3:00
5

0000 0000 -> this is an 8-bit binary number. A digit represents a bit.

You count like so:

0000 0000 → (0)

0000 0001 → (1)

0000 0010 → (2)

0000 0011 → (3)

Each bit can be one of two values: on or off. The total highest number can be represented by multiplication: 

2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 - 1 = 255

Or 

2^8 - 1.

We subtract one because the first number is 0.

255 can hold quite a bit (no pun intended) of values.

As we use more bits the max value goes up exponentially. Therefore for many purposes, adding more bits is overkill.

Improve this answer
4

An unsigned 1 byte number can contain the range [0-255] inclusive. So when you see 255, it is mostly because programmers think in base 10 (get the joke?) :)

Actually, for a while, 255 was the largest size you could give a VARCHAR in MySQL, and there are advantages to using VARCHAR over TEXT with indexing and other issues.

Improve this answer
4

In many applications, like MsOffice (until version 2000 or 2002), the maximum number of characters per cell was 255. Moving data from programs able of handling more than 255 characters per field to/from those applications was a nightmare. Currently, the limit is less and less hindering.

Improve this answer
1

Another reason may be that in very old data access libraries on Windows such as RDO and ADO (COM version not ADO.NET) you had to call a special method, GetChunk, to get data from a column with more than 255 chars. If you limited a varchar column to 255, this extra code wasn't necessary.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.