如何利用SQL触发器实现在INSERT数据时自动同步到审计表？

必须用AFTER INSERT，因BEFORE阶段自增主键未生成，NEW.id为NULL，导致审计表插入失败；且审计表字段顺序、类型、字符集须与源表严格一致，否则会错位、截断或报错。

能，但必须用 AFTER INSERT，且审计表字段顺序、类型、字符集要和源表严格一致；否则写入会错位、截断甚至报错。

为什么不能用 BEFORE INSERT 同步到审计表？

因为 BEFORE INSERT 时主表记录尚未落盘，自增主键（如 id）可能还没生成。你往审计表里插 NEW.id，结果是 NULL 或默认值，导致数据不一致。

• MySQL 中 NEW.id 在 BEFORE 阶段对自增列不可靠，除非显式赋值
• AFTER INSERT 才能确保 NEW 包含完整、已确认的行数据
• PostgreSQL 虽允许 BEFORE 访问 NEW，但同步场景仍推荐 AFTER，避免逻辑混淆

INSERT INTO audit SELECT NEW.* 为什么经常失败？

表面简洁，实则埋雷：一旦审计表比源表多字段（比如加了 op_type、op_time），或字段顺序不一致，这条语句立刻报错 Column count doesn't match value count。

• 必须显式列出目标列名：INSERT INTO audit (id, name, op_type, op_time) VALUES (NEW.id, NEW.name, 'INSERT', CURRENT_TIMESTAMP)
• 字符集/排序规则不一致也会触发 Illegal mix of collations，尤其当源表用 utf8mb4_unicode_ci、审计表用 utf8mb4_general_ci 时
• 如果审计表有 NOT NULL 字段但没在 VALUES 中提供值，直接插入失败

如何安全透传操作人 ID，而不是数据库连接用户？

CURRENT_USER() 返回的是 [email protected].% 这类连接账号，对业务审计毫无意义。真实操作人必须由应用层带入。

• 应用执行 SQL 前先设会话变量：SET @current_app_user = 12345;
• 触发器中读取：op_user = @current_app_user（注意：不能在 BEFORE 里用 := 赋值给 NEW，除非审计表字段与源表完全一致）
• 千万别用 USER() 或 SYSTEM_USER()，它们反映的是 TCP 连接身份，不是业务主体
• 更健壮的做法是应用在 SQL 注释里埋信息（如 INSERT /* user_id=12345 */ INTO orders...），但触发器无法解析——得靠 ProxySQL 或应用日志兜底

同步量大时，触发器会拖慢主表写入吗？

会，而且非常明显。触发器是同步阻塞执行的，主表 INSERT 必须等审计写完才返回。一个慢查询、一个锁等待、一个跨库写入，都会卡住业务。

• 审计表务必禁用所有非必要索引，只保留按时间范围查用的复合索引，例如 (op_time, table_name)
• 避免在触发器里调用 UUID()、JSON_OBJECT() 等函数，MySQL 5.7 不支持，8.0 支持但性能损耗高
• 日均百万级审计量，建议放弃触发器，改用应用层双写，或解析 binlog 异步补全

真正麻烦的不是语法，而是字段一致性、时区对齐、操作人透传这三个点——漏掉任何一个，日志就不可信；而性能问题往往上线后才暴露，那时再切架构代价已经很大。