-- ============================================================
|
-- 安全生产 - 修复 uk_code 唯一索引导致「第二次软删除同编码」报错
|
-- 生成时间：2026-10-29
|
--
|
-- 现象：巡检点位 PD-001 先被软删一次，之后新建同编码 PD-001 再删除时报
|
-- Duplicate entry 'PD-001-\x01' for key 'safety_inspection_point.uk_code'
|
-- 接口返回「系统异常」(500)
|
--
|
-- 原因：uk_code 建在 (code, deleted) 上，deleted 是 bit(1)。
|
-- 软删除后 deleted=b'1'，若历史上已有一条同编码且已软删的记录，
|
-- 第二次软删除就会插入重复的 (code, b'1') 组合，撞唯一键。
|
--
|
-- 修复：去掉 (code, deleted) 唯一约束，改成普通索引 idx_code 用于查询；
|
-- 编码唯一性由 Service 层保证（新增/修改时 selectByCode 查活跃记录判重，
|
-- 与 yudao 其它业务表一致，例如 mes_dv_machinery 就没有 code 唯一键）。
|
--
|
-- 幂等：先判断索引是否存在再操作，可重复执行
|
-- ============================================================
|
|
DROP PROCEDURE IF EXISTS sp_safety_fix_code_index;
|
DELIMITER $$
|
CREATE PROCEDURE sp_safety_fix_code_index(IN p_table VARCHAR(64))
|
BEGIN
|
-- 1. 删掉旧的 (code, deleted) 唯一键
|
IF EXISTS (SELECT 1 FROM information_schema.statistics
|
WHERE table_schema = DATABASE() AND table_name = p_table AND index_name = 'uk_code') THEN
|
SET @s = CONCAT('ALTER TABLE `', p_table, '` DROP INDEX `uk_code`');
|
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
|
END IF;
|
-- 2. 补一个普通索引，保证按编码查询的性能
|
IF NOT EXISTS (SELECT 1 FROM information_schema.statistics
|
WHERE table_schema = DATABASE() AND table_name = p_table AND index_name = 'idx_code') THEN
|
SET @s2 = CONCAT('CREATE INDEX `idx_code` ON `', p_table, '` (`code`)');
|
PREPARE st2 FROM @s2; EXECUTE st2; DEALLOCATE PREPARE st2;
|
END IF;
|
END$$
|
DELIMITER ;
|
|
CALL sp_safety_fix_code_index('safety_inspection_point');
|
CALL sp_safety_fix_code_index('safety_hazard');
|
CALL sp_safety_fix_code_index('safety_material');
|
CALL sp_safety_fix_code_index('safety_work_permit');
|
CALL sp_safety_fix_code_index('safety_accident');
|
|
DROP PROCEDURE IF EXISTS sp_safety_fix_code_index;
|
|
-- ---------- 校验：确认已无 uk_code，且有 idx_code ----------
|
SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE,
|
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
|
FROM information_schema.statistics
|
WHERE table_schema = DATABASE() AND TABLE_NAME LIKE 'safety%'
|
AND INDEX_NAME IN ('uk_code', 'idx_code')
|
GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE
|
ORDER BY TABLE_NAME;
|
