Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why would I limit my MySQL VARCHAR length?

Posted on 2007-04-04
3
Medium Priority
?
707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 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