Solved

Why would I limit my MySQL VARCHAR length?

Posted on 2007-04-04
3
687 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:IconMan7
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
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
 
LVL 28

Expert Comment

by:gamebits
Comment Utility
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
 
LVL 3

Author Comment

by:IconMan7
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now