When Should MySQL Store NULL vs a Default Value? A Deep Dive into Row Formats and Storage Impact

1. Row Data Storage

InnoDB stores each row together with metadata. The storage engine supports four row formats: REDUNDANT , COMPACT , DYNAMIC and COMPRESSED . Since MySQL 5.7 the default format is DYNAMIC .

Capabilities of each row format

REDUNDANT : compact storage = No; enhanced variable‑length column storage = No; large index‑key‑prefix support = No; compression = No; tablespace types = system, file‑per‑table, general; file format = Antelope or Barracuda.

COMPACT : compact storage = Yes; enhanced variable‑length column storage = No; large index‑key‑prefix support = No; compression = No; tablespace types = system, file‑per‑table, general; file format = Antelope or Barracuda.

DYNAMIC : compact storage = Yes; enhanced variable‑length column storage = Yes; large index‑key‑prefix support = Yes; compression = No; tablespace types = system, file‑per‑table, general; file format = Barracuda.

COMPRESSED : compact storage = Yes; enhanced variable‑length column storage = Yes; large index‑key‑prefix support = Yes; compression = Yes; tablespace types = file‑per‑table, general; file format = Barracuda.

The COMPACT layout is the basis for DYNAMIC and COMPRESSED . The diagram below shows the COMPACT row structure.

Example table creation (default charset latin1, InnoDB engine):

CREATE TABLE `t_user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(16) DEFAULT NULL,
  `email` VARCHAR(32) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After inserting two rows, the physical storage looks like the following diagrams.

The variable‑length column width list stores the lengths of non‑NULL variable‑length fields in reverse column order. If all columns are NOT NULL and have fixed length, this list is omitted.

The NULL‑value list is also stored in reverse order; a binary 1 marks a NULL entry, otherwise 0. When every column is NOT NULL, the NULL list is absent.

Each record header occupies 5 bytes and contains:

delete‑flag – indicates whether the record is logically deleted.

record_type – distinguishes ordinary rows from internal nodes.

next_record – pointer to the next physical record.

n_owned – number of rows owned by the current page.

InnoDB also adds hidden columns to every row:

DB_TRX_ID – transaction ID that last modified the row.

DB_ROLL_PTR – rollback pointer to the previous version for MVCC.

DB_ROW_ID – generated row ID used as a clustered index when no explicit primary key exists.

2. NULL Handling

Defining a column as NOT NULL provides several practical benefits:

Reduces storage overhead because the NULL‑value bitmap (1–2 bytes per column) is not needed.

Eliminates the need for null‑checks in application code, reducing the risk of NullPointerException.

Aggregates such as COUNT(col) count all rows, avoiding the “NULL is ignored” pitfall.

Indexes do not store NULL entries, improving index size and lookup speed.

Standard comparison operators ( =, !=, >, <) work directly, enabling range queries and IN/NOT IN semantics.

Allowing NULL also has clear semantic advantages:

Explicitly represents “no value” or “unknown”, which improves data meaning.

Facilitates straightforward filtering with WHERE col IS NULL.

Consistent behavior with SQL standards in joins and other operations.

In practice, developers sometimes replace NULL with placeholder strings (e.g., "-", "", "N/A") to avoid null checks, but this can cause downstream inconsistencies. When designing tables, the choice between NULL and a default value should be guided by overall data‑model conventions rather than marginal storage differences.