SQLite触发器优化：将sqlmodded设为INTEGER默认0能否提升性能？

我正在编写一系列更新查询，用来评估表中记录，合适时修改一个或多个字段。之前用触发器修改sqlmodded字段来标记已更改的记录，方便导出这些记录更新底层文件元数据，旧触发器代码如下：

/* old faithful trigger */
CREATE TRIGGER sqlmods
         AFTER UPDATE
            ON alib
      FOR EACH ROW
          WHEN old.sqlmodded IS NULL
BEGIN
    UPDATE alib
       SET sqlmodded = TRUE
     WHERE rowid = NEW.rowid;
END;

现在我想拓展sqlmodded的用途，让它通过单个触发器实现递增，同时满足两个需求：

• 值>0时，表示该记录的更改次数
• 值为NULL（或必要时默认0）时，表示从未被修改

调试后得到了可行的触发器：

/* proposed trigger to signify change and number of changes processed */
CREATE TRIGGER IF NOT EXISTS sqlmods
                       AFTER UPDATE
                          ON alib
                    FOR EACH ROW
                        WHEN old.sqlmodded IS NULL
BEGIN
    UPDATE alib
       SET sqlmodded = iif(sqlmodded IS NULL, '1', (CAST (sqlmodded AS INTEGER) + 1) ) 
     WHERE rowid = NEW.rowid;
END;

因为表有数十万行、多个字段，且会运行大量更新查询，想请教：把sqlmodded定义为INTEGER类型并设默认值0，是否能带来显著性能提升？有没有更优的实现方式？

1. 关于INTEGER默认0的性能提升 #

肯定会有显著性能提升，原因如下：

• 当前触发器里用到了CAST类型转换和字符串转整数操作，每次触发都要做类型转换，在数十万行、大量更新的场景下，这类转换的累积开销会非常明显。
• SQLite对整数类型的操作（比如加法）比处理字符串/NULL值高效得多，不需要额外的NULL判断和类型校验逻辑，能直接减少触发器的执行时间和CPU占用。
• 默认设为0后，不需要再处理sqlmodded IS NULL的分支，逻辑更简洁，进一步降低了执行开销。

2. 更优的实现方式 #

你当前的触发器存在一个逻辑bug：WHEN old.sqlmodded IS NULL的条件意味着只有第一次修改（之前sqlmodded是NULL）才会触发计数，后续更新时old.sqlmodded已经是数值了，触发器不会执行，导致计数永远停在1，完全无法统计多次更改的次数。

结合INTEGER默认0的优化，给出更优实现步骤：

步骤1：修改表结构 #

先把sqlmodded改成INTEGER类型并设置默认值0，同时迁移现有数据（如果之前已有数据）：

-- 先把现有NULL值转为0，字符串类型的计数转为整数
UPDATE alib SET sqlmodded = 0 WHERE sqlmodded IS NULL;
UPDATE alib SET sqlmodded = CAST(sqlmodded AS INTEGER) WHERE sqlmodded IS NOT NULL;

-- 修改字段类型为INTEGER，默认值0（SQLite 3.35.0+支持ALTER COLUMN语法）
ALTER TABLE alib ALTER COLUMN sqlmodded TYPE INTEGER DEFAULT 0;

-- 如果是旧版本SQLite，需要创建新表迁移数据：
-- CREATE TABLE alib_new AS SELECT *, IFNULL(CAST(sqlmodded AS INTEGER), 0) AS sqlmodded FROM alib;
-- DROP TABLE alib;
-- ALTER TABLE alib_new RENAME TO alib;

步骤2：优化触发器 #

去掉错误的WHEN条件，简化递增逻辑：

CREATE TRIGGER IF NOT EXISTS sqlmods
AFTER UPDATE ON alib
FOR EACH ROW
BEGIN
    UPDATE alib
    SET sqlmodded = sqlmodded + 1
    WHERE rowid = NEW.rowid;
END;

这个触发器会在每次更新记录时触发，直接把sqlmodded加1，没有额外的类型转换或分支判断，执行效率拉满，同时能准确统计所有更改次数。

额外建议 #

如果你的更新查询可能存在“更新后字段值没有变化”的情况（比如更新语句执行但实际没修改数据），可以给触发器加个条件，避免无效计数：

CREATE TRIGGER IF NOT EXISTS sqlmods
AFTER UPDATE ON alib
FOR EACH ROW
WHEN OLD <> NEW -- 只有当记录确实发生变化时才触发
BEGIN
    UPDATE alib
    SET sqlmodded = sqlmodded + 1
    WHERE rowid = NEW.rowid;
END;

这样可以避免无意义的计数递增，进一步优化性能和统计准确性。

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