When to Store NULL vs Default Values in MySQL: Row Format and Storage Implications

1. Row Data Storage

MySQL stores not only the column data but also auxiliary information for each row. InnoDB supports four row formats—REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED. Since MySQL 5.7 the default is DYNAMIC , which offers compact storage, enhanced variable‑length column handling, large index‑key prefixes, and compression support.

The table below (originally from the MySQL documentation) summarizes the characteristics of each format:

REDUNDANT : No compact storage, no variable‑length column support, no large index‑key prefix, no compression.

COMPACT : Compact storage, no variable‑length column support, no large index‑key prefix, no compression.

DYNAMIC : Compact storage, supports variable‑length columns, supports large index‑key prefixes, no compression.

COMPRESSED : Compact storage, supports variable‑length columns, supports large index‑key prefixes, supports compression.

Both DYNAMIC and COMPRESSED are improvements over COMPACT, sharing a similar internal structure.

2. Example Table and Row Layout

Creating a sample table demonstrates how MySQL records rows:

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 and examining the physical storage (shown in the following screenshots) reveals the actual row layout.

The variable‑length column width list stores the lengths of non‑NULL variable‑length fields in reverse column order. Similarly, the NULL‑value list records the presence of NULLs using a single bit per column (1 for NULL, 0 otherwise).

Hidden Columns

DB_TRX_ID – transaction ID that modified the row.

DB_ROLL_PTR – pointer to the previous version for rollback.

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

Record Header (5 bytes)

delete‑flag – marks a row as deleted; actual removal occurs later.

record_type – distinguishes ordinary rows from internal nodes.

next_record – pointer to the next row in the page.

n_owned – number of records owned by the page.

3. NULL Handling

Defining a column as NOT NULL offers several benefits:

Reduces storage overhead because the NULL‑value list (1–2 bytes per column) is omitted.

Prevents NullPointerException‑type bugs in application code.

Ensures COUNT(col) includes all rows, avoiding hidden NULLs.

Improves index efficiency, as indexes do not store NULL entries.

Enables standard comparison operators (=, !=, >, <) without special handling.

Avoids empty result sets when using IN/NOT IN with NULL columns.

Allowing NULL also has legitimate advantages:

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

Flexibility – easy to filter with IS NULL / IS NOT NULL.

Compatibility – JOIN and other operations treat NULL consistently, preserving data integrity.

In practice, developers sometimes replace NULL with placeholder strings such as “‑”, “”, or “N/A”. While this can sidestep null‑related errors, it introduces inconsistencies across downstream systems and complicates data cleaning.

When designing tables, the choice between NULL and a default value should be guided by overall system design standards and consistency rather than micro‑optimizing storage space.