Should You Embrace NULL Values in Your MySQL Database?

When Null is the Right Database Option

When working with database tables in MySQL, the use of NULL values may raise concerns. Despite its semantic significance, some sources advise against its usage, citing performance issues. This article delves into the circumstances where using NULL is appropriate and explores the trade-offs involved.

Appropriate Circumstances for Null

Using NULL is appropriate when:

• A field truly represents a missing or inapplicable value.
• Semantically, NULL conveys that a value is distinct from both "false" and the empty string.

Performance Considerations

Past articles have mentioned performance concerns regarding nullable fields. However, empirical evidence to support these claims is lacking. It is essential to approach such claims with caution, relying on reproducible measurements rather than idle suppositions.

In MySQL, utilizing an index can optimize searches for NULL values. The following example demonstrates the use of an index with a NULL search:

mysql> CREATE TABLE foo (
  i INT NOT NULL,
  j INT DEFAULT NULL,
  PRIMARY KEY (i),
  UNIQUE KEY j_index (j)
);

mysql> INSERT INTO foo (i, j) VALUES 
  (1, 1), (2, 2), (3, NULL), (4, NULL), (5, 5);

mysql> EXPLAIN SELECT * FROM foo WHERE j IS NULL;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | foo   | ref  | j_index       | j_index | 5       | const |    2 | Using where | 
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

Avoiding Nulls: A Misguided Approach

Substituting NULL with values like zero, blank strings, or false is a flawed design decision. These values may represent meaningful data within the column. Using NULL allows for the full range of data values to be utilized without ambiguity.

The above is the detailed content of Should You Embrace NULL Values in Your MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!