如何排查MySQL中questions表违反指定一致性规则的异常数据

现有表结构 #

CREATE TABLE `questions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(4000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `question_type_id` int(10) unsigned NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `order` int(10) unsigned NOT NULL,
  `section_id` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `questions_section_id_foreign` (`section_id`),
  CONSTRAINT `questions_section_id_foreign` FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`)
);

一致性规则要求 #

• 同一个section_id下的所有问题，order必须是从1到N的连续整数，不能重复也不能有空隙（N是该section下的问题总数）
• 如果存在question_type_id = 6的问题，必须恰好有3个，且order依次为1、2、3，对应标题分别为'A'、'B'、'C'（该类型问题为可选，不存在则无需满足此规则）

违规Section查询语句 #

1. 查找存在排序重复或间隙的Section #

-- 查询同一section下order重复的情况
SELECT section_id, `order`, COUNT(*) AS 重复次数
FROM questions
WHERE deleted_at IS NULL -- 排除软删除数据
GROUP BY section_id, `order`
HAVING 重复次数 > 1

UNION ALL

-- 查询同一section下order存在间隙的情况
SELECT s.section_id, NULL AS `order`, '存在间隙' AS 违规类型
FROM (
    SELECT section_id, COUNT(*) AS 问题总数, MAX(`order`) AS 最大order值
    FROM questions
    WHERE deleted_at IS NULL
    GROUP BY section_id
) s
WHERE s.问题总数 != s.最大order值 OR s.最大order值 < 1;

2. 查找question_type_id=6不符合要求的Section #

SELECT 
    section_id,
    CASE
        WHEN COUNT(*) != 3 THEN '数量不为3个'
        WHEN MAX(CASE WHEN `order`=1 THEN title END) != 'A' THEN 'order=1的标题不是A'
        WHEN MAX(CASE WHEN `order`=2 THEN title END) != 'B' THEN 'order=2的标题不是B'
        WHEN MAX(CASE WHEN `order`=3 THEN title END) != 'C' THEN 'order=3的标题不是C'
        WHEN MIN(`order`) !=1 OR MAX(`order`) !=3 THEN 'order不是连续的1、2、3'
        ELSE '其他违规'
    END AS 违规类型
FROM questions
WHERE question_type_id =6 AND deleted_at IS NULL
GROUP BY section_id
HAVING COUNT(*) !=3 
    OR MAX(CASE WHEN `order`=1 THEN title END) != 'A'
    OR MAX(CASE WHEN `order`=2 THEN title END) != 'B'
    OR MAX(CASE WHEN `order`=3 THEN title END) != 'C'
    OR MIN(`order`) !=1 
    OR MAX(`order`) !=3;

额外问题：数据库层面能否添加约束防止违规？ #

可以，但无法仅靠原生约束覆盖所有规则，需要结合触发器实现，具体如下：

原生约束可直接实现的规则 #

给section_id和order添加唯一索引，直接阻止同一section下出现重复的order值：

ALTER TABLE questions ADD UNIQUE INDEX idx_section_order (section_id, `order`);

需通过触发器实现的规则 #

原生约束无法处理“order连续无间隙”和“type=6的特殊规则”，需要编写触发器在插入、更新、删除数据时做校验：

1. 校验同一section的order连续无间隙

• 插入/更新触发器：插入或更新数据时，先查询当前section的问题总数，确保新的order在1到“总数+1”范围内（插入场景），或更新后问题总数等于最大order值。
• 删除触发器：删除数据后，要么自动将后续问题的order减1以维持连续性，要么校验剩余问题的order是否仍保持连续（若允许手动调整则仅做校验提示）。

2. 校验type=6的特殊规则

插入或更新question_type_id=6的记录时，触发器需检查：

• 该section下type=6的记录数量不超过3个
• 插入的order与标题严格对应（如order=1的标题必须为'A'）
• 更新操作后仍符合数量、order、标题的要求

注意事项 #

• MySQL 8.0.16及以上版本支持CHECK约束，但对于“同一section的order连续性”这类跨行校验，CHECK约束无法实现，仍需依赖触发器。
• 触发器会增加数据库操作的开销，需权衡性能与数据一致性需求。
• 涉及软删除（deleted_at）的场景，触发器校验时需排除已删除的记录。

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