VARCHARS: 2, 4, 8, 16, etc.? Or 1, 3, 7, 15, etc.?

Question

I see VARCHAR(255) being used all the time instead of VARCHAR(256), but I also see VARCHAR(16) being used instead of VARCHAR(15). This seems inconsistent to me. If an extra byte is being used to store the VARCHAR's length, shouldn't this rule also apply to the smaller lengths like 2, 4, 8 to be 1, 3, 7, instead?

Or am I totally missing something?

In other words, if I have a number that I know will never be above 12, should I just go ahead and use a VARCHAR(15) or VARCHAR(16) instead? Because it uses the same amount of space as a VARCHAR(12)? If so, which one do I use? 15 or 16? Does this rule change at all when I get closer to 256?

I use both MySQL and SQL, depending on the project.

Answer 1

In other words, if I have a number that I know will never be above 12, should I just go ahead and use a VARCHAR(15) or VARCHAR(16) instead?

No! Use varchar(12) (or maybe even char(12) if the length is fairly constant ).

Once upon a time the varchar type was limited to 255 characters on some systems (including MySql prior to 5.0.3) because the first byte stored indicated the length of the field. Given this restriction, devs wanting to allow a reasonable amount of text would choose 255 rather than going to a different data type altogether.

But if you know the size of your data, definitely use exactly that size for the database.

Answer 2

I think the original issue is that for some systems VARCHAR(...) was limited to 255, because when you use one byte to encode the actual length, you can only express lengths upto 255.

VARCHAR(16) / VARCHAR(15) it's most likely reminiscent of these origins, but there is nothing special in the two values.

Answer 3

It has nothing to do with odd or even numbers.

Historically, 255 characters has often been the maximum length of a VARCHAR in various DBMSes. The length limit for a field that wasn't an LOB (Large Object) back then was 255 bytes (1 byte int). So the first byte was used to store the length of the field (0-255), and the remaining n bytes for the characters. That's why you often see VARCHAR(255).

If the field will never be greater than 12, use VARCHAR(12).