huang
e049080f6c
All checks were successful
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Successful in 7m47s
98 lines
4.1 KiB
SQL
98 lines
4.1 KiB
SQL
-- 目的：支持上游 IoT 平台传入 19/20 位 ICCID 均能精确命中卡记录
|
||
-- 背景：部分运营商 19 位为唯一标识，部分运营商需要完整 20 位才能唯一标识一张卡
|
||
-- 策略：双列存储 + 按上游传入长度路由查询，Miss 时记录 Warn 日志不降级
|
||
|
||
-- 注意：golang-migrate 默认在事务中运行，CREATE INDEX CONCURRENTLY 不能在事务中执行
|
||
-- 因此使用 CREATE INDEX IF NOT EXISTS（非 CONCURRENTLY）
|
||
-- 生产环境若需零停机建索引，可手动执行 CONCURRENTLY 版本
|
||
|
||
-- ==========================================================================
|
||
-- 1. tb_iot_card 新增双列
|
||
-- ==========================================================================
|
||
|
||
ALTER TABLE tb_iot_card
|
||
ADD COLUMN IF NOT EXISTS iccid_19 varchar(19),
|
||
ADD COLUMN IF NOT EXISTS iccid_20 varchar(20);
|
||
|
||
COMMENT ON COLUMN tb_iot_card.iccid_19 IS 'ICCID前19位（所有卡必填，用于19位运营商精确查询）';
|
||
COMMENT ON COLUMN tb_iot_card.iccid_20 IS '完整20位ICCID（仅20位运营商卡有值，19位卡为NULL）';
|
||
|
||
-- ==========================================================================
|
||
-- 1.1a 冲突检测（执行后人工确认均为 0 行，否则停止迁移人工处理）
|
||
-- 检测 19 位前缀重复：若 19 位前缀不唯一，回填后 iccid_19 无法作为唯一索引列
|
||
-- ==========================================================================
|
||
|
||
DO $$
|
||
DECLARE
|
||
conflict_count INTEGER;
|
||
abnormal_count INTEGER;
|
||
BEGIN
|
||
-- 检测 19 位前缀重复
|
||
SELECT COUNT(*) INTO conflict_count
|
||
FROM (
|
||
SELECT LEFT(iccid, 19)
|
||
FROM tb_iot_card
|
||
WHERE deleted_at IS NULL
|
||
GROUP BY LEFT(iccid, 19)
|
||
HAVING COUNT(*) > 1
|
||
) t;
|
||
|
||
IF conflict_count > 0 THEN
|
||
RAISE EXCEPTION '冲突检测失败：发现 % 组 19 位前缀重复的 ICCID，请人工处理后再执行迁移', conflict_count;
|
||
END IF;
|
||
|
||
-- 检测异常长度 ICCID（长度既非 19 也非 20 的记录）
|
||
SELECT COUNT(*) INTO abnormal_count
|
||
FROM tb_iot_card
|
||
WHERE deleted_at IS NULL
|
||
AND LENGTH(iccid) NOT IN (19, 20);
|
||
|
||
IF abnormal_count > 0 THEN
|
||
RAISE WARNING '警告：发现 % 条异常长度 ICCID 记录（长度非19也非20），回填后这些卡将无法通过新查询路径命中', abnormal_count;
|
||
END IF;
|
||
END $$;
|
||
|
||
-- ==========================================================================
|
||
-- 1.2 回填存量数据
|
||
-- iccid_19 = LEFT(iccid, 19)（所有卡）
|
||
-- iccid_20 = iccid（仅当 LENGTH(iccid)=20，否则 NULL）
|
||
-- ==========================================================================
|
||
|
||
UPDATE tb_iot_card
|
||
SET iccid_19 = LEFT(iccid, 19),
|
||
iccid_20 = CASE WHEN LENGTH(iccid) = 20 THEN iccid ELSE NULL END;
|
||
|
||
-- ==========================================================================
|
||
-- 1.3 为 iccid_19 创建 Partial Index（过滤软删除记录）
|
||
-- ==========================================================================
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_iot_card_iccid_19
|
||
ON tb_iot_card (iccid_19)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
-- ==========================================================================
|
||
-- 1.4 为 iccid_20 创建 Partial Index（过滤软删除和 NULL 记录）
|
||
-- ==========================================================================
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_iot_card_iccid_20
|
||
ON tb_iot_card (iccid_20)
|
||
WHERE deleted_at IS NULL AND iccid_20 IS NOT NULL;
|
||
|
||
-- ==========================================================================
|
||
-- 1.5 tb_personal_customer_iccid 新增 iccid_19 列
|
||
-- ==========================================================================
|
||
|
||
ALTER TABLE tb_personal_customer_iccid
|
||
ADD COLUMN IF NOT EXISTS iccid_19 varchar(19);
|
||
|
||
COMMENT ON COLUMN tb_personal_customer_iccid.iccid_19 IS 'ICCID前19位（用于19位运营商精确查询）';
|
||
|
||
-- 回填存量数据
|
||
UPDATE tb_personal_customer_iccid
|
||
SET iccid_19 = LEFT(iccid, 19);
|
||
|
||
-- 创建对应 Partial Index
|
||
CREATE INDEX IF NOT EXISTS idx_personal_customer_iccid_19
|
||
ON tb_personal_customer_iccid (iccid_19)
|
||
WHERE deleted_at IS NULL;
|