MySQL decimal列插入数据报Out of Range错误,数值未超范围仍触发
问题场景
- 已按业务需求完成数据表结构创建
- 执行固定参数的插入语句时触发报错,表结构截图、具体SQL语句的执行界面可参考附件
- 实际测试验证的现象:
- longitude字段传入值小于100时,同格式的decimal类型参数都能正常写入
- 尝试传入
-161.9722021时,语句直接执行失败 - 自查确认传入的是纯decimal类型值,不存在字符串隐式转换问题,初步判断该数值没有超出字段定义的取值范围
- 检索了网上的同类报错案例,基本都是实际传入值超出字段精度、长度定义导致的报错,但反复核对后找不到匹配的原因,无法定位问题根因。
排查与解决思路
遇到这类decimal字段“小值能插、特定值报错、看着没超范围”的问题,可以按下面的顺序逐项核对,基本都能定位根因:
- 重新逐字核对字段的精度定义,别只看数值本身的大小
所有数据库的decimal类型都遵循
decimal(M,D)的定义规则:M是总有效数字位数,D是小数部分占的位数,整数部分的可用位数就是M-D。很多人建表的时候只记得总长度,忘了整数部分的计算逻辑,更忽略了负号会占用1位长度的实现细节——比如定义的字段是decimal(8,5),本来整数部分应该有3位长度,但是负号占1位的话,负数值的整数部分就只能存2位,这时候-161这种带3位整数的负值,自然就存不进去,和“小于100的值能过、超100的负值报错”的现象完全匹配。 - 核对当前会话的SQL模式配置
如果数据库开了严格模式,超出范围的decimal值会直接抛错;关了严格模式的话,会自动把值截断后写入。直接执行
SELECT @@SESSION.sql_mode;就能看到当前会话的模式,对比下插小值成功、插-161报错时的模式是不是不一样,排除配置变更导致的拦截。 - 排查表上的约束、触发器拦截规则
这类自定义规则触发的报错,提示信息和“字段长度不足”的报错高度相似,很容易混淆。要检查下表上是不是配了CHECK约束、写入触发器,比如业务上要求longitude只能存正值、或者整数部分不能超过2位的规则,这类拦截和字段本身的类型没关系,很容易被当成字段问题排查半天。可以查information_schema里的约束表,或者执行
SHOW TRIGGERS语句确认有没有这类规则。 - 核对当前数据库版本的已知存储逻辑
不少老版本的数据库在计算decimal可存储范围的时候,会有负号占长度的实现偏差,实际能存的负值比理论范围小一个量级,遇到这种情况直接把字段的总长度加1,给负号留够存储位就能解决。
内容的提问来源于stack exchange,提问作者snapshot

