When Should You Store NULL vs Default Values in MySQL? A Deep Dive into Row Formats

1. Row Data Storage

When MySQL stores a row, it saves not only the column values but also auxiliary metadata. InnoDB supports four row formats; since MySQL 5.7 the default is Dynamic. The four formats are REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED, each with different storage characteristics such as compactness, variable‑length column handling, index prefix support, compression, and tablespace type compatibility.

Both DYNAMIC and COMPRESSED are improvements over COMPACT, sharing a similar structure. The variable‑length column width list stores the lengths of non‑NULL variable‑length fields in reverse column order.

Creating a table with nullable columns:

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 shows how MySQL stores the row format, variable‑length column width list, and NULL‑value list.

The NULL‑value list is stored in reverse order; a NULL is represented by binary 1, otherwise 0.

2. NULL Handling

Defining a column as NOT NULL has several advantages:

Reduces storage because the extra 1‑2 bytes for the NULL‑value list are avoided.

Lowers the risk of NullPointerException in application code.

Prevents count(column) from ignoring rows with NULL.

Improves index efficiency since indexes do not store NULL values.

Enables normal comparison operators (=, !=, >, <) instead of only IS NULL / IS NOT NULL.

Allows range queries (IN, NOT IN) to return expected results.

However, keeping columns nullable also offers benefits:

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

Flexibility: NULL can be filtered easily with IS NULL in WHERE clauses.

Compatibility: JOIN operations treat NULL consistently, preserving data integrity.

In practice, developers often replace NULL with placeholder defaults such as “-”, “”, or “N/A”, which can avoid null‑pointer issues but may introduce inconsistencies across systems. When designing tables, the choice between NULL and a default value should be guided more by overall system design standards and consistency rather than minor storage differences.