MySQL大表Longtext列JSON验证：触发器实现方案咨询

针对1亿行数据的大表，直接将存储JSON字符串的Longtext列改为JSON类型会导致长时间表锁，因此可以通过BEFORE INSERT和BEFORE UPDATE触发器，在数据写入或更新前验证JSON格式，拦截非法数据。

具体触发器实现 #

假设存储JSON的列名为json_data，以下是完整实现代码（注意修改分隔符避免语法错误）：

DELIMITER //

-- 插入前验证JSON格式
TRIGGER `before_insert_user`
BEFORE INSERT
ON `users`
FOR EACH ROW
BEGIN
    IF NOT JSON_VALID(NEW.json_data) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'json_data列的JSON格式无效';
    END IF;
END //

-- 更新前验证JSON格式（仅当列值被修改时触发）
TRIGGER `before_update_user`
BEFORE UPDATE
ON `users`
FOR EACH ROW
BEGIN
    -- 仅在json_data列值变更时执行验证，减少不必要的性能开销
    IF NEW.json_data <> OLD.json_data THEN
        IF NOT JSON_VALID(NEW.json_data) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'json_data列的JSON格式无效';
        END IF;
    END IF;
END //

DELIMITER ;

关键说明 #

• 依赖函数：使用MySQL内置的JSON_VALID()函数验证格式，要求MySQL版本≥5.7。
• 错误拦截：通过SIGNAL抛出用户自定义错误（SQLSTATE '45000'为通用用户错误码），非法数据会被直接阻止写入/更新。
• 性能优化：更新触发器中增加了列值变更判断，避免每次更新都执行验证，降低对高频更新操作的性能影响。

额外注意事项 #

1. 历史数据校验：触发器仅对新插入/更新的数据生效，已存在的历史Longtext数据需要单独分批校验，修正格式错误的数据。
2. 性能评估：行级触发器会增加单条写入/更新的耗时，若表的写入量极高，需先在测试环境评估性能影响。
3. 权限要求：创建触发器需要TRIGGER权限，确保执行操作的账号具备对应权限。

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