MySQL 5.7 的 AFTER INSERT trigger 中 SELECT 偶发失效

2026-10-24数据小陈

结论

这不是 MySQL Query Cache 引起的“偶发失效”。根据现有 SQL 和示例，应优先检查以下三点：

实际查询包含严格的时间条件：
```
tl_duplicated.date_added = @billing_start_interval
```
示例中两条记录的 date_added 分别是 00:35:39 和 00:38:54，两者并不相等。如果第一条记录的时间不等于 @billing_start_interval，查询就不会选中它。
事后验证使用的 SQL 没有完整复现 trigger 的 WHERE 条件，其中既没有检查 gc_id，也没有检查 date_added = @billing_start_interval。因此，“其他字段全部相等”不足以证明 trigger 当时应该命中。
trigger 使用了 @duplicated_transaction_id、@billing_start_interval 等连接级用户变量。这类变量在同一个数据库连接中会持续存在，可能受到连接池、初始化遗漏和上一次执行结果的影响。trigger 内应优先使用通过 DECLARE 定义的局部变量。

还有一种可能：第一条记录插入后尚未提交。即使它的 date_added 比第二条早几小时，第二个事务仍可能看不到它。排查并发问题时，不能只比较业务时间或插入时间，还要确认事务的实际提交时间。

先核对实际执行的查询条件

当前的验证 SQL 只比较了部分字段。应按照 trigger 的完整条件重新执行查询，并将关键变量替换为 trigger 当时使用的实际值：

SELECT
    transaction_id,
    gc_id,
    function_name,
    report_options,
    source,
    result_code,
    i_person_id,
    billing_code,
    date_added
FROM log_mbs.transaction_log
WHERE gc_id = ?
  AND function_name = ?
  AND report_options = ?
  AND source = ?
  AND result_code = ?
  AND i_person_id <=> ?
  AND billing_code = ?
  AND date_added = ?
  AND transaction_id <> ?;

每个 ? 都必须替换为第二条记录进入 trigger 时对应的值。尤其要使用当时的 @billing_start_interval，不能事后重新计算。

示例中的两个时间是：

2023-06-10 00:35:39
2023-06-10 00:38:54

它们显然不相等。如果 @billing_start_interval 取的是第二条记录的 date_added，第一条记录不会命中查询。

时间条件可能写错了

从名称看，@billing_start_interval 更像是“计费区间起点”，但 SQL 使用了精确相等条件：

tl_duplicated.date_added = @billing_start_interval

如果业务规则是检查某个时间窗口内是否出现过相同调用，应改用范围条件。例如，要检查半开区间 [start, end)，可以写成：

AND tl_duplicated.date_added >= v_billing_start
AND tl_duplicated.date_added <  v_billing_end

如果要检查当前记录之前的一段时间，可以写成：

AND tl_duplicated.date_added >= NEW.date_added - INTERVAL 10 MINUTE
AND tl_duplicated.date_added <  NEW.date_added

具体窗口仍要依据计费规则确定，不能只根据变量名判断。

还需要检查这些细节：

date_added 的类型是 DATETIME 还是 TIMESTAMP；
字段是否包含小数秒；
应用、数据库会话和字段使用的时区是否不同；
@billing_start_interval 的数据类型是否与字段一致；
变量是否经过字符串转换或隐式类型转换。

可以直接查看变量及其类型：

SELECT
    @billing_start_interval,
    TYPEOF(@billing_start_interval);

需要注意的是，MySQL 5.7 没有通用的 TYPEOF() 函数。实际排查时，可以显式转换变量，或者将变量写入诊断表。例如：

SELECT
    @billing_start_interval,
    CAST(@billing_start_interval AS DATETIME);

不要在 trigger 中依赖 `@user_variables`

以 @ 开头的变量是会话级用户变量，不是 trigger 的局部变量。使用连接池时，同一个连接可能被多个业务请求复用。如果变量没有在每次执行前正确赋值，下一次 trigger 就可能读到旧值。

还有一个容易遗漏的行为：当 SELECT ... INTO 没有查到记录时，目标变量不一定会自动变为 NULL，所以必须显式初始化。旧值通常会导致“误判为重复”，而不是示例中的“漏判”，但这个隐患仍需修正。

建议改用 trigger 局部变量：

DECLARE v_duplicated_transaction_id VARCHAR(64) DEFAULT NULL;
DECLARE v_billable TINYINT DEFAULT 1;
DECLARE v_reason_code VARCHAR(255) DEFAULT '';
DECLARE v_billing_start DATETIME;
DECLARE v_billing_end DATETIME;

每次查询前先显式清空：

SET v_duplicated_transaction_id = NULL;

完整查询可以整理为：

SET v_duplicated_transaction_id = NULL;

SELECT tl.transaction_id
INTO v_duplicated_transaction_id
FROM log_mbs.transaction_log AS tl
WHERE tl.gc_id <=> NEW.gc_id
  AND tl.function_name <=> NEW.function_name
  AND tl.report_options <=> NEW.report_options
  AND tl.source <=> NEW.source
  AND tl.result_code <=> NEW.result_code
  AND tl.i_person_id <=> NEW.i_person_id
  AND tl.billing_code <=> NEW.billing_code
  AND tl.date_added >= v_billing_start
  AND tl.date_added <  v_billing_end
  AND tl.transaction_id <> NEW.transaction_id
ORDER BY tl.date_added, tl.transaction_id
LIMIT 1;

IF v_duplicated_transaction_id IS NOT NULL THEN
    SET v_billable = 0;
    SET v_reason_code =
        CONCAT('DUPLICATED BY ', v_duplicated_transaction_id);
ELSE
    SET v_billable = 1;
    SET v_reason_code = '';
END IF;

<=> 是 MySQL 的 NULL-safe equality operator。两边都是 NULL 时，它会返回真，因此不必为每个可能是 NULL 的字段手写以下条件：

(a = b) OR (a IS NULL AND b IS NULL)

是否应对所有字段使用 <=>，取决于业务上是否把两个 NULL 视为相同。

还要检查事务可见性

trigger 在触发它的事务内运行。它只能看到当前事务自己的修改，以及按照隔离级别对当前事务可见的其他已提交数据。

例如：

事务 A 在 00:35 插入第一条记录，但没有立即提交；
事务 B 在 00:38 插入第二条记录并执行 trigger；
事务 B 的 trigger 看不到事务 A 尚未提交的数据；
两条记录最终都完成提交，并且都被标记为 billable = 1。

因此，即使两条记录的 date_added 相差三分钟甚至几小时，也不能排除并发问题。需要调查：

第一条记录所在事务的提交时间；
是否存在长事务；
应用是否关闭了 autocommit；
是否在批量写入完成后才统一提交；
第二条记录的 statement snapshot 建立时，第一条记录是否已经提交；
日志表和计费表是否使用支持事务的存储引擎。

可以先检查配置和长事务：

SELECT @@autocommit, @@transaction_isolation;

SELECT *
FROM information_schema.innodb_trx
ORDER BY trx_started;

在 MySQL 5.7 的部分版本中，隔离级别变量名可能是：

SELECT @@tx_isolation;

`SQL_NO_CACHE` 对此没有帮助

SQL_NO_CACHE 只影响旧版 Query Cache 的结果缓存行为。它不会改变：

WHERE 条件；
NULL 比较规则；
用户变量的生命周期；
事务隔离和数据可见性；
未提交数据的处理方式。

trigger 中的查询也不会因为 Query Cache 而随机漏掉少量结果，因此不必继续沿缓存方向排查。

`LIMIT 1` 最好配合 `ORDER BY`

没有 ORDER BY 时使用 LIMIT 1，不会导致查询“找不到记录”。但如果存在多条重复记录，MySQL 可以返回其中任意一条，导致 reason_code 记录的 transaction ID 不稳定。

如果需要引用最早的重复记录，可以写成：

ORDER BY tl.date_added ASC, tl.transaction_id ASC
LIMIT 1

如果需要引用最近的一条，则写成：

ORDER BY tl.date_added DESC, tl.transaction_id DESC
LIMIT 1

`SELECT` 检查无法彻底解决并发重复

即使修正了时间条件和变量问题，“先查询，再决定是否计费”的方式仍然存在竞态：

两个事务几乎同时插入相同的业务键；
两边查询时都看不到对方尚未提交的数据；
两边都判断“没有重复”；
两边都写入 billable = 1。

如果业务必须严格保证“不重复”，最终约束应由唯一索引或原子写入承担，不能只依赖 trigger 中的普通 SELECT。

常见做法是建立一张去重登记表，并针对业务上的重复判定字段建立唯一索引：

CREATE TABLE billing_dedup (
    dedup_key BINARY(32) NOT NULL,
    first_transaction_id VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (dedup_key)
) ENGINE = InnoDB;

然后执行原子插入：

INSERT INTO billing_dedup (
    dedup_key,
    first_transaction_id,
    created_at
)
VALUES (
    UNHEX(SHA2(CONCAT_WS(
        '#',
        NEW.gc_id,
        NEW.function_name,
        NEW.report_options,
        NEW.source,
        NEW.result_code,
        COALESCE(NEW.i_person_id, '<NULL>'),
        NEW.billing_code
    ), 256)),
    NEW.transaction_id,
    NEW.date_added
);

如果时间窗口也是去重键的一部分，还应加入按照明确规则归一化后的窗口标识，例如小时、自然日或计费周期。哈希只用于压缩组合键。在使用哈希前，仍需明确定义字段归一化方式、字符集、大小写规则和 NULL 的表示方式。

MySQL 5.7 的 AFTER INSERT trigger 中 SELECT 偶发失效

结论

先核对实际执行的查询条件

时间条件可能写错了

不要在 trigger 中依赖 @user_variables

还要检查事务可见性

SQL_NO_CACHE 对此没有帮助

LIMIT 1 最好配合 ORDER BY

SELECT 检查无法彻底解决并发重复

推荐的定位顺序

不要在 trigger 中依赖 `@user_variables`

`SQL_NO_CACHE` 对此没有帮助

`LIMIT 1` 最好配合 `ORDER BY`

`SELECT` 检查无法彻底解决并发重复