Why MySQL Shouldn’t Use NULL as a Default Column Value – Myths Debunked

Introduction

Many developers set NULL as the default value for columns in MySQL because it seems convenient, but this practice can cause ambiguous query results and degrade database performance.

Understanding NULL in MySQL

NULL is a special constraint indicating an unknown or missing value. It is not equal to an empty string (''), and MySQL provides three main operators to work with NULL: IS NULL, IS NOT NULL, and the three‑way comparison operator <=>. Additionally, the IFNULL() function can replace NULL with a specified value.

SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

Practical Examples

Creating a simple table and inserting rows with and without NULL values:

CREATE TABLE test_null (id INT NOT NULL, name VARCHAR(10));
INSERT INTO test_null VALUES (1, 'zlm');
INSERT INTO test_null VALUES (2, NULL);
SELECT * FROM test_null;

Querying with IS NULL returns the row containing NULL, while using the equality operator = NULL returns an empty set:

SELECT * FROM test_null WHERE name IS NULL;   -- returns row with NULL
SELECT * FROM test_null WHERE name = NULL;    -- returns empty set

Using the three‑way operator <=> treats two NULLs as equal:

SELECT NULL <=> NULL;   -- returns 1 (true)

Impact on Indexes

Contrary to the common belief that columns with NULL cannot use indexes, MySQL does allow indexing on nullable columns. The following example shows that both primary key and secondary indexes work even when the column contains NULL values:

EXPLAIN SELECT id, k FROM sbtest1 WHERE id = 100001;   -- uses PRIMARY index
EXPLAIN SELECT id, k FROM sbtest1 WHERE k IS NULL;   -- uses secondary index k_1

Note that a NULL value requires an extra byte in each row to store the null‑flag.

Effect on Aggregate Functions and Sorting

NULL values are ignored by COUNT(*) but affect COUNT(column):

SELECT COUNT(*), COUNT(name) FROM test_null;   -- returns 2 and 1

When using DISTINCT, GROUP BY, or ORDER BY, MySQL treats NULLs as equal, which can lead to unexpected grouping or ordering results.

SELECT DISTINCT name FROM test_null;   -- returns 'zlm' and NULL
SELECT name FROM test_null GROUP BY name;   -- groups NULL together
SELECT id, name FROM test_null ORDER BY name;   -- NULL rows appear first

Recommendations

Because NULL introduces uncertainty, extra storage overhead, and can complicate SQL logic, it is advisable to define columns as NOT NULL and use sentinel values such as 0 or an empty string to represent missing data. When NULL cannot be avoided, use IFNULL() to handle it explicitly.