存储过程之format函数使用经验

存储过程使用到了format函数，调用的时候类似于：

set result = IFNULL(format((arg_x/arg_y),2),0);

之前一直是没问题的,但今天却发生了错误，日志显示“Error Code: 1265 Data truncated for column ‘result’ at row 9986”。

好吧，既然发生了错误，那么就动手来排查一下原因以及找出解决方法吧。

第一步，检查存储过程。

一直以来该存储过程都是定时运行的，并且每天查看都能看到存储过程执行成功和转储后的数据内容。在仔细检查了存储过程之后发现并没有显著存在的问题。

第二步，单独运行存储过程。

既然存储过程代码没有问题，而且今天之前的运行全部正确，那么就单独运行一下存储过程，带入今天的日期作为参数执行。执行的过程中数据库果然报错了，依然提示“Data truncated”的错误。那这是怎么回事呢？我们继续往下查找原因。

第三步，确定问题发生位置。

根据错误提示，找到了存储过程中发生错误的地方，也就是本文开头的那句调用语句上。从字面上看来，并没有什么明显错误，那么一定是运行过程中生成的动态数据有问题。

第四步，单独运行format函数。

“Data truncated”错误从字面上来看就是数据截取的时候有错误，导致不能正确识别数据，那么单独运行format函数试试。
执行

select format(12.3456,2);

结果显示12.35。
再执行

select format(123.456,2);

结果显示123.46。
没有错误啊，那是怎么回事？

第五步，查找format函数文档。

既然运行没有问题，那应该是别的原因，先查找format函数的文档吧，根据文档：“Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.”可以发现，整数部分超过三位的时候，会以逗号分割，并且返回的结果是字符串类型的。那么问题应该就是出现在这里了，试验一番，执行

select format(1234.5678,2);

结果显示1,234.57，果然是分割了呀。而本文开头被赋值的“result”是float类型的，所以自然就会出错了。

第六步，修改代码。

好吧，找到问题原因了，那么就考虑换方式来格式化数字吧。查找文档，发现类型转换函数convert()应该是比较适合的。接下来，试验一番，执行

select convert(12345.6789,decimal(8,2));

结果显示12345.68。

嗯，太好了，这样一来就行了。由于项目涉及到的数据位数不大，所以decimal函数只需截取到8位并且其中2位作为小数就行了。

至此，问题解决了，那么回到开头的语句，应该如此修改：

set result = IFNULL(convert((arg_x/arg_y),decimal(8,2)),0);

就可以了。