23

Disclaimer: I'm very new to SQL and databases in general.

I need to create a field that will store a maximum of 32 characters of text data. Does "VARCHAR(32)" mean that I have exactly 32 characters for my data? Do I need to reserve an extra character for null-termination?

I conducted a simple test and it seems that this is a WYSIWYG buffer. However, I wanted to get a concrete answer from people who actually know what they're doing.

I have a C[++] background, so this question is raising alarm bells in my head.

2 Answers2

37

Yes, you have 32 characters at your disposal. SQL does not concern itself with nul terminated strings like some programming languages do.

nos
  • 223,662
  • 58
  • 417
  • 506
6

Your VARCHAR specification size is the max size of your data, so in this case, 32 characters. However, VARCHARS are a dynamic field, so the actual physical storage used is only the size of your data, plus one or two bytes.

If you put a 10-character string into a VARCHAR(32), the physical storage will be 11 or 12 bytes (the manual will tell you the exact formula).

However, when MySQL is dealing with result sets (ie. after a SELECT), 32 bytes will be allocated in memory for that field for every record.

zombat
  • 92,731
  • 24
  • 156
  • 164
  • +1 for the explanation of the memory alloc when SELECTing the field. I wonder how it works with TEXT and BLOB fields... – Leonel Jun 04 '12 at 21:23