Why You Should Avoid NULL Columns in MySQL: Performance, Storage, and Index Impacts

Recently I started a new job and noticed many database fields lack NOT NULL constraints, which is intolerable for a perfectionist, prompting this article.

In most development practices we set all fields to

NOT NULL

and provide default values.

Typical defaults are:

Integers use 0 as the default.

Strings use an empty string.

Dates use

1970-01-01 08:00:01

or

0000-00-00 00:00:00

, but the connection parameter

zeroDateTimeBehavior=convertToNull

must be added; it is better to avoid these default date formats.

Why set columns to NOT NULL? A quote from High Performance MySQL:

Avoid NULL as much as possible. Many tables contain nullable columns even when applications do not need to store NULL; this is because NULL is the default attribute of a column. Usually it is best to specify columns as NOT NULL unless you truly need to store NULL values. Nullable columns make optimization harder for MySQL because they complicate indexes, index statistics, and value comparisons. Nullable columns consume more storage space and require special handling. When a nullable column is indexed, each index record needs an extra byte, and in MyISAM this can turn a fixed‑size index into a variable‑size one. The performance gain from converting nullable columns to NOT NULL is small, so it is not a primary tuning target unless it causes problems. However, when planning to index a column, you should avoid designing it as nullable. There are exceptions: InnoDB stores NULL in a separate bit, offering good space efficiency for sparse data, but this does not apply to MyISAM.

Considering InnoDB, the reasons to avoid NULL are:

If NOT NULL is not set, NULL becomes the default value; avoid it unless truly needed.

Using NULL introduces more problems such as complex index handling, statistics, and value calculations; avoid NULL for indexed columns.

Nullable indexed columns increase storage usage and require extra handling.

Sparse data (many NULLs, few non‑NULL values) benefits from special storage efficiency.

Default Values

For MySQL, if a column is not explicitly set to NOT NULL, its default on insert is NULL.

NULL and NOT NULL represent different meanings: NULL means the value is unknown, while an empty value means we know the value is empty.

Example: a

name

field that is NULL indicates we do not know the name; an empty string indicates we know there is no name.

Most programs do not need NULL fields; NULL can cause null‑pointer‑like issues.

When using MyBatis, it is recommended to use the generated

insertSelective

method or manually write insert statements to avoid errors caused by adding NOT NULL columns without proper defaults.

Value Computation

Aggregate functions

Aggregates ignore NULL values; for example,

count(*)

counts all rows, while

count(name)

counts only rows where

name

is not NULL, which can lead to unexpected results.

Equality comparison

NULL cannot be compared with

=

; you must use

IS NULL

.

Operations with other values

Any arithmetic with NULL yields NULL; for instance, adding 1 to a NULL age remains NULL, and concatenating NULL with a string also yields NULL.

distinct, group by, order by

All NULLs are considered equal for

distinct

and

group by

. For

order by

, ascending NULLs appear first.

Index Issues

Testing shows that adding indexes on columns that may contain NULL does not prevent index usage for

IS NULL

or range queries, but as the proportion of NULLs grows, index effectiveness degrades.

Storage Space

InnoDB rows are stored in one of four formats: REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED. The default format is COMPACT.

Each row includes a NULL‑value bitmap; each nullable column adds one bit (rounded to a byte) to indicate NULL status, so nullable columns consume an extra byte per row.

Example table with one NOT NULL column

c1

and other nullable columns demonstrates the row layout, showing variable‑length fields, the NULL bitmap, and actual column values.

References: https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html https://www.cnblogs.com/zhoujinyi/articles/2726462.html