Since the storage requirements for a Varchar field are based on the actual length of the string entered, what would be the downside of specifying every Varchar field as the max possible: Varchar (65535)? Well, aside from 1 extra byte for max fields > 255 characters?
[Storage Reqts for strings of length L: L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes]
Thank you!
From the documents - Table Column-Count and Row-Size Limits:
Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.
The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.
Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
So, defining a single VARCHAR(65535) column, effectively limits you to a single column on the row (assuming you have filled it up).
All this apart from the fact that such a large size is completely wrong for some types of data - if you have a phone number column which may contain local and international numbers, you may choose to use a VARCHAR field to do so, but setting it to anything over 20 may well meaningless (I am being generous).
See this answer from Bill Karwin which also indicates possible performance penalties if temporary tables get generated with unnecessarily long VARCHAR fields (to do with conversion of such fields to CHAR and back again - see the post for details).
I think varchar column lengths are not only about storage. They're about data semantics as well.
I.E. specifying a name column as varchar(100) means that names stored on your system must not be longer than 100 characters.
On the storage side of things, they should be the same. Although, row size estimations would be more accurate with a specific length on varchar columns that without them (without needing a statistics gathering system keeping data distributions on varchar sizes).
One possible reason would be to improve compatibility with other applications. For instance, if you had an app that used a "product_no" field that was 100 chars long, and you wanted to interface with an app that used a similar field like "model_no" that was 40 chars long, it would be a pain. Any product_nos in your app that were longer than 40 chars would get truncated and you'd have to figure out some way to translate them between the applications.
One reason is that the size of the field is a check on the data entered. Do you really want someone to enter a 1000 character phone number? Having a field too large is a way to guarantee that garbage will be entered into your database. You will have phone numbers that say things like (example not taken at random):
"only talk to the big blonde in the front office"
instead of a real phone number or am email field that contains notes about the client because they don't have a notes field? That doesn't work so well when you try to send an email to it.
Wide tables can create problems all their own in databases as you may run into unexpected record limits (you can design a table to be wider than can actually be stored in one record, sometimes this causes inserts to fail unexpectedly) and performance issues as the data separates across data pages. I know you can have that from wide tables in SQL Server and I would not be surprised if mysql runs into similar problems. A mysql expert will have to truly address this though. Indexing can also be a problem with wide fields. The database engine may be less inclined to think the index is useful. Again, I'm not sure if mysql would have this problem but it is something to research. I know these are issues with using the max field size for everything in SQL Server, mysql may have these issues or other ones that SQL Server does not.
VARCHAR simply truncates the string at the specified character limit if a value larger than whatever you specified is entered, it doesn't throw an error or give any indication so. Which can break some applications particularly those retrieving sets of data separated by delimiters that do something with each value ie. order IDs and ensuring each is valid, etc.
Commented
May 8 at 18:07
For example the MEMORY Engine in MySQL doesn't support VARCHAR-Fields very well. The engine will reserve for every row the maximum amount of bytes, not the really used length. So if you define a table with a single VARCHAR(1000) column, then you will have a memory usage of 1000*3 bytes for every row you add, even if they are empty strings.
