MySQL字符串类型的字段默认值NULL、空值、Empty String的区别，哪个更好？-艾林博客

MySQL默认值NULL、空值、Empty String的区别，哪个更好？

当我们在数据库中添加一个新的字段时，通常会面临选择：应该设置默认值为NULL、空值，还是Empty String（空字符串）？这篇文章将帮助你理解这些选择之间的差异及其影响

三种值的介绍

空值：
- 空值（空白）通常意味着字段没有被显式设置。当在设计表结构时保存空值，它通常会自动转变为NULL。
NULL：
- NULL表示未知或未定义的值。它在数据库中是一个特殊的标记，不等同于空字符串或零。
Empty String：
- Empty String是一个长度为0的字符串，表示字段已被定义为一个空的文本值，即''或""。

NOT NULL的好处

节省空间：
- NULL列需要额外的字节来存储是否为NULL的标志位，因此使用NOT NULL可以节省空间。
减少空指针问题：
- 查询时，可以减少与NULL相关的空指针异常问题。
减少计算错误：
- 在统计时，NULL值不会被计入，例如COUNT(column)会忽略NULL值，这可能导致意外的结果。

设置为NULL的坏处

索引效率：
- 含有NULL值的列在查询优化时较困难，索引中不存储NULL值，这可能导致索引效率下降。建议使用0、特殊值或空字符串代替。
负向条件查询：
- 使用!=或NOT IN时，NULL值可能导致查询结果为空，容易引发错误。
占用空间：
- NULL虽然不占据数据存储空间，但需要额外字节标记，空字符串''不需要这个标记。
统计问题：
- COUNT()统计时忽略NULL，但不忽略空字符串，这可能导致统计分析误差。
查询复杂性：
- 在SQL中，判断NULL需要使用IS NULL或IS NOT NULL，而空字符串则可以用常规比较操作符。

综上建议

字符串类型字段：推荐设置默认值为空字符串''。这样可以避免NULL带来的复杂性，并确保字段始终有可比较的值。
整数类型字段：建议设置默认值为0。这避免了处理NULL整数值的复杂性，并提供一个明确的初始值。

扩展

在MySQL中，WHERE <column> IS NOT NULL这种查询条件通常会导致全表扫描（full table scan），即使该列上存在索引。这主要是由于以下几个原因：

索引查找和全表扫描的区别

索引结构：
- 索引是按照特定顺序（如B-Tree）存储的，主要用于高效查找特定值或范围内的值。
- IS NOT NULL需要查找所有非NULL值，这实际上是一个范围查询，可能分布在整个表的数据页中。
数据分布：
- IS NOT NULL条件需要遍历所有可能的行以确定非NULL值。这涉及大量随机I/O操作，可能导致性能问题。
- 即使索引中存储了NULL和非NULL值，但为了查找所有非NULL值，仍然会造成大量随机读写，效率不高。

执行计划

通过查看执行计划（EXPLAIN），我们可以理解MySQL优化器如何处理这种查询。假设我们有如下表结构和索引：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) DEFAULT NULL
);

CREATE INDEX idx_username ON users(username);

使用EXPLAIN查看查询计划：

EXPLAIN SELECT * FROM users WHERE username IS NOT NULL;

通常结果会显示：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | idx_username  | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+------+-------+

解释

type = ALL：
- ALL表示全表扫描。
- 即使possible_keys显示idx_username为可能使用的索引，但优化器认为全表扫描更有效。
rows：
- 估计需要扫描的行数，这个数值较大，表明全表扫描。

为什么全表扫描？

覆盖范围广：
- IS NOT NULL条件覆盖了所有非NULL值，索引无法有效地利用区间信息进行高效查找。
数据访问模式：
- 索引通常用于查找特定值或较小范围内的值。
- 对于大量分布广泛的数据，索引查找效率较低，因为需要多次随机访问数据页。
优化器选择：
- MySQL优化器基于统计信息选择最优查询方案。
- 在数据分布和索引效能较差的情况下，全表扫描可能会比索引查找更快。

解决方案

若查询非NULL值频繁且数据量大，可以考虑业务逻辑调整或设计更适合的索引方案。
例如，新增一个标志字段，明确区分NULL和非NULL状态，通过该标志进行查询。

示例改进

ALTER TABLE users ADD COLUMN is_username_not_null TINYINT(1) GENERATED ALWAYS AS (username IS NOT NULL);

CREATE INDEX idx_is_username_not_null ON users(is_username_not_null);

EXPLAIN SELECT * FROM users WHERE is_username_not_null = 1;

这种方式通过增加生成列和索引，优化查询性能。