Understanding NULL vs Empty String in MySQL: Storage, Operations, and Best Practices

When a column does not have strict requirements, developers often wonder whether to use an empty string ("") or NULL. Although many projects have their own conventions, the underlying reasons are rarely documented.

In MySQL, NULL is a special marker that indicates the absence of a value. Whether a column permits NULL and whether its default is NULL affect both storage and query behavior.

1. Storage difference between NULL and empty string

If a column allows NULL, each row gets a NULL‑bitmap flag. The flag itself occupies a few bits, but the actual NULL value does not consume space in the row data. When all columns are defined NOT NULL, the bitmap is omitted.

2. Operational issues with NULL

For numeric columns, aggregate functions treat NULL specially:

In MIN, MAX, SUM the NULL rows are ignored. AVG also ignores NULL, which may produce unexpected results if the caller assumes a zero denominator.

Example table:

CREATE TABLE `t1` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sample data and queries:

SELECT * FROM t1;

+----+----------+--------+
| id | name     | number |
+----+----------+--------+
|  1 | zhangsan | NULL   |
|  2 | lisi     | NULL   |
|  3 | wangwu   | 0      |
|  4 | zhangliu | 4      |
+----+----------+--------+

Running SELECT MAX(number) FROM t1; returns 4, while SELECT MIN(number) FROM t1; returns 0. SELECT SUM(number) FROM t1; yields 4, and SELECT AVG(number) FROM t1; yields 2.0000 because the two NULL rows are excluded.

Arithmetic with NULL always produces NULL:

SELECT 1 + NULL;

+--------+
| 1+NULL |
+--------+
| NULL   |
+--------+

3. Ordering

When ordering ascending, NULL values appear first; descending order places them last.

SELECT * FROM t1 ORDER BY number ASC;

+----+----------+--------+
| id | name     | number |
+----+----------+--------+
|  1 | zhangsan | NULL   |
|  2 | lisi     | NULL   |
|  3 | wangwu   | 0      |
|  4 | zhangliu | 4      |
+----+----------+--------+

4. GROUP BY / DISTINCT handling

NULL

values are considered equal for GROUP BY and DISTINCT purposes.

SELECT DISTINCT(number) FROM t1;

+--------+
| number |
+--------+
| NULL   |
| 0      |
| 4      |
+--------+

SELECT number, COUNT(*) FROM t1 GROUP BY number;

+--------+----------+
| number | count(*) |
+--------+----------+
| NULL   | 2        |
| 0      | 1        |
| 4      | 1        |
+--------+----------+

5. Character columns and NULL

When a column is of type CHAR/VARCHAR, it is easy to confuse the literal string 'NULL' with the special NULL value.

INSERT INTO t1 (name, number) VALUES ('NULL', 5);
INSERT INTO t1 (number) VALUES (6);

SELECT * FROM t1 WHERE number IN (5,6);

+----+------+--------+
| id | name | number |
+----+------+--------+
| 5  | NULL | 5      |
| 6  | NULL | 6      |
+----+------+--------+

Testing for NULL must use IS NULL or IS NOT NULL, not equality operators.

SELECT name IS NULL FROM t1 WHERE number=5;

+--------------+
| name IS NULL |
+--------------+
| 0            |
+--------------+

6. Counting rows containing NULL

Aggregate COUNT(*) counts all rows, while COUNT(column) ignores NULL values.

SELECT COUNT(*) FROM t1;   -- returns 6
SELECT COUNT(name) FROM t1; -- returns 5

7. Using LENGTH() on VARCHAR

Applying LENGTH() to a NULL value returns NULL, not a numeric length.

SELECT LENGTH(name) FROM t1 WHERE name IS NULL;

+--------------+
| LENGTH(name) |
+--------------+
| NULL         |
+--------------+

Conclusion

NULL

is a special marker in MySQL that behaves differently from an empty string in storage, arithmetic, ordering, grouping, and aggregation. Because its semantics differ from ordinary values, it is advisable to avoid using NULL as a default whenever possible and to handle it explicitly with IS NULL / IS NOT NULL checks.