MySQL 5.7 AFTER INSERT触发器中SELECT语句间歇性失效问题

问题背景 #

我在两个不同的schema下维护两张表：

• log_mbs.transaction_log：记录客户调用API的使用日志
• 计费表：标记交易是否可计费

需求逻辑：当新插入一条API调用日志时，检查是否存在匹配参数的已有记录，若存在则将该交易标记为不可计费。

触发器核心代码 #

触发器中负责重复检测的SQL片段如下：

SELECT tl_duplicated.transaction_id 
INTO @duplicated_transaction_id
FROM log_mbs.transaction_log AS tl_duplicated
WHERE tl_duplicated.gc_id = NEW.gc_id
  AND tl_duplicated.function_name = NEW.function_name
  AND tl_duplicated.report_options = NEW.report_options
  AND tl_duplicated.source = NEW.source
  AND tl_duplicated.result_code = NEW.result_code
  AND ((tl_duplicated.i_person_id = NEW.i_person_id) OR (tl_duplicated.i_person_id IS NULL AND NEW.i_person_id IS NULL))
  AND tl_duplicated.billing_code = NEW.billing_code 
  AND tl_duplicated.date_added <= NEW.date_added
  AND tl_duplicated.date_added >= @billing_start_interval
  AND NOT tl_duplicated.transaction_id = NEW.transaction_id
  LIMIT 1;

IF @duplicated_transaction_id IS NOT NULL THEN
    SET @billable = 0;
    SET @reason_code = CONCAT('DUPLICATED BY ',@duplicated_transaction_id);
ELSE 
    SET @billable = 1;
    SET @reason_code = '';
END IF;

异常现象 #

在380万条记录中，约0.5%（1.5万条）的交易本该被检测为重复，但触发器中的SELECT语句未返回任何结果。

• 排除并发插入问题：异常也出现在间隔数小时的交易中
• 排除缓存问题：已尝试添加SQL_NO_CACHE关键字，无效

问题示例 #

以下是典型的异常案例：

匹配字段验证查询 #

SELECT  
    tl_1.function_name = tl_2.function_name as 'match function_name',
    tl_1.report_options = tl_2.report_options as 'match report_options',   
    tl_1.source = tl_2.source as 'match source',   
    tl_1.result_code = tl_2.result_code as 'match result_code',   
    tl_1.i_person_id = tl_2.i_person_id as 'match i_person_id',  
    tl_1.billing_code = tl_2.billing_code as 'match billing_code',   
    tl_1.date_added, tl_2.date_added 
FROM 
    log_mbs.transaction_log as tl_1 
INNER JOIN 
    log_mbs.transaction_log as tl_2 
WHERE 
    tl_1.transaction_id = '93221R499739' 
    AND tl_2.transaction_id = '93181R499869';

查询结果 #

+---------------------+----------------------+--------------+-------------------+-------------------+--------------------+---------------------+---------------------+
| match function_name | match report_options | match source | match result_code | match i_person_id | match billing_code | date_added          | date_added          |
+---------------------+----------------------+--------------+-------------------+-------------------+--------------------+---------------------+---------------------+
|                   1 |                    1 |            1 |                 1 |                 1 |                  1 | 2023-06-10 00:35:39 | 2023-06-10 00:38:54 |
+---------------------+----------------------+--------------+-------------------+-------------------+--------------------+---------------------+---------------------+

计费表结果 #

第二条交易比第一条晚3分钟插入，但未被标记为不可计费：

+----------------+----------+-------------+---------------------+
| transaction_id | billable | reason_code | date_added          |
+----------------+----------+-------------+---------------------+
| 93221R499739   |        1 |             | 2023-06-10 00:35:39 |
| 93181R499869   |        1 |             | 2023-06-10 00:38:54 |
+----------------+----------+-------------+---------------------+

可能的原因及验证方案 #

1. @billing_start_interval变量取值异常 #

• 排查点：触发器执行时该变量的取值是否正确，是否在某些场景下大于历史交易的date_added，导致本该匹配的记录被过滤
• 验证：针对异常交易，手动代入NEW字段值和当时的@billing_start_interval值，执行触发器中的SELECT语句，看是否能返回结果

2. 其他字段的NULL值未处理 #

• 排查点：除i_person_id外，report_options、source等字段可能存在NULL值，MySQL中NULL = NULL返回NULL，不会被WHERE条件匹配
• 修复建议：为所有可能为NULL的字段添加NULL匹配逻辑，例如：

  AND ((tl_duplicated.report_options = NEW.report_options) OR (tl_duplicated.report_options IS NULL AND NEW.report_options IS NULL))
  

3. 字符集/排序规则差异 #

• 排查点：若表的字符集排序规则非二进制（如utf8_general_ci），字符串比较可能存在大小写忽略、空格自动截断等行为，导致触发器执行时的比较逻辑与事后查询不一致
• 验证：改用二进制比较，例如tl_duplicated.function_name = BINARY NEW.function_name，看是否能解决问题

4. 执行计划偏差（索引问题） #

• 排查点：若未创建匹配字段的联合索引，MySQL可能选择不合适的执行计划（如仅用date_added索引），导致漏查记录
• 修复建议：创建联合索引强制优化器使用正确的查询路径：

  CREATE INDEX idx_duplicate_check ON log_mbs.transaction_log(
      gc_id, function_name, report_options, source, result_code, 
      i_person_id, billing_code, date_added
  );
  

5. 字段类型隐式转换 #

• 排查点：若字段类型不匹配（如result_code是字符串但存储数字），可能出现隐式转换导致比较结果异常；CHAR类型字段的空格填充也可能导致'123 '和'123'比较不相等
• 验证：检查字段定义，统一类型或在比较时显式转换

6. 事务隔离级别影响 #

• 排查点：若触发器在事务中执行，低隔离级别可能导致不可重复读，但间隔数小时的交易出现问题的可能性极低，可作为最后排查项

内容的提问来源于stack exchange，提问作者João Luiz dos Reis Santos