When to Use NULL vs Default Values in MySQL? A Deep Dive into InnoDB Row Storage

1. Row Data Storage in InnoDB

MySQL stores not only the column data but also auxiliary information for each row. Since MySQL 5.7 the default InnoDB row format is Dynamic , and the engine supports four formats: Redundant , Compact , Dynamic , and Compressed . The key differences are summarised below:

Redundant : No compact storage, no variable‑length column support, no index‑prefix support, no compression; file formats are Antelope or Barracuda.

Compact : Enables compact storage and file‑per‑table, but still lacks variable‑length column support.

Dynamic : Supports compact storage, variable‑length columns, index‑prefixes, and works with Antelope or Barracuda file formats.

Compressed : Adds page‑level compression while keeping the other features of Dynamic.

The following diagram (image) illustrates the layout of the Compact format:

2. Example Table Creation

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;

Two rows are inserted into this table (see the second image). The storage layout of each row is shown in the third image.

3. Variable‑Length Column Width List

For variable‑length columns, InnoDB stores a list of the actual non‑null lengths. This list is stored in reverse order of the column definitions, i.e., the last variable‑length column appears first in the list.

4. NULL Value List

If a column can be NULL, a separate NULL‑value list is also stored in reverse order. Each entry is a single bit: 1 indicates the column is NULL, 0 indicates a non‑NULL value.

5. Hidden Columns

DB_TRX_ID : Transaction ID that created/modified the row.

DB_ROLL_PTR : Pointer to the previous version for rollback.

DB_ROW_ID : Synthetic primary key used when the table has no explicit primary key.

6. Record Header (5 bytes)

delete‑flag : Marks the row as deleted; MySQL performs lazy deletion.

record_type : Indicates whether the record is a leaf node, internal node, etc.

next_record : Pointer to the next record in the page.

n_owned : Number of records owned by this record’s “page directory”.

7. NULL Handling Trade‑offs

Defining a column as NOT NULL brings several advantages:

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

Reduces the chance of NullPointerException in application code.

COUNT(col) ignores NULLs, giving cleaner statistics.

Indexes do not store NULL entries, improving index efficiency.

Allows full comparison operators (=, !=, >, <) on the column.

Range queries (IN, NOT IN) work as expected.

Allowing NULL also has benefits:

Semantically clear – NULL means “no value” or “unknown”.

Easy to filter with WHERE col IS NULL.

Maintains compatibility with joins and other operations where NULL propagates as expected.

In practice, developers often replace NULL with placeholder strings such as "-", "", or "N/A" to avoid null checks, but this can cause downstream inconsistencies.

8. Practical Guidance

When designing tables, the decision to use NULL or a default value should be driven by overall system design standards and data‑consistency requirements rather than micro‑optimisations of storage space. Consistent conventions across subsystems lead to more robust processing logic.