IconMan7
asked on
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!
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!
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
gamebits
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.
ASKER
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!
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!