1

I have a MySQL database and I'd like to know if I have a VARCHAR(32) with no data inside it, does it take any space, like some kb?

The reason I'm asking it is because I store a hash of 32 chars to check something but after a while I'll not use it anymore, so if it takes some of DB space, I might make this field null or empty.

If so, should I make this field null or empty to save some space?

William
  • 1,010
  • 3
  • 21
  • 39
  • Regardless of the answer, I doubt you'll win any significant space this way. – HoneyBadger Aug 17 '16 at 12:05
  • please note. MySQL release never allocated space also if you delete row. the only way to free it is to save the table, truncate it and fill it again – Bernd Buffen Aug 17 '16 at 12:05
  • If you look [at the manual](http://dev.mysql.com/doc/refman/5.7/en/char.html) it is all explain very nicely as long as you can be botherd to read it. – RiggsFolly Aug 17 '16 at 12:08
  • @BerndBuffen, I guess you are right, it seems that if I change the field from 32 chars to 0 it doesn't save any space, but do you have any references to that? – William Aug 17 '16 at 12:15

1 Answers1

2

NULL values occupy space for the NULL flag. In addition, any fixed component of the data type is also stored. For a VARCHAR() that is the length component, which is either 1 or 2 bytes.

The storage requirement for VARCHAR() is explained in the documentation. For VARCHAR(32) the length is stored as a single byte.

Arrgh. I forgot that in MySQL, this depends on the storage engine. The above is true for MyISAM. It is different for InnoDB. Here is a good answer.

Community
  • 1
  • 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786