0

I read that "If you put a greater value than 255 it will be converted to a TEXT type" so what's the point in setting a max size?

  • 1
    Not always you need VarChar to go 255. For example, the varchar for password is 6 digits than i will set it to 6 so if the user tries to parse into my db, it will throw error. – Smit Feb 27 '17 at 17:32
  • So it's only when set to 255, it would be converted to TEXT if the size passes that limit; and otherwise throw an error? –  Feb 27 '17 at 17:34
  • 2
    This kind of misinformation is why we recommend *avoiding* w3fools. (It's not just the misinformation that is the problem. The real problem is that w3fools obstinately refuses to *correct* this kind of misstatement.) It is is patently *false* that a VARCHAR(n) is converted to TEXT when n>255. (That statement is FALSE for MySQL 5.1, 5.6, and 5.7. If it ever was true(?) that would have been for a really old version of MySQL.) There are some significant differences between VARCHAR and TEXT. And some good reasons you would use one over the other. Exceeding 255 characters is *not* a good reason. – spencer7593 Feb 27 '17 at 17:47

3 Answers3

1

One reason you specify the maximum length, even though a VARCHAR 'could' be up to 255 (or more) chars, is for indexes. You want to tell the database how many chars are really being used so that the index can be efficient and fits what the storage engine is capable of. There are limits on how much data can be in an index (see MyISAM and InnoDB)

If you're asking, "why not just use TEXT" everywhere? Well, it's not as storage efficient, can't be used in an index, and it can have different retrieval characteristics too.

See also Difference between VARCHAR and TEXT in mysql

Community
  • 1
  • 1
Paul Dixon
  • 295,876
  • 54
  • 310
  • 348
1

That information is just 10 years obsolete. I cannot link the manual because Oracle has removed older versions from their site but the quote is:

Prior to MySQL 5.0.3, a VARCHAR column with a length specification greater than 255 was converted to the smallest TEXT type that could hold values of the given length. For example, VARCHAR(500) was converted to TEXT, and VARCHAR(200000) was converted to MEDIUMTEXT. This was a compatibility feature. However, this conversion affected trailing-space removal.

Apparently, VARCHAR did not accept sizes greater than 255 until MySQL/3.23. They figured out this would be a great way to keep backwards compatibility and later they reverted the decision.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
0

Not always you need VarChar to go 255. For example, the varchar for password is 6 digits than i will set it to 6 so if the user tries to parse into my db, it will throw error. You may set it to 255 when you are expecting a message or anything to which you have not limits!

As @Paul mention about the memory as well! The memory and the retrieval characteristics will be different too.

And Yes, it the text exceeds 255, it will throw error!

Smit
  • 2,078
  • 2
  • 17
  • 40