0

As a newbie to Mysql I just can't understand how NULL values are stored in each row formats.

As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. Meanwhile, NULLs for variable length columns are handled differently. The behaviour is defined under REDUNDANT row format description. I doubt what will be the behaviour with DYNAMIC / COMPRESSED row formats?

Note: I have tried to create 997 bigints in a table and have inserted value for one column. I had around 1957 rows but only had 26 pages, which means NULLs doesn't occupy space for fixed length. Am I understanding it in correct way? Please anyone help! Specifications: 5.7.23 | Innodb | Dynamic Row | 16kb page size

  • I verified page count with py_innodb_page_info.py script – Dinesh Kumar Sep 19 '18 at 10:38
  • It is rarely wise to have more than 100 columns of any type in a table. Please explain the table further; we may have other advice that looks beyond just the NULL question you have posed. – Rick James Oct 8 '18 at 0:14
0

The second bullet point 'The variable-length part of the record header contains a bit vector for indicating NULL columns'.

So when a row has a field that is NULL, the bit in the bit vector of the header is set.

AFAIK this is the same for DYNAMIC/COMPRESSED that really only changed the handling of variable length fields.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.