shanxia2.0/docs/sql/migrations/2026-08-03-crm-contract-no-global-unique.sql

-- 合同编号租户内全局唯一（含软删不复用）— 可重复执行
-- 1) 消解已有 (contract_no, tenant_no) 撞号：保留 del_flag=0，软删行改号
-- 2) 唯一索引改为 (contract_no, tenant_no)
--
-- 说明：若生产已执行过，旧索引 uk_contract_tenant_no_del 可能已不存在，
--      本脚本会先检查再 DROP/ADD，避免 1091。

SET NAMES utf8mb4;

-- 软删撞号改号：{原编号}~DEL~{id前8位}
UPDATE crm_contract c
INNER JOIN (
  SELECT contract_no, tenant_no
  FROM crm_contract
  GROUP BY contract_no, tenant_no
  HAVING COUNT(*) > 1
) d ON c.contract_no = d.contract_no AND c.tenant_no = d.tenant_no
SET c.contract_no = CONCAT(c.contract_no, '~DEL~', LEFT(c.id, 8)),
    c.update_time = NOW()
WHERE c.del_flag = 1
  AND c.contract_no NOT LIKE '%~DEL~%'
  AND c.contract_no NOT LIKE '%~DUP~%';

-- 若仍有撞号（极端：两条都 del_flag=0），再改非 keep 的一条
UPDATE crm_contract c
INNER JOIN (
  SELECT contract_no, tenant_no, MIN(id) AS keep_id
  FROM crm_contract
  GROUP BY contract_no, tenant_no
  HAVING COUNT(*) > 1
) d ON c.contract_no = d.contract_no AND c.tenant_no = d.tenant_no AND c.id <> d.keep_id
SET c.contract_no = CONCAT(c.contract_no, '~DUP~', LEFT(c.id, 8)),
    c.update_time = NOW()
WHERE c.contract_no NOT LIKE '%~DUP~%';

-- 有旧索引则删除
SET @db := DATABASE();
SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = @db
        AND table_name = 'crm_contract'
        AND index_name = 'uk_contract_tenant_no_del'
    ),
    'ALTER TABLE crm_contract DROP INDEX `uk_contract_tenant_no_del`',
    'SELECT ''skip DROP uk_contract_tenant_no_del'' AS info'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 无新索引则添加
SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = @db
        AND table_name = 'crm_contract'
        AND index_name = 'uk_contract_tenant_no'
    ),
    'SELECT ''skip ADD uk_contract_tenant_no (already exists)'' AS info',
    'ALTER TABLE crm_contract ADD UNIQUE KEY `uk_contract_tenant_no` (`contract_no`, `tenant_no`)'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 验收：应为空
SELECT contract_no, tenant_no, COUNT(*) AS cnt
FROM crm_contract
GROUP BY contract_no, tenant_no
HAVING COUNT(*) > 1;

SHOW INDEX FROM crm_contract WHERE Key_name LIKE 'uk_contract%';