如何约束MySQL的JSON列仅存储JSON对象而非字符串或数组？

当然可以！你有两种靠谱的方式来实现这个需求——要么用MySQL原生的CHECK约束（推荐给新版本用户），要么用触发器兼容旧版本。下面给你详细拆解：

一、原生CHECK约束（优先推荐） #

从MySQL 8.0.16版本开始，CHECK约束不再是“摆设”，会实际生效拦截不符合要求的数据。你可以直接给notifications列加个约束，强制它只能存JSON对象：

ALTER TABLE your_table_name
ADD CONSTRAINT chk_notifications_must_be_object
CHECK (JSON_TYPE(notifications) = 'OBJECT');

注意事项： #

• 这个约束只管新插入/更新的数据，已经存在的那些字符串类型的旧数据不会被自动修复，你得先手动处理（后面会说怎么弄）。
• 如果你的MySQL版本低于8.0.16，CHECK约束不会生效，这时候就得用触发器方案了。

二、触发器方案（兼容旧版本） #

正如你提到的，触发器可以在数据写入前做检查，不符合要求就直接抛错。这里给你补全了插入和更新两种场景的触发器（只写插入的话，更新数据时可能会绕过约束）：

DELIMITER $$
-- 插入前校验
CREATE TRIGGER trg_notifications_object_before_insert
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    IF JSON_TYPE(NEW.notifications) <> 'OBJECT' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误：notifications列必须存储JSON对象类型';
    END IF;
END$$

-- 更新前校验
CREATE TRIGGER trg_notifications_object_before_update
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    IF JSON_TYPE(NEW.notifications) <> 'OBJECT' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误：notifications列必须存储JSON对象类型';
    END IF;
END$$
DELIMITER ;

这里把SQLSTATE改成了45000（用户自定义错误码），比你原来用的02000（警告码）更合适——毕竟这是强制约束，应该抛出错误而非警告，直接阻止非法数据写入。

先处理现有不符合要求的数据 #

不管用哪种方案，你都得先把已经存在的问题数据修好：
那些被转义成字符串的JSON，可以用JSON_UNQUOTE()函数把它转成真正的JSON对象，然后更新回去：

UPDATE your_table_name
SET notifications = JSON_UNQUOTE(notifications)
WHERE JSON_TYPE(notifications) = 'STRING';

更新完后，再跑一遍查询确认：

SELECT notifications, JSON_TYPE(notifications) FROM your_table_name GROUP BY notifications;

确保所有非NULL的notifications都变成OBJECT类型了。

内容的提问来源于stack exchange，提问作者mankowitz