MySQL Interview Question: Store NULL or a Default Value in Nullable Columns?

1. Row formats and storage

InnoDB supports four row formats: REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED. Since MySQL 5.7 the default is DYNAMIC . Their main characteristics are:

REDUNDANT : no compact storage, no enhanced variable‑length column support, no large index‑prefix support, no compression; works with system, file‑per‑table and general tablespaces; file format Antelope or Barracuda.

COMPACT : compact storage, no enhanced variable‑length column support, no large index‑prefix support, no compression; same tablespace support as REDUNDANT; file format Antelope or Barracuda.

DYNAMIC : compact storage, supports enhanced variable‑length columns, supports large index‑prefixes, no compression; tablespace support same as above; file format Barracuda.

COMPRESSED : compact storage, supports enhanced variable‑length columns, supports large index‑prefixes, supports compression; requires file‑per‑table or general tablespace; file format Barracuda.

DYNAMIC and COMPRESSED share the same internal layout as COMPACT; the diagram below shows the COMPACT layout.

Example table definition

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;

Inserting two rows produces the storage layout shown in the following images.

The variable‑length column width list stores the lengths of non‑NULL variable‑length columns in reverse order of the table definition.

If every column is NOT NULL and has a fixed length, the variable‑length list disappears. The NULL‑value list (also stored in reverse order) uses a single bit per column: 1 for NULL, 0 otherwise.

Record header

The 5‑byte record header stores:

delete‑flag – indicates logical deletion.

record_type – distinguishes leaf from non‑leaf records.

next_record – pointer to the next row.

n_owned – number of rows owned by the page.

Hidden columns

DB_TRX_ID – transaction ID that last modified the row.

DB_ROLL_PTR – rollback pointer to the previous version.

DB_ROW_ID – generated clustered index key when no primary key is defined.

2. NULL handling

Defining a column NOT NULL provides:

Storage savings – eliminates the 1–2 bytes per column needed for the NULL‑value list.

Reduces the chance of NullPointerException in application code.

Aggregates such as COUNT(col) ignore NULL, yielding counts of real values.

Indexes do not store NULL entries, improving index efficiency.

Standard comparison operators (=, !=, >, <) work normally.

Range predicates ( IN, NOT IN) return expected results.

Allowing NULL offers:

Semantic clarity – NULL explicitly means “no value” or “unknown”.

Easy filtering with IS NULL in WHERE clauses.

Consistent behavior in JOINs and other operations, preserving data integrity.

In practice, some projects replace NULL with placeholder defaults such as "-", "" or "N/A" to avoid null checks. While this prevents immediate null‑pointer errors, it can introduce downstream inconsistencies when different subsystems interpret the placeholders differently.