SQLite AFTER UPDATE触发器重命名重复文件名技术问询

我来给你一个能解决这个需求的方案，结合全量更新语句和触发器，既能处理现有数据的重复文件名，又能自动维护后续插入/更新的记录：

首先我们需要对已有的数据批量生成带序号的文件名，这里用窗口函数来标记每个重复组内的序号：

WITH RankedPhotos AS (
    SELECT 
        ContactID, -- 假设你的表有唯一主键ContactID，用来关联更新
        PersonName || '_' || Date AS BaseName,
        ROW_NUMBER() OVER (PARTITION BY PersonName, Date ORDER BY ContactID) AS NameRank
    FROM contacts_New
)
UPDATE contacts_New cn
SET PhotoName = 
    CASE 
        WHEN rp.NameRank = 1 THEN rp.BaseName
        ELSE rp.BaseName || '-' || rp.NameRank
    END
FROM RankedPhotos rp
WHERE cn.ContactID = rp.ContactID;

这段代码的逻辑是：

• 按PersonName和Date分组，给每个组内的记录按主键排序编号
• 编号为1的记录保留基础文件名（PersonName_Date），编号≥2的记录在后面加上-序号（比如-2、-3）

为了让后续插入新记录或者修改PersonName/Date时自动生成不重复的文件名，我们需要创建一个BEFORE INSERT/UPDATE触发器，下面以PostgreSQL为例（其他数据库语法略有差异，但逻辑一致）：

-- 先定义触发器函数
CREATE OR REPLACE FUNCTION GenerateUniquePhotoName()
RETURNS TRIGGER AS $$
DECLARE
    base_name TEXT;
    duplicate_count INT;
BEGIN
    -- 生成基础文件名
    base_name := NEW.PersonName || '_' || NEW.Date;
    
    -- 统计已存在的同名记录数（UPDATE时排除自身）
    IF TG_OP = 'UPDATE' THEN
        SELECT COUNT(*) INTO duplicate_count
        FROM contacts_New
        WHERE PersonName = NEW.PersonName AND Date = NEW.Date
          AND ContactID <> OLD.ContactID; -- 排除当前正在更新的记录
    ELSE -- INSERT操作
        SELECT COUNT(*) INTO duplicate_count
        FROM contacts_New
        WHERE PersonName = NEW.PersonName AND Date = NEW.Date;
    END IF;
    
    -- 生成最终的唯一文件名
    NEW.PhotoName := CASE
        WHEN duplicate_count = 0 THEN base_name
        ELSE base_name || '-' || (duplicate_count + 1)
    END;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器，仅当PersonName或Date变更时触发
CREATE TRIGGER ReNamePhotoNames
BEFORE INSERT OR UPDATE OF PersonName, Date ON contacts_New
FOR EACH ROW
EXECUTE FUNCTION GenerateUniquePhotoName();

触发器逻辑说明： #

• 触发时机：在插入新记录或者修改PersonName/Date字段之前执行，确保写入数据库的PhotoName已经是唯一的
• 重复计数：插入时统计当前表中同名的记录数；更新时排除自身，避免把当前记录算进重复数里
• 文件名生成：没有重复时用基础名，有重复则在后面加上-(重复数+1)，保证序号依次递增

• 确保你的表有唯一主键（比如示例中的ContactID），否则UPDATE时无法正确排除自身记录
• 如果使用SQL Server、MySQL等其他数据库，触发器函数的语法需要调整（比如SQL Server用CREATE TRIGGER直接写逻辑，MySQL用DELIMITER定义函数）
• 全量更新和触发器要配合使用：先运行全量更新处理现有数据，再创建触发器维护后续的新增/修改操作

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