Why Avoid NULL Columns in MySQL: Reasons and Implications

In MySQL, many developers default to allowing NULL values in table columns, often because they are unaware of the consequences or think it simplifies data insertion. This article explains why that practice can be problematic and provides concrete reasons to prefer NOT NULL with appropriate defaults.

1. Why do many people use NULL?

NULL is the default when a table is created, and inexperienced programmers may overlook it. Some mistakenly believe that NOT NULL consumes more storage, which is not the main issue. The real reason is the perceived convenience of not having to check for nulls when inserting data.

2. Is it a myth?

Official MySQL documentation states that each NULL column adds an extra bit (rounded up to a full byte) to record whether the value is NULL. For MyISAM tables, this means additional storage per row. Moreover, MySQL’s optimizer has to handle nullable columns specially, making index statistics and value handling more complex.

NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

Nullable columns make index and statistics calculations more complicated and can increase storage requirements. When indexed, each row needs an extra byte, turning fixed‑size indexes into variable‑size ones.

3. Reasons to avoid NULL

Every situation that currently uses NULL can be represented by a meaningful default value, improving code readability, maintainability, and data constraints.

Updating a column from NULL to a non‑NULL value cannot be done in‑place, which may cause index splits and degrade performance.

Timestamp columns can behave unexpectedly unless the explicit_defaults_for_timestamp system variable is enabled.

Negative conditions such as NOT IN or != return empty results when a NULL value is present, leading to subtle bugs.

SQL examples that illustrate these pitfalls:

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 (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),(1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2");

insert into table_3 values (1,"zhaoliu_2_1"),(2, null);

-- 1. NOT IN with NULL returns no rows
select user_name from table_2 where user_name not in (
    select user_name from table_3 where id!=1
);

-- 2. Single‑column index does not store NULL values; composite index skips rows where all parts are NULL
select * from table_3 where name != 'zhaoliu_2_1';

-- 3. CONCAT with NULL yields NULL
select CONCAT('1', null) from dual;  -- result is NULL

-- 4. COUNT on a NULL column ignores NULLs
select count(user_name) from table_3;  -- returns 2, not 4

-- 5. Correct NULL check
select * from table_3 where user_name is null;

Additional observations show that nullable columns require an extra byte as a flag, and index length calculations depend on data type, character set, and nullability. For example, a VARCHAR(20) column in utf8mb4 with NULL has a key length of 83 bytes (20×4 + 1 flag + 2 length bytes), whereas the same column without NULL uses only 62 bytes.

In summary, avoiding NULL in MySQL tables reduces storage overhead, simplifies indexing, prevents query anomalies, and leads to more predictable performance. Use NOT NULL with sensible default values whenever possible.