VARCHAR and max string length

[Topazan]

Is there any reason to restrict the length of a VARCHAR string below the maximum?  My understanding is that with VARCHAR the data storage requirements are determined by the actual length, not the maximum length.  Is this correct?  If so, why would you want to restrict the maximum length of the string?  If not, what's the difference between VARCHAR and CHAR?

[JGadrow]

Restricting the length of the varchar is a way to make your intent clear. For example, do you really want usernames that are 255 characters in length? 32 or even 20 sounds much more reasonable.

You are correct that the storage is dependent upon the data. The data in a varchar takes 1 byte per character plus one (sometimes 2) extra byte to store the size.

A char field takes a number of bytes equal to the defined size. If you enter a value that does not contain the proper number of characters, spaces are added to increase the length to the required size.

Essentially, char is a more efficient means of storing the maximum total of characters while varchar is more efficient at storing up to max-2 characters.

[Chris]

ORDER BY (or other sorting/grouping operations) on some storage engines might resize the row to maximum allowed size, which leads to higher memory usage (so for some operations VARCHAR might act like CHAR). I don't recall under which conditions exactly it happens, anyway bigger VARCHAR might perform worse than small one under some circumstances.

Even better, just use CHAR only for a whole table which makes all entries fixed size so finding a new row is trivial and the fastest (again, depending on storage engine and if the field is too big it might be better to use variable sized VARCHAR...)

[Nox]

VARCHAR x CHAR is rather internal difference, type with not(less?)restricted length is TEXT/LONGTEXT so use that if you'd like long string to be possible

[JGadrow]

VARCHAR x CHAR is rather internal difference, type with not(less?)restricted length is TEXT/LONGTEXT so use that if you'd like long string to be possible

The only issue with these types is indexing is impractical. You shouldn't commonly be indexing non-numeric fields anyways... but in some scenarios it makes sense (state names in the US, declaring a unique email address field, etc.).

[Chris]

VARCHAR x CHAR is rather internal difference, type with not(less?)restricted length is TEXT/LONGTEXT so use that if you'd like long string to be possible

TEXT/BLOB is not like VARCHAR/CHAR, the first is stored as a pointer (in some other internally created table I suppose?), the second is included in the table body. If you need only 255 chars use VARCHAR, not TEXT. Again, might be not true for some storage engines.

You shouldn't commonly be indexing non-numeric fields anyways

I agree, also only first X letters will be used (MySQL limit) for index so it might be much less effective than you expect. For index to work efficiently the size is crucial, long integer uses 4 bytes (and this is enough for 4 billion entires), while string use 1 byte per letter (so for equal efficiency you would have index no more than first 4 letters and with 4 letter only you will get much less than 4 billion combinations not to mention that there will be several repetitions like "The[space]" in names of books which might make the index half useless).

[Topazan]

Ok, I think I understand a little better now, thanks.