-- ============================================================================
|
-- (sub_id, scriptid) 去重 + 唯一索引 —— 根治"同一患者同一题堆出多份重复明细"
|
-- 南华附一（nhfy）2026-10-21
|
--
|
-- 【为什么要做】
|
-- 保存答案/补默认值的 upsert 走 isExistServiceSubtaskDetail，历史上只看 del_flag=0；
|
-- 明细一旦被逻辑删除就查不到 → 下一次保存必然 INSERT，副本越积越多
|
-- （2026-09-08 那 10 位患者各堆了 2~6 份，答案完全相同）。
|
-- 代码侧已改为「忽略删除标记取最新一行 + 更新后 reviveServiceSubtaskDetail 复活」，
|
-- 本脚本再从数据库层加一道唯一约束兜底。
|
--
|
-- 【现状（2026-10-21 实测 smartor_nhfy）】
|
-- 明细总数 346,628；sub_id / scriptid 均无 NULL
|
-- 重复组（同 sub_id+scriptid 多于 1 行）：5,389 组，涉及 12,680 行
|
-- 若"每组只留 1 行"，需要物理删除 7,291 行（绝大多数是 del_flag=1 的历史副本）
|
--
|
-- 【执行顺序】建议低峰期，按 0 → 4 顺序执行；第 3 步是物理删除，务必先跑第 2 步备份
|
-- 【回滚】第 5 步；删掉的行走 bak_ssd_dup_20261021 还原
|
-- ============================================================================
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【0】评估（只读，随时可跑）
|
-- ----------------------------------------------------------------------------
|
SELECT COUNT(*) AS dup_groups,
|
SUM(rows_cnt) AS rows_in_dup_groups,
|
SUM(rows_cnt - 1) AS rows_to_delete_if_keep_one
|
FROM (SELECT sub_id, scriptid, COUNT(*) AS rows_cnt
|
FROM service_subtask_detail
|
WHERE sub_id IS NOT NULL AND scriptid IS NOT NULL
|
GROUP BY sub_id, scriptid
|
HAVING COUNT(*) > 1) x;
|
|
-- 重复最多的 10 组，看看是不是都长一个样
|
SELECT sub_id, scriptid, COUNT(*) AS rows_cnt,
|
SUM(del_flag = 0) AS live_cnt,
|
COUNT(DISTINCT COALESCE(asrtext, '')) AS distinct_answers
|
FROM service_subtask_detail
|
GROUP BY sub_id, scriptid
|
HAVING COUNT(*) > 1
|
ORDER BY rows_cnt DESC
|
LIMIT 10;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【1】备份"将被删除"的行（约 7,291 行）
|
-- 保留规则：同一 (sub_id, scriptid) 保留 1 行 —— 优先 del_flag=0，其次 id 最大
|
-- ----------------------------------------------------------------------------
|
-- DROP TABLE IF EXISTS bak_ssd_dup_20261021;
|
-- CREATE TABLE bak_ssd_dup_20261021 AS
|
-- SELECT d.*
|
-- FROM service_subtask_detail d
|
-- JOIN (SELECT sub_id, scriptid,
|
-- SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY (del_flag = 0) DESC, id DESC), ',', 1) AS keep_id
|
-- FROM service_subtask_detail
|
-- WHERE sub_id IS NOT NULL AND scriptid IS NOT NULL
|
-- GROUP BY sub_id, scriptid
|
-- HAVING COUNT(*) > 1) k
|
-- ON k.sub_id = d.sub_id AND k.scriptid = d.scriptid
|
-- WHERE d.id <> k.keep_id;
|
-- SELECT COUNT(*) FROM bak_ssd_dup_20261021; -- 预期 ≈ 7291
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【2】备份自检（确认备份表里没有"每组都应该保留的那一行"）
|
-- ----------------------------------------------------------------------------
|
-- SELECT COUNT(*) AS wrong_rows
|
-- FROM bak_ssd_dup_20261021 b
|
-- JOIN (SELECT sub_id, scriptid,
|
-- SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY (del_flag = 0) DESC, id DESC), ',', 1) AS keep_id
|
-- FROM service_subtask_detail
|
-- GROUP BY sub_id, scriptid) k
|
-- ON k.sub_id = b.sub_id AND k.scriptid = b.scriptid
|
-- WHERE b.id = k.keep_id; -- 预期 0
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【3】物理删除重复行（每组保留 1 行）
|
-- ----------------------------------------------------------------------------
|
-- DELETE d
|
-- FROM service_subtask_detail d
|
-- JOIN (SELECT sub_id, scriptid,
|
-- SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY (del_flag = 0) DESC, id DESC), ',', 1) AS keep_id
|
-- FROM service_subtask_detail
|
-- WHERE sub_id IS NOT NULL AND scriptid IS NOT NULL
|
-- GROUP BY sub_id, scriptid
|
-- HAVING COUNT(*) > 1) k
|
-- ON k.sub_id = d.sub_id AND k.scriptid = d.scriptid
|
-- WHERE d.id <> k.keep_id;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【4】加唯一索引
|
-- ⚠️ 前提：第 3 步已跑过（否则必然 ORA/1062 失败）
|
-- ⚠️ 346k 行的 ADD UNIQUE 会短时锁表，建议低峰执行；
|
-- 如果生产版本 MySQL 支持 online DDL，可用 ALGORITHM=INPLACE, LOCK=NONE
|
-- ----------------------------------------------------------------------------
|
-- ALTER TABLE service_subtask_detail
|
-- ADD UNIQUE KEY uk_ssd_sub_script (sub_id, scriptid),
|
-- ALGORITHM = INPLACE, LOCK = NONE;
|
|
-- 校验
|
-- SHOW INDEX FROM service_subtask_detail WHERE Key_name = 'uk_ssd_sub_script';
|
-- SELECT COUNT(*) AS dup_groups_should_be_0 FROM (
|
-- SELECT sub_id, scriptid FROM service_subtask_detail
|
-- GROUP BY sub_id, scriptid HAVING COUNT(*) > 1) x;
|
|
|
-- ----------------------------------------------------------------------------
|
-- 【5】回滚
|
-- ----------------------------------------------------------------------------
|
-- ALTER TABLE service_subtask_detail DROP INDEX uk_ssd_sub_script;
|
-- INSERT INTO service_subtask_detail SELECT * FROM bak_ssd_dup_20261021;
|
-- DROP TABLE bak_ssd_dup_20261021;
|
|
|
-- ============================================================================
|
-- 【加索引之后必须注意】
|
-- 唯一索引生效后，并发保存同一 (sub_id, scriptid) 时"后到的那一条 INSERT"会抛
|
-- DuplicateKeyException。代码侧已经做了两件事降低风险：
|
-- ① isExistServiceSubtaskDetail 改为"忽略 del_flag 取最新一行"→ 正常保存走 UPDATE，不会 INSERT；
|
-- ② /smartor/subtaskAnswer/saveQuestionAnswer 加了 Redis 幂等占位（60 秒，同一 pid+tid+subid 只处理一次）。
|
-- 但如果将来还有别的写入路径直接 INSERT 明细，建议在插入处 catch DuplicateKeyException 后转 UPDATE。
|
-- ============================================================================
|
