Why Avoid NULL Columns in MySQL: Reasons, Pitfalls, and Practical Examples

MySQL treats NULL as a special marker that requires an extra byte per row to indicate the absence of a value, which increases storage and complicates index handling. The official MySQL documentation notes that each NULL column adds one bit (rounded to a byte) to the row.

Many developers mistakenly prefer NULL because it seems convenient for optional data, but this leads to several practical problems:

NULL values prevent in‑place updates, causing index fragmentation and slower writes.

Timestamp columns without explicit_defaults_for_timestamp can behave unexpectedly when NULL is allowed.

Negative conditions such as NOT IN or != return no rows when NULLs are present, causing logical errors.

Indexes on NULL columns store an extra byte per entry, and MyISAM indexes become variable‑length, increasing index size.

Aggregations like COUNT(column) ignore NULLs, potentially skewing statistics.

Instead of using NULL, you can assign a meaningful default value that enforces data integrity and simplifies code. The article lists concrete reasons to avoid NULL:

All cases where NULL is used can be represented by a domain‑specific sentinel value, improving readability and maintainability.

Updating a NULL column to a non‑NULL value forces a row rewrite, increasing index split risk.

NULL in timestamp columns can cause hidden bugs unless explicit defaults are set.

Queries with NOT IN or != involving NULL always evaluate to UNKNOWN, yielding empty result sets.

NULL columns require an extra storage byte for the null‑flag.

Code examples illustrate these issues. For instance, a table with a nullable user_name column produces different index lengths compared to a NOT NULL column, as shown by the EXPLAIN output and the calculated key_len values (62 vs. 83 bytes).

create table table_2 (
    `id` INT(11) NOT NULL,
    user_name varchar(20) NOT NULL
);

create table table_3 (
    `id` INT(11) NOT NULL,
    user_name varchar(20)
);

insert into table_2 values (1,'alice'),(2,'bob');
insert into table_3 values (1,'alice'),(2,NULL);

-- Example of NOT IN returning empty set when NULL exists
select user_name from table_2 where user_name not in (
    select user_name from table_3 where id!=1
);

The output demonstrates that the presence of NULL makes the NOT IN subquery return no rows, leading to unexpected results.

Further, the article shows that index length for a VARCHAR(20) column differs between the two tables because one uses UTF8 (3 bytes per character) and the other UTF8MB4 (4 bytes) plus the null‑flag byte, confirming that NULL adds storage overhead.

In conclusion, avoiding NULL columns simplifies indexing, improves query correctness, reduces storage, and leads to more predictable performance; therefore, using NOT NULL with appropriate default values is recommended.