在MySQL中如何编写BEFORE INSERT触发器校验字段有效性-mysql教程-PHP中文网

必须用before insert，after insert校验完全无效；因后者数据已落盘，signal无法回滚主表变更，脏数据必然入库，而before在写入前执行，可signal中断或set new修正，真正拦截非法数据。

在mysql中如何编写before insert触发器校验字段有效性

必须用 BEFORE INSERT，AFTER INSERT 校验完全无效

BEFORE INSERT 是唯一能真正拦截非法数据的时机。AFTER INSERT 中数据已写入磁盘，此时 SIGNAL SQLSTATE '45000' 只会让语句报错，但主表记录不会回滚——查表就能发现脏数据已存在。尤其在 MyISAM 表或非严格事务场景下，这个问题更隐蔽。MySQL 不支持语句级触发器，FOR EACH ROW 是强制行为，意味着每行都独立走一遍 BEFORE 逻辑，天然支持逐行校验。

校验逻辑必须显式处理 NULL 和类型隐式转换

字段允许 NULL 时，NEW.email NOT REGEXP '...' 在 NEW.email IS NULL 时返回 NULL（非 TRUE/FALSE），IF 判断直接跳过，校验失效。正确写法是：

IF NEW.email IS NOT NULL AND NEW.email NOT LIKE '%_@__%.__%' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'email 格式不合法';
END IF;

优先用 LIKE 替代 REGEXP：简单邮箱模式匹配快、可索引友好，且对 NULL 更可控
手机号正则末尾的点号要写成 \.：MySQL 解析一次、正则引擎再解析一次，单反斜杠会被吃掉
日期比较用 CURDATE()，别用 NOW()：后者带时分秒，可能因精度导致 NEW.expire_date 误判过期

标准化赋值和校验中断必须分开写，顺序不能颠倒

想自动清洗再校验，得先 SET NEW.email = LOWER(TRIM(NEW.email))，再判断清洗后是否合法。如果反过来——先校验原始值、再清洗——那清洗就失去意义；如果只清洗不校验，非法格式照样入库。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

SET NEW.created_at = NOW() 这类默认值填充安全，但注意字段约束：若定义为 NOT NULL，又在触发器里设成空字符串，会触发 NOT NULL 约束报错，而非你写的 SIGNAL
多个校验项必须用独立的 IF ... END IF; 块，不要合并到一个 IF 里——MySQL 遇到第一个 SIGNAL 就终止整个触发器，后续逻辑不执行
批量插入如 INSERT INTO t VALUES (1,'a'),(2,'b') 会触发两次 BEFORE，每次 NEW 只对应当前行，无法跨行去重或聚合

哪些校验能放触发器，哪些绝对不该碰

触发器不是业务逻辑容器，加错地方会拖慢导入、锁表甚至破坏事务。适合的只有轻量、单行、无副作用的操作：

✅ 允许的：NEW.status NOT IN ('draft', 'paid', 'shipped')、NEW.price > 0、NEW.total = NEW.qty * NEW.unit_price
❌ 绝对禁止：SELECT balance FROM accounts WHERE user_id = NEW.user_id（大表查余额易锁表）、JOIN 查询、调用外部 HTTP 或读文件（MySQL 触发器根本不支持）
⚠️ 高风险但有时需做：查主表状态如 SELECT is_active INTO v_active FROM users WHERE id = NEW.user_id，前提是 users.id 有主键或唯一索引，且查询结果集极小

真正难处理的是“本批内重复”这类需求——触发器看不到同一批其他待插入行，唯一可靠方案是建唯一索引 + 用 INSERT IGNORE 或应用层预检。