I know the differnce between CHAR and VARCHAR,
CHAR - Fixed length
VARCHAR - Variable length (size + 1 byte)
But I wanted to know what was the purpse of the having the option for a varchar length e.g. VARCHAR(50), VARCHAR(100), VARCHAR(255)
This seems pointless to me because the actual space used depends on the value stored in the database.
So my questions are:
1) It is fine to set all my varchar's to 255 2) Why would you want to specify any other lenght?
Excerpt from the MySQL documentation:
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. As of MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained.
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
1) If you dont want to limit the maximum size of a stored varchar, then yes it is fine. That being said...
2) In many cases you want to set an upper limit for the size of a varchar. Lets say you are storing a mailing list, and have a limited amount of space for an address line. By setting an upper limit for your address field, you now allow the database to enforce a maximum address line length for you.
1) Technically it is fine, because the fields are created with only 1 or 2 bytes in length in the beginning. Afterwards, they'll grow as necessary.
2) Having said that though, good design principles suggest that you set field lengths appropriately so a) If someone goes through the table scheme and tries to work out how much data is stored in particular fields, they can see that certain fields will hold less data than others and b) you can prevent small amounts of extra work done by the database engine because it has to truncate less space from a VARCHAR(10) field than a VARCHAR(255) during an insert.
You can view extra details about it here:
I have read elsewhere that varchar comes with a performance hit relative to char, when you run selects against columns defined with them. So, maybe you want to choose char, if you know for sure the field will always be a certain length, and you have performance issues...
1) Yes.
2) Historically it was a performance hit.
Look at databases such as sqlite that store everything as text for evidence that it no longer really matters.