Solved

Why would I limit my MySQL VARCHAR length?

Posted on 2007-04-04
3
693 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 18849614
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
ID: 18849615
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
ID: 18849683
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 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