MySQL JSON 空值教程：SQL NULL、缺失路径与显式 null

配置接口把主题改成“空”，数据库里可能出现四种不同状态：整个 JSON 列是 SQL NULL，文档没有 theme 这个键，键存在且值为 JSON null，或者值恰好是字符串 "null"。它们在某些客户端里看起来相似，更新和过滤时却不能混用。

如果只写一句 theme IS NULL，先问这里的 theme 指的是列、提取表达式，还是应用反序列化后的字段。问题经常不是数据库“空值判断不准”，而是接口把几个不同状态压成了一个值。

构造每一种状态，不靠界面颜色判断

以下实验面向 MySQL 8.4，使用可空 JSON 列，故意不限制根文档一定是对象，以展示完整区别。

CREATE DATABASE json_null_lab;
USE json_null_lab;
CREATE TABLE user_setting (
    id BIGINT NOT NULL PRIMARY KEY,
    payload JSON NULL
) ENGINE=InnoDB;

INSERT INTO user_setting(id, payload) VALUES
    (1, NULL),
    (2, JSON_OBJECT('theme', NULL)),
    (3, JSON_OBJECT()),
    (4, JSON_OBJECT('theme', 'null')),
    (5, JSON_OBJECT('theme', 'dark')),
    (6, CAST('null' AS JSON)),
    (7, JSON_OBJECT('theme', ''));

第 1 行的整个列为 SQL NULL；第 2 行是对象里的显式 JSON null；第 3 行没有 theme；第 4 行保存四个字母组成的文本；第 6 行的整个文档是 JSON null；第 7 行则是一个存在的空字符串。它们不是七种写法产生同一种“空”。

用诊断查询同时保留列是否为空、根类型、路径是否存在和路径值类型：

SELECT id,
       payload IS NULL AS column_is_sql_null,
       COALESCE(JSON_TYPE(payload), 'SQL_NULL') AS root_type,
       JSON_CONTAINS_PATH(payload, 'one', '$.theme') AS has_theme,
       COALESCE(JSON_TYPE(JSON_EXTRACT(payload, '$.theme')),
                'SQL_NULL') AS theme_type,
       JSON_EXTRACT(payload, '$.theme') AS raw_theme
FROM user_setting
ORDER BY id;

这里 COALESCE 只用于把诊断输出中的 SQL NULL 标成文字，不代表业务字段应统一回填这串文字。根类型与子路径类型分开显示，才能区分“列没有文档”“文档不是对象”和“对象少了键”。

不要仅看 raw_theme 的可视化文本。数据库客户端可能用同一种灰色渲染 SQL NULL 与 JSON null，复制到电子表格后甚至进一步丢失类型。排障应保存可复现 SQL 和类型判断，而不是对着界面截图猜。

把每一类过滤条件写成自己的含义

查整个列为 SQL NULL：

SELECT id FROM user_setting WHERE payload IS NULL;

查文档是对象、但没有 theme 键：

SELECT id
FROM user_setting
WHERE JSON_TYPE(payload) = 'OBJECT'
  AND JSON_CONTAINS_PATH(payload, 'one', '$.theme') = 0;

查键存在且值为 JSON null：

SELECT id
FROM user_setting
WHERE JSON_TYPE(payload) = 'OBJECT'
  AND JSON_CONTAINS_PATH(payload, 'one', '$.theme') = 1
  AND JSON_TYPE(JSON_EXTRACT(payload, '$.theme')) = 'NULL';

查值为字符串 null：

SELECT id
FROM user_setting
WHERE JSON_TYPE(JSON_EXTRACT(payload, '$.theme')) = 'STRING'
  AND JSON_UNQUOTE(JSON_EXTRACT(payload, '$.theme')) = 'null';

字符串比较前保留类型条件，是为了避免在提取、去引号或应用转换后丢掉区别。空字符串同样是一种具体文本值，不应在未经业务约定时自动并入“没有配置”。

这几条查询的目标不同，返回不同 id 才正常。若接口只关心“没有可用主题”，可以明确组合多种条件；如果需要区分“继承默认值”和“主动清空”，就应保留状态，不要先一股脑归一化。

设置 null 与删除键，是两种更新协议

在事务里分别观察显式 null 和路径删除的差异：

START TRANSACTION;
UPDATE user_setting
SET payload = JSON_SET(payload, '$.theme', NULL)
WHERE id = 5;
SELECT payload FROM user_setting WHERE id = 5;
ROLLBACK;

START TRANSACTION;
UPDATE user_setting
SET payload = JSON_REMOVE(payload, '$.theme')
WHERE id = 5;
SELECT payload FROM user_setting WHERE id = 5;
ROLLBACK;

第一种把路径值设为 JSON null，第二种把键删除。配置系统经常把“没有键”解释为继承默认，把“明确 null”解释为禁用或清空；若产品采取这种协议，两种操作就绝不能共用一个含糊的“置空”按钮。

反过来，若业务根本没有显式 null 这个状态，应在接口契约里拒绝或转换它，别让数据库逐渐积累多种无人解释的载荷。转换规则应在写入入口可见，并有对应测试，不能仅靠某个导入脚本临时补丁。

对 SQL NULL 执行 JSON_SET，不会自动长出对象

START TRANSACTION;
UPDATE user_setting
SET payload = JSON_SET(payload, '$.theme', 'light')
WHERE id = 1;
SELECT payload FROM user_setting WHERE id = 1;
ROLLBACK;

JSON_SET 的文档参数为 SQL NULL 时，结果仍为 SQL NULL。若本意是“没有文档则先建空对象”，需要显式初始化：

START TRANSACTION;
UPDATE user_setting
SET payload = JSON_SET(COALESCE(payload, JSON_OBJECT()),
                       '$.theme', 'light')
WHERE id = 1;
SELECT payload FROM user_setting WHERE id = 1;
ROLLBACK;

这只处理 SQL NULL，不会把第 6 行根部的 JSON null 也当成缺失列。JSON null 是一个有效 JSON 值，COALESCE 没理由替你把它换成对象。需要统一根类型时，应先明确允许的类型并选择修复规则。

更深路径也不能靠猜。父节点不存在、父节点是标量、路径指向数组时，修改函数各有自己的创建与忽略语义。对用户可配置的任意路径，先验证载荷结构和路径白名单，避免让一次局部修改改变整个文档形状。

配置读取要先决定默认值在哪里生效

一个常见混乱是数据库查询把缺失主题填成 dark，应用反序列化又把 JSON null 填成 light，前端最后把空字符串也当默认。用户看到的结果取决于走了哪条接口，原始文档反而无法解释。

更容易维护的方式是给每个状态分配唯一含义，并把默认值应用在明确的一层。读取接口可以同时返回原始配置与解析后的有效值，但不要把派生默认值写回原始字段，冒充用户曾经明确设置过。

局部更新接口尤其应区分“请求里未出现这个字段”和“请求显式传 null”。前者可能表示保持原值，后者可能表示删除或清空。服务端若在反序列化阶段把两者都变成语言里的空指针，后面再写 SQL 已经无从恢复原意。

这份契约还会影响审计。记录“主题从 dark 变为空”不足以重放操作；应能判断是删除键、设 JSON null，还是重置整个文档。审计保存什么，应该与接口允许的动作一一对应。

换成 JSON_VALUE，会主动合并一部分空状态

JSON_VALUE 适合提取标量，但它的结果契约与原始 JSON 提取不同。MySQL 8.4 在路径值为 JSON null 时返回 SQL NULL，默认的缺失路径处理也会返回 SQL NULL。因此，查询写得更短，不代表原始状态区别仍然保留。

SELECT id,
       JSON_VALUE(payload, '$.theme'
                  RETURNING CHAR(20)
                  NULL ON EMPTY ERROR ON ERROR) AS scalar_theme
FROM user_setting
WHERE id IN (2, 3, 4, 5)
ORDER BY id;

本例第 2 行显式 JSON null、第 3 行缺失路径，都可以映射成 SQL NULL；第 4 行仍是字符串 null。若读取只想得到一个可用标量，这种收敛可能正合适；若要审计用户是否主动清空，必须另外保留路径存在信息。

可以用 ERROR ON EMPTY 把缺失路径显式暴露出来：

-- 预期失败：id=3的对象中不存在theme路径。
SELECT JSON_VALUE(payload, '$.theme'
                  RETURNING CHAR(20)
                  ERROR ON EMPTY ERROR ON ERROR)
FROM user_setting WHERE id = 3;

注意这里测试的是路径不存在，不是把 JSON null 也强行解释成缺失。ON EMPTY 与 ON ERROR 的顺序和作用不同，代码中应完整写明；不能把所有提取异常都捕获后返回空字符串，那会再次抹平类型错误与正常缺省。

这也解释了为什么一次重构从 JSON_EXTRACT 改成 JSON_VALUE 后，空值统计可能变化。新函数可能主动把不同原始载荷映射成同一种 SQL 结果，迁移前要先约定统计口径，而不是看到数量不同就判断数据被改坏。

参数绑定仍然要保住调用者的原意

客户端传入语言层的空值、字符串 null，以及已经编码的 JSON 文本，是三种输入。驱动负责按参数类型发送值，不负责判断业务想表达什么。接口若允许三种形式混用，应先统一请求模型，禁止调用者通过反复编码把对象变成字符串。

可以把前面七行作为固定回归数据集，逐一通过实际驱动读取，再检查返回值的类型标签和内容，而不是只做字符串比较。对更新接口，则分别发送字段缺失、显式空值和普通文本，核对数据库最终文档形状。

这一组验证应停在能够解释状态差异的结果上：哪一行是继承，哪一行是清空，哪一行是字面文本。只要这份映射确定，SQL 就能写得直接；映射不确定，再多一层 COALESCE 也只是把问题藏得更深。

最后核对第 7 行的空字符串。它的路径存在，类型是 STRING，字符长度为零；它不是 SQL NULL，因此简单使用 COALESCE 也不会替换它。如果界面把空字符串显示成“使用默认主题”，那是额外的产品规则，必须在映射中写出来。

SELECT id,
       JSON_CONTAINS_PATH(payload, 'one', '$.theme') AS has_theme,
       CHAR_LENGTH(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.theme'))) AS text_length
FROM user_setting WHERE id = 7;

与空字符串相对，包含空格的字符串长度不为零。是否去空格仍然由配置协议决定，不能在读取时悄悄 TRIM 后再把结果当用户原始设置。

所有验证完成后，清理独立实验库：

DROP DATABASE json_null_lab;