Why would I limit my MySQL VARCHAR length?

Hi

According to the MySQL documentation, the amount of bytes taken up by a standard VARCHAR entry is equal to the length of the data + 1.
For example, when I store the string 'abc', it will take up 4 bytes of space.

That's why I think it is strange you can specify the allowed length of a VARCHAR field.
Is there any reason whatsoever I wouldn't want to specify a column as VARCHAR (255) even though each individual cell of data stored in that column will never exceed 10 characters (for instance)?

In other words, if I want - for example - to keep a list of ISO country codes (which are each exactly 2 characters long, such as 'US' for United States), is there any reason why I would specify this column as a VARCHAR (2) and not a VARCHAR (255)?

Thanks!
LVL 3
IconMan7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you last example is actually chosen badly, as there you would really better use CHAR(2).
the main argument there is that such a choice will prevent anyone putting anything else in either way that is longer.
also, in such a case, the index operations will work slightly faster as the data is fixed-width.

now, if you had chosen something like firstname/lastname, where rarely it is longer than 50 characeters, you would indeed be tempted to choose a longer value.
while in MySQL this is not a problem, in other databases, having a row defined too large could be a problem: example MSSQL can only have 8KB for a single row max (not the definition, only the actual data).

it's historical to choose some max size for columns, as in the past, disk space was much more expensive. meanwhile, that is no longer true, and hence indeed better choose longer data types, but still stay reasonable.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gamebitsCommented:
It could help prevent injection, if you ask me to enter 2 characters in a field but you give me the room for 255 as a normal user I would go with the 2 characters you expect, but if I'm a hacker I might try to go with a short snippet of code to be executed instead of what I'm suppose to enter, if you set the varchar at 2 whatever I enter after the 2 first character would be truncated.
0
IconMan7Author Commented:
Thanks for this fast answer!

Indeed, the country code was not a good example, but I just wanted to illustrate the question.  I can stll make sure users do not enter data that exceeds the column size through server-side scripting prior to insertion into the database.  Likewise, injection can also be prevented.

Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.