必须用BEFORE INSERT触发器校验数据合法性,因为AFTER INSERT执行时数据已写入表中,SIGNAL仅报错无法回滚,非法数据必然落库;而BEFORE阶段可读写NEW、预校验并用SIGNAL终止语句。

必须用 BEFORE INSERT,不能用 AFTER INSERT —— 后者根本拦不住非法数据,INSERT 已执行完毕,SIGNAL 只报错不回滚。
为什么 AFTER INSERT 校验完全无效
常见错误现象:SIGNAL SQLSTATE '45000' 看似触发了报错,但查表发现非法数据已插入成功。这是因为 AFTER INSERT 执行时,INSERT 语句早已完成、事务已修改数据页;此时中断只会让客户端收到错误,主表记录照常落盘(尤其在 MyISAM 等非事务引擎下更明显)。
真正能拦截的只有 BEFORE INSERT:它在行写入前介入,可读取并修改 NEW,也可用 SIGNAL 终止整个语句。
-
AFTER中NEW是只读的,SET NEW.email = ...会直接报错:ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger -
BEFORE中NEW是可写临时行对象,SET操作直接影响最终入库值 - 哪怕只是想“提醒”而不用
SIGNAL,仅写SELECT 'xxx'也毫无作用——插入照常进行
IF + SIGNAL 的典型写法与易错点
校验逻辑全靠 IF 判断 NEW 字段,失败则 SIGNAL 中断。但 NULL、正则转义、时间精度这些细节一错就失效。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 字段允许
NULL时,必须显式判断:IF NEW.email IS NOT NULL AND NEW.email NOT REGEXP '...'—— 否则NULL REGEXP 'xxx'返回NULL(非TRUE/FALSE),条件不成立,SIGNAL不触发 - 邮箱正则末尾的
\.是双重转义:触发器体解析一次,正则引擎再解析一次,最终才匹配字面量点号;写成.会误匹配任意字符 - 日期比较用
CURDATE(),别用NOW()—— 后者含时分秒,NEW.birth_date > NOW()在当天下午可能意外放行未来时间 -
MESSAGE_TEXT长度不能超 128 字符,建议关键字段名前置,如'email 格式不合法',方便日志定位
哪些校验适合放触发器,哪些绝对不能放
触发器不是业务逻辑兜底工具,加错地方会拖慢批量插入、锁表、甚至破坏事务一致性。
- ✅ 适合的:
NEW.status NOT IN ('draft', 'paid', 'shipped')、NEW.price > 0、单字段正则/LIKE 校验、简单计算(如NEW.total = NEW.qty * NEW.unit_price) - ❌ 绝对禁止:
SELECT balance FROM accounts WHERE user_id = NEW.user_id(大表查余额易锁表)、JOIN关联查询(性能极差)、调用 HTTP 或读文件(触发器根本不支持) - ⚠️ 高风险但有时不得不做:查主表状态,如
SELECT is_active INTO v_active FROM users WHERE id = NEW.user_id—— 必须确保users.id有主键或唯一索引,否则全表扫描+锁行风险陡增
标准化处理(自动清洗)必须和校验一起用 BEFORE
校验失败用 SIGNAL,校验通过后想自动修正格式(比如去空格、转小写),只能靠 SET NEW.field = ...,且必须在 BEFORE INSERT 中完成。
- 例如:
SET NEW.email = LOWER(TRIM(NEW.email))—— 但要注意字段定义:如果email是NOT NULL且无默认值,而你把它设成空字符串,后续约束检查仍会失败 - 清理手机号中的非数字字符需确认 MySQL 版本:
REGEXP_REPLACE()仅在 8.0+ 支持,低版本得用嵌套REPLACE或提前在应用层处理 - 多个校验项之间必须用独立的
IF ... END IF;分开,不要堆在一个IF里 —— MySQL 遇到第一个SIGNAL就终止,后续逻辑不执行
最常被忽略的是 NULL 判断和双重转义,这两处出错会导致校验形同虚设;另外,哪怕只查一次主表状态,若没索引支撑,在高并发批量插入时极易演变成锁表现象。










