Why would I limit my MySQL VARCHAR length?

Posted on 2007-04-04
Last Modified: 2008-01-09

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

Question by:IconMan7
LVL 142

Accepted Solution

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.

LVL 28

Expert Comment

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.

Author Comment

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!

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CheckListBox usage 3 59
Amazon Redshift 2 35
update joined tables 2 46
Creating Functions in phpMyAdmin 8 17
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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…

777 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