A guide to avoid pits when the default value of MySQL field is NULL

Pit 1: Logical confusion ——NULL ≠ null value

You might thinkNULL = ''It's true, but it's actuallyFake or unknown. This is a typical trap of three-value logic.

Example

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) DEFAULT NULL
);

INSERT INTO users (id) VALUES (1);

-- Looks like empty names，Actually, it can't be found
SELECT * FROM users WHERE name = '';
-- check NULL Must be explicitly written：
SELECT * FROM users WHERE name IS NULL;

Pit 2: Low index hit rate

When MySQL executes a query, it will determine whether to use the index based on statistical information. When a field is large in numberNULL, even if the index is created, it is possibleNo indexing。

Example

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(10) DEFAULT NULL
);

CREATE INDEX idx_status ON orders(status);

-- Insert data，in 90% yes NULL
INSERT INTO orders (id, status)
SELECT , IF( % 10 = 0, 'PAID', NULL)
FROM mysql.help_topic t1 LIMIT 1000;

-- Query NULL The value may not go idx_status
EXPLAIN SELECT * FROM orders WHERE status IS NULL;

Pit Three: Unique Index Failure

In a unique index, multipleNULLIt is allowed, which meansYou cannot rely on unique constraints to prevent repeated insertions。

Example

CREATE TABLE email_users (
    id INT PRIMARY KEY,
    email VARCHAR(255) DEFAULT NULL,
    UNIQUE KEY uniq_email(email)
);

INSERT INTO email_users (id, email) VALUES (1, NULL);
INSERT INTO email_users (id, email) VALUES (2, NULL); -- success！

Pit 4: Inconsistent behavior of aggregate functions

When you do statistics,NULLWill beAutomatically ignore, resulting in inconsistent results.

Example

CREATE TABLE survey (
    id INT PRIMARY KEY,
    age INT DEFAULT NULL
);

INSERT INTO survey (id, age) VALUES (1, 20), (2, NULL), (3, 30);

SELECT COUNT(age) AS count_age, AVG(age) AS avg_age FROM survey;

Recommended practice

CREATE TABLE users (
    id INT PRIMARY KEY,
    is_active TINYINT(1) NOT NULL DEFAULT 0
);

Summarize

The default value of the field isNULLIt seems flexible, but it hides much more problems than you expected:

• Query difficult to write
• Instable index
• Statistics are inaccurate
• Uniqueness invalid

so,Unless you really need to indicate "unknown" or "meaningless", it is highly recommended to avoid NULL as the default.

This is the article about this guide to avoid pits when the default value of MySQL fields is NULL. For more related content related to MySQL fields default value of NULL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!