6

I'm designing a MySQL table that will contain a row which contains a subject title of variable length, but probably between 40-100 characters. VARCHAR is the obvious choice, but I'm not clear on how to balance efficiency with ensuring that I capture all data. I can declare it as VARCHAR(256) and be very safe, or I can declare it as VARCHAR(512) and be crazy safe. From what I understand, VARCHAR only stores what you put in it, so why don't we just use VARCHAR(max) all the time? Is it just to protect against running over the 65,535 byte row size limit, which includes all columns in the row? What's the best design practice when you have a general idea about the upper limit of a string's length like this?

Some people don't understand the question. I'm asking what the reason is to specify a limit on VARCHAR that isn't max. I know what a VARCHAR is, what max is, etc.

GoldenJoe
  • 7,874
  • 7
  • 53
  • 92
  • Uh...how does that answer my question? I know what a VARCHAR is and how it works. I'm asking WHY. – GoldenJoe Jul 18 '16 at 03:22
  • So you're one of those guys who thinks that users with 2K karma don't know how to use google. Turns out we can, and we do RTFM. Links are not acceptable answers anyway. – GoldenJoe Jul 18 '16 at 04:17
  • You're pretty lost overall. It's best to let someone else answer. Have a nice evening. – GoldenJoe Jul 18 '16 at 04:21
  • 1
    I think the question @GoldenJoe is trying to ask is "*What is the downside to declaring all VARCHAR fields with width 65536?*", which is on-topic. – Ryan Bemrose Jul 18 '16 at 04:36
  • 1
    I agree http://stackoverflow.com/q/8295131 is a much better dupe candidate. – Ryan Bemrose Jul 18 '16 at 04:57
  • @RyanBemrose Thanks, that's a pretty helpful link. At least it explains (max) vs (x). I gather that it's because of how it treats max like TEXT (which is in the documentation), but I'm still not clear on why people do stuff like VARCHAR(40) or VARCHAR(64). The answer says there's no difference, but...why? Do people just not know that it makes no difference? – GoldenJoe Jul 18 '16 at 07:06
  • The difference is that if you try to store a 50-character string, a `VARCHAR(64)` will store the whole string. A `VARCHAR(40)` will only store the first 40 characters and discard the rest. Whether or not that's useful depends on the problem you're trying to solve. The answers to the two dupe candidates Drew provided explain this pretty well. – Ryan Bemrose Jul 18 '16 at 07:26
  • 1
    Yeah I know it will truncate the string. I read the docs. That just...doesn't seem useful? Is there some hidden benefit, like some way MySQL allocates memory being more efficient with a smaller cap? The only reason I could find in the doc is that row size has a hard limit, but capping your column doesn't necessarily protect against that, at least not without data loss. – GoldenJoe Jul 18 '16 at 07:52

0 Answers0