Does a NULL DATE use as much storage as a value?

Question

I need to store a DATETIME value that may or may not have a date.

My options are:

• | starts DATETIME     |
  |---------------------|
  | 0000-00-00 12:38:06 |
  

• | starts_date DATE | starts_time TIME |
  |------------------|------------------|
  | NULL             | 12:38:06         |
  

If a NULL DATE value takes 0 storage, I may save a few bytes on most records (8 bytes on full DATETIME vs 3 bytes on just TIME). However, the following pages say that NULLs still take as much storage as the actual values on the fixed length columns, such as CHAR vs. VARCHAR. Is the DATE considered fixed-length too?

• The byte size of NULL Decimal(19,4) or DATETIME (this is for SQL Server though)
• Does an empty column value occupy same storage space as a filled column value

Answer 1

If you are using MyISAM, don't.

If you found that DATETIME is 8 bytes, you are using an old version of MySQL; it takes only 5 bytes now. Upgrade.

"Fixed" is slightly important for MyISAM; rarely important for InnoDB. So don't worry. In particular, don't worry unless you have a billion rows.

Answer 2

For InnoDB Tables

 Helpful Notes About NULLs: 

For the third row, I inserted NULLs in FIELD2 and FIELD3. Therefore in the Field Start Offsets the top bit is on for these fields (the values are 94 hexadecimal, 94 hexadecimal, instead of 14 hexadecimal, 14 hexadecimal). And the row is shorter because the NULLs take no space.

See https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

For MyISAM table

you need at least some space for thge header, but it should be very small

see the exact https://dev.mysql.com/doc/internals/en/myisam-introduction.html