如何防止SQL非法金额输入_利用触发器实现精确度校准-SQL-PHP中文网

应使用before insert触发器配合cast(new.amount as char)转字符串，再用locate和length校验小数位数，超限时用signal抛出明确错误；禁用after触发器、避免函数调用，并注意批量插入性能。

如何防止sql非法金额输入_利用触发器实现精确度校准

关键不是拦住“非法字符”，而是拦住“合法数字但精度超标”的输入。比如 DECIMAL(10,2) 字段，存 123.456 看似是数字，但小数位超了，MySQL 会四舍五入成 123.46 —— 这不是你想要的“校准”，是静默篡改。

正确做法是在触发器里用字符串方式检查原始值：先转成 CHAR，再用 LOCATE 找小数点位置，结合 LENGTH 算小数位数。不能依赖 ROUND() 或直接比较数值，因为浮点转换可能引入误差。

NEW.amount 是 DECIMAL 类型，但触发器中要用 CAST(NEW.amount AS CHAR) 获取原始输入形态（注意：实际 INSERT 值可能已被 MySQL 截断，所以更稳妥的是在 BEFORE INSERT 触发器中检查 NEW.amount 的字符串表示）
如果业务允许前端传字符串（如 JSON 中的 "123.456"），那必须在触发器里先用 TRIM() 去空格，再验证格式，否则 "123.45 " 会被 CAST 成 "123.45" 躲过检查
别用 INSTR() 判断小数点——它在无小数点时返回 0，容易和位置 0 混淆；用 LOCATE('.', value) 更明确

MySQL 触发器不能直接用 RAISE（那是 PostgreSQL 的），得靠制造一个必然失败的操作来中断流程，比如向一个不存在的表插入数据。但这样报错信息是 Table 'xxx.nonexistent' doesn't exist，运维根本看不懂。

更实用的做法是调用一个自定义函数，让它返回 NULL，再在触发器里用 IF ... THEN INSERT INTO nonexistent_table ... —— 不如直接用 SIGNAL（5.5+ 支持）。

必须用 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount precision exceeds 2 decimal places'，SQLSTATE 选 '45000' 表示通用用户错误
不要把校验逻辑写在 AFTER INSERT 触发器里——那时数据已入库，SIGNAL 只能报错，无法回滚（除非整个事务失败，但触发器本身不开启事务）
如果表有多个金额字段（如 price, tax, discount），每个都得单独校验，SIGNAL 信息里要带上字段名，例如 CONCAT('Field ', '''price''', ' exceeds precision')

很多人只测单条 INSERT，一上线就发现批量导入变慢十倍。原因很简单：触发器对每一行都执行一次校验逻辑，而 LOAD DATA INFILE 或 INSERT ... VALUES (...), (...), (...) 有多少行，就触发多少次。

这不是性能优化问题，是设计盲区：如果你的 ETL 流程依赖高吞吐写入，又强制所有路径走触发器，等于给管道加了个每秒限流阀。

对定时任务或后台导入，建议绕过触发器：用 SET SESSION sql_mode = '' 临时关闭严格模式（不推荐），或更安全地——给导入账号分配一个不带触发器权限的专用账户，并在应用层做精度预检
触发器里避免调用存储函数或查询其他表，哪怕只是查个配置表，都会让批量操作从 O(n) 变成 O(n×m)，尤其当配置表没索引时
如果必须用触发器保底，至少把字符串解析逻辑写成内联表达式，别封装成存储函数——函数调用开销在批量场景下会被放大

MySQL 8.0.16+ 支持 CHECK 约束，看起来更干净，但对精度校验它有个硬伤：CHECK 表达式里不能用确定性函数以外的函数，而 CAST(... AS CHAR) 在某些版本被认为“非确定性”，会导致建表失败。

更麻烦的是，CHECK 约束在分区表、某些复制拓扑或低版本从库上会被忽略，而触发器只要存在就会执行——可控性更高。

CHECK (LENGTH(SUBSTRING_INDEX(CAST(amount AS CHAR), '.', -1)) 在 MySQL 8.0.29 上可能报错 <code>Function or expression 'cast' cannot be used in a check constraint
即使 CHECK 可用，它也无法区分“用户输入 123.456”和“应用层计算后写入 123.456”，而触发器可以结合 USER() 或上下文标记做更细粒度控制
线上已有表加 CHECK 约束会锁表（ALGORITHM=COPY），而触发器可热加载，风险更低

精度校准这件事，本质是在数据入口处做“格式守门员”，而不是在类型系统里赌它永远可靠。越靠近写入点拦截，越不容易被中间环节绕过，也越难被误认为“只是个约束”。