如何通过SQL触发器实现自动化的数据质量校验

能用触发器做单行字段级自动化校验，必须用BEFORE INSERT + SIGNAL SQLSTATE '45000'，仅限字符串长度、邮箱格式、数值范围等可单行判定的规则，无法实现全表统计、跨行分析或状态追踪。

如何通过sql触发器实现自动化的数据质量校验_拦截非法格式

不能用触发器做“自动化数据质量校验”——它压根看不到全表、算不出指标、更没法实时评分。所谓“拦截非法格式”，仅限单行字段级校验，且必须用 BEFORE INSERT + SIGNAL，否则就是假拦截。

为什么 BEFORE INSERT 是唯一能真正拦截的时机

数据写入流程中，BEFORE INSERT 是数据库引擎在解析 SQL、分配行锁、生成物理记录前的最后一道可控关卡。此时 NEW 已就位，但数据尚未落盘、事务未提交。一旦触发器内执行 SIGNAL SQLSTATE '45000'，整个语句立即终止，应用层收到明确错误，不会出现“返回成功但没写进去”或“写进去了却非法”的情况。

常见错误现象：

AFTER INSERT 里抛错：数据已落盘，回滚对调用方不可见，日志和前端状态割裂
用 SELECT ... INTO 模拟异常：MySQL 不报错，INSERT 照常成功，校验失效
漏写 SIGNAL：哪怕逻辑判断为真，也毫无拦截效果

哪些“非法格式”能用触发器拦，哪些根本拦不了

触发器只能访问当前行的 NEW 值，无法聚合、无法跨行统计。能拦的，仅限单行内可判定的格式类规则：

字符串长度超限：IF CHAR_LENGTH(NEW.phone) != 11 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '手机号必须为11位';
邮箱格式粗筛：IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN ...
数值范围与非空组合：IF NEW.age IS NULL OR NEW.age 150 THEN ...

以下场景触发器完全无能为力：

“email 字段完整度低于 95%” —— 需查全表，触发器内查会死锁或报 Can't update table 'xxx' in stored function/trigger
“同一用户连续 3 次插入失败” —— 无状态、无历史上下文
“该手机号在 users 表中已存在但 status = 'deleted'” —— 关联查询若无覆盖索引，高并发下极易锁表

MySQL 和 PostgreSQL 触发器写法的关键差异

同是 BEFORE INSERT，两者的语法约束和容错点完全不同：

MySQL：直接用 NEW.field，支持 REGEXP、CHAR_LENGTH()，8.0.16+ 才有 SIGNAL；旧版本只能靠 SELECT 1/0 强制中断，但错误信息不可控
PostgreSQL：不能在子查询中把 NEW 当表用，SELECT id FROM NEW 是语法错误；必须先 DECLARE 变量，再 SELECT ... INTO 赋值；函数结尾必须写 RETURN NEW，漏掉会导致静默丢数据
两者都禁止在触发器里查当前表（如 SELECT COUNT(*) FROM orders WHERE user_id = NEW.user_id），MySQL 报错，PostgreSQL 在某些隔离级别下可能死锁

容易被忽略的三个硬性限制

这些不是“建议”，而是数据库引擎强制执行的边界：

NOT NULL、CHECK 约束永远优先于触发器执行。如果字段定义为 NOT NULL，但你在 BEFORE INSERT 里试图给 NEW.field 赋 NULL，数据库会在触发器运行前就报错，根本进不到你的逻辑里
触发器无法捕获客户端传入的隐式类型转换结果。比如 INSERT INTO t VALUES ('abc') 插入到 INT 字段，MySQL 先转成 0 再进触发器，你看到的已是转换后值，无法还原原始字符串
所有校验逻辑共享主 SQL 的事务上下文。触发器里一次慢查询，会拖慢整条 INSERT；一个未处理的异常，会让整个批量插入（含多行）全部回滚