Why MySQL Discourages Using NULL as Default Column Value – A 5‑Minute Deep Dive

Introduction

Many developers default columns to NULL for convenience, but this practice can introduce uncertainty in queries and degrade database performance.

Understanding NULL in MySQL

NULL represents a missing or unknown value, distinct from an empty string. MySQL provides three operators to work with NULL: IS NULL, IS NOT NULL, and the null‑safe equality operator <=>, as well as the IFNULL() function.

IS NULL returns true when the column value is NULL.

IS NOT NULL returns true when the column value is not NULL.

<=> returns true when both operands are NULL (e.g., NULL <=> NULL).

IFNULL(expr1, expr2) returns expr2 if expr1 is NULL.

Common Misconception

A frequent interview answer claims that using NULL in a column disables indexes, but MySQL can still use indexes with NULL values; the statement is only partially correct.

Practical Examples

(root@localhost mysql3306.sock)[zlm]> create table test_null(id int not null, name varchar(10));
Query OK, 0 rows affected (0.02 sec)

(root@localhost mysql3306.sock)[zlm]> insert into test_null values(1,'zlm');
Query OK, 1 row affected (0.00 sec)

(root@localhost mysql3306.sock)[zlm]> insert into test_null values(2,null);
Query OK, 1 row affected (0.00 sec)

(root@localhost mysql3306.sock)[zlm]> select * from test_null;
+----+------+
| id | name |
+----+------+
|  1 | zlm  |
|  2 | NULL |
+----+------+

(root@localhost mysql3306.sock)[zlm]> select * from test_null where name is null;
+----+------+
| id | name |
+----+------+
|  2 | NULL |
+----+------+

Using IS NULL in the WHERE clause correctly utilizes the index, while a direct comparison with = NULL returns an empty set.

Impact on Aggregates and Queries

Expressions involving NULL always yield NULL, affecting functions like COUNT(), MAX(), and MIN(). For example, COUNT(*) counts all rows, but COUNT(column) ignores NULLs, leading to different results.

SELECT COUNT(*), COUNT(name) FROM test_null;
+----------+-------------+
| COUNT(*) | COUNT(name) |
+----------+-------------+
|        2 |           1 |
+----------+-------------+

NULL values are treated as equal in DISTINCT, GROUP BY, and ORDER BY, which can affect sorting and grouping outcomes.

INSERT INTO test_null VALUES (3, null);
SELECT DISTINCT name FROM test_null;
+------+
| name |
+------+
| zlm  |
| NULL |
+------+

Index Usage with NULL

MySQL supports indexing columns that contain NULL values, unlike some other databases. The extra storage required is one additional byte per row to flag the NULL state.

Best Practices

To avoid the pitfalls of NULL defaults, define columns as NOT NULL and use zero, empty strings, or appropriate sentinel values instead of NULL. This reduces uncertainty, improves performance, and simplifies SQL logic.

Conclusion

NULL values introduce uncertainty and can degrade performance through index usage, aggregation inaccuracies, and added query complexity. Defining columns as NOT NULL and handling missing data explicitly is the recommended approach.