0

If I set a column as varchar(50) say, then update it with a string of length 20 say. Then OPTIMISE it. What will happen if I then UPDATE it with a string of more than 20, say 30?

(because the optimise is supposed to get rid of excess baggage on the varchars...)

Second question: What happens if I have a varchar(50) and UPDATE with a string of 51 characters?

Third question: The manual is unclear here, but what is the true maximum optimal string size for varchar? is it 256 or 254 (because of the extra 2 bytes it needs) or is it something else?

"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."

Thanks.

David19801
  • 11,214
  • 25
  • 84
  • 127
  • "What happens if I have a varchar(50) and UPDATE with a string of 51 characters?" : try it, it should truncate the extra characters. – Mathias E. Dec 27 '10 at 09:58
  • about 256 or 254 (256 is impossible) http://stackoverflow.com/questions/262238/are-there-disadvantages-to-using-a-generic-varchar255-for-all-text-based-fields – Mathias E. Dec 27 '10 at 10:00

1 Answers1

0

  1. optimize does not get rid of excess baggage, it just act like window-defragmented to clean disk-space for bulky delete - details. If you update with a string with 30 length, mysql just store as what is it, nothing more and nothing less

  2. if you update a string with 51, the last character will be chopped/truncated

  3. that is no optimal string size for varchar, is much depended on your application usage, and how you work with mysql index. The maximum varchar(255) still stay at string at length of 255

ajreal
  • 46,720
  • 11
  • 89
  • 119