写SQL的人没有一天能绕开数值处理。上一篇文章把字符串函数过了一遍这篇轮到数值函数。你别觉得数值函数就是加减乘除加个四舍五入真到用的时候round、truncate、ceil、floor这几个函数到底谁是谁金额对不上账、评分统计偏了0.1、随机抽样抽出一堆重复数据这些问题十有八九出在数值函数的细节上。这篇把MySQL里常见的数值函数拆开讲清楚重点说清楚每个函数背后的计算逻辑以及实际写SQL时怎么选、怎么避坑。适合刚接触MySQL的开发者也适合写了好几年SQL但没认真抠过数值边界的朋友看完能直接照着手里的业务场景用。1. 数值函数到底在解决什么问题1.1 先看两个实际场景你就知道数值函数有多重要第一个场景是订单金额处理。某电商项目的订单表里每笔订单有商品单价和数量需要计算实付金额并保留两位小数。很多开发者第一反应用round(price * quantity, 2)但跑批后发现有笔订单折扣价是9.35数量是3算出来是28.05没错可另一笔订单单价5.10数量是0.47乘出来是2.397round之后变成2.40。问题来了财务要求按分结算2.397到底该进到2.40还是直接截断成2.39不同业务规则需要不同函数这个选择直接影响真金白银的差异。第二个场景是评分统计。某学习类App要给课程评分算均值比如课程A的评分明细是4.5、4.5、4.8、4.8、4.9平均值算出来之后要展示成一位小数。有人直接round(avg(score), 1)有人用cast(avg(score) as decimal(10,1))还有人用format(avg(score), 1)。看起来结果都是4.7但底层返回类型完全不一样。界面上的展示、后续的排序、再参与计算的精度都会因为选错函数而出问题。这两个场景很典型数值函数不只是“把数变好看”它决定了对数据的舍入策略、精度控制、参与后续计算时的数据类型。搞清楚数值函数本质上是在搞清楚业务希望你怎么对待一个带小数位的数字。1.2 数值函数在MySQL里的分类与定位MySQL里的数值函数大致分成四类舍入与截断类round、truncate、ceil、ceiling、floor基础数学运算类abs、sign、mod、pow、power、sqrt、exp、ln、log、log2、log10随机数类rand三角函数与进制转换类sin、cos、tan、asin、acos、atan、atan2、cot、degrees、radians以及bin、oct、hex、conv这个系列我不展开讲聚合函数里的sum、avg、max、min那些是聚合场景跟标量函数的使用逻辑不太一样。这篇聊的是一行数据进去、一个数值出来的标量函数它们的作用是对单个数值做变换或者对某个表达式的结果做二次计算。有个很容易被忽略的点数值函数里不少函数返回的是DECIMAL或DOUBLE类型这决定了结果在后续join、union、排序、比较时的行为。很多人只关注“算出来是多少”没关注“算出来是什么类型”这是很多诡异bug的来源后面专题讲。2. 舍入与截断最值钱的四个函数2.1 ROUND的四舍五入和那个容易翻车的“四舍六入五成双”round(x)直接对x取整round(x, d)保留d位小数。MySQL的round遵循四舍五入规则如果被保留位数的下一位是5则向前进位。重点是MySQL在某些情况下对正好处于.5的边界值会出现“五成双”现象。这不是bug而是底层浮点数的二进制表示误差。比如round(2.5)结果是3但round(2.565, 2)可能得到2.56而不是2.57因为2.565在二进制里实际存储为2.5649999999999999。如果用DECIMAL类型存储的2.565round(2.565, 2)就能正确得到2.57。我踩过这个坑。某账单系统的金额字段用的是DECIMAL(10, 2)后来改成从第三方接口读DOUBLE类型结果日志里一堆舍入差异。排查到最后问题就出在存储类型上。实操建议金额计算尽量用DECIMAL不要用FLOAT或DOUBLE如果需要绝对精确的四舍五入先把字段转成DECIMAL再roundround的第二个参数d可以省略取整也可以传负数比如round(1234, -2)得到1200round(1234, -3)得到1000这是对整数位做舍入做粗略统计时有用2.2 TRUNCATE直接截断不跟你商量truncate(x, d)和round长得像但行为完全不同它把小数点后d位之后的数字直接砍掉不四舍五入。truncate(2.567, 2)结果是2.56truncate(2.999, 1)结果是2.9。业务里什么时候必须用truncate银行存款利息计算、手续费分账、部分业务统计里“只舍不入”规则。比如某支付通道的分润规则是“按交易金额的0.6%计算分润金额保留两位小数不足一分的部分不做四舍五入直接舍去”。用round就会出现多给的情况用truncate才对。truncate也支持负数位truncate(1234, -2)结果是1200truncate(1299, -2)也是1200它纯截断不是四舍五入。注意truncate返回类型和round有细微差别前者返回DECIMAL后者在某些参数下返回DECIMAL但处理浮点输入时会有类型提升。建议在写查询前先明确你想要什么类型别把结果直接塞进VARCHAR字段再做比较。2.3 CEIL与FLOOR向上取整和向下取整的边界场景ceil(x)返回大于等于x的最小整数floor(x)返回小于等于x的最大整数。注意MySQL里还有一个ceiling跟ceil完全等价别以为一个是大写一个变小写了。边界场景很值得琢磨ceil(5.0)结果是5不是6因为5.0本身就是整数ceil(-5.1)结果是-5因为-5大于-5.1floor(-5.1)结果是-6ceil(0)是0用floor分页的场景很常见比如要把一批数据按每500条分一个桶需要给每条数据算桶号常见写法是floor((rownum - 1) / 500) 1。这里不用round因为分桶必须要严格的向下边界。向上取整的典型场景是算最少需要几辆车、几个盒子。比如有47件货每箱最多装12件需要ceil(47 / 12)得到4箱。有人写floor(47 / 12) 1这种方法当47恰好能被12整除时结果会错会多算1箱。所以能整除的场景老老实实用ceil。MySQL里5.0 / 2返回2.5000是DECIMAL注意它会保留小数点后4位跟整数除法的预期不一样。涉及取整除法时建议显式写ceil(a / b)不要依赖隐式转换。2.4 实操案例金额按分处理与评分统计案例一订单金额分摊。某订单总金额100元有3个商品需要把100元按比例分摊到每个商品上且分摊金额之和必须等于总金额不能因为四舍五入多一分或少一分。错误做法每个商品round(100 * item_price / total_price, 2)三个四舍五入后的数相加大概率不等于100.00。正确做法是最后一个商品用倒挤法SELECT item_name, CASE WHEN item_id (SELECT MAX(item_id) FROM order_items WHERE order_id 100) THEN 100 - SUM(rounded_amount) OVER (ORDER BY item_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ELSE ROUND(100 * item_price / total_price, 2) END AS allocated_amount FROM order_items;这个写法里前几个商品按四舍五入分摊最后一个商品用100减去前面分摊金额之和来兜底保证总数不差。案例二课程评分展示。评分平均分要求保留1位小数直接round(avg(score), 1)没问题。但如果你后续要按这个评分排序建议在ORDER BY里再算一次不要依赖界面展示已经格式化好的值因为如果界面层用format返回了字符串排序会变成字典序9.5会排在8.9前面。3. 基础数学运算ABS、SIGN、MOD、POW、SQRT3.1 ABS与SIGN这两个函数往往被忽略abs(x)取绝对值这个不复杂但它常被用在“两条数据差异多少”的统计里。比如对比两个时间戳字段的差值abs(timestampdiff(second, start_time, end_time))避免出现负数。sign(x)返回x的符号正数返回1负数返回-1零返回0。这个函数在做趋势判断时非常方便。比如统计本月销售额与上月销售额的环比可以用sign(current_sales - last_sales)直接看是增长、下降还是持平避免写一堆case when。某运单系统里有一段逻辑判断车辆GPS经纬度偏移是否在允许范围内需要算偏移方向sign返回的结果直接作为方向调整系数配合绝对值计算使用逻辑很清晰。3.2 MOD取模的负数场景容易跟业务预期不一致mod(a, b)返回a除以b的余数也可以用a % b两者在MySQL里等价。不过要注意MySQL里对负数的处理mod(-7, 3)结果是2而不是-1。这是由“余数必须与被除数符号无关、结果非负”的数学约定决定的。但有的语言里比如C或JavaScript的%-7 % 3结果是-1。如果你是从其他语言刚转过来写SQL这一点会坑到你怎么都想不明白。实际业务中取模最常见的是分表、分桶、奇偶判断。比如订单号取模分表order_id % 16把订单散到16张表。这里你只要保证order_id是正数就没问题。但如果order_id可能是负数比如某些补偿单号带负号就会出现负数桶号导致查询走错表。奇偶行筛选WHERE id % 2 1取奇数行WHERE id % 2 0取偶数行。3.3 POW、SQRT、EXP、LOG谁说用不到pow(a, b)和power(a, b)返回a的b次方。sqrt(x)返回平方根。exp(x)返回e的x次方。ln(x)返回以e为底的自然对数log(b, x)返回以b为底x的对数log10(x)返回以10为底的对数log2(x)返回以2为底的对数。这些函数在纯业务SQL里确实不常用但在某些数据清洗、特征计算场景里非常管用。比如某推荐系统项目里要给用户行为次数做特征缩放直接用log10(cnt 1)把几十万的大数值压到个位数模型训练效果明显好于原始值。这里1是为了避免log10(0)报错或返回NULL。功率计算场景某IOT项目计算设备功耗电流I的平方乘以电阻R直接pow(current, 2) * resistance比起先算current * current语义更清晰。有一个坑ln(0)会返回NULL并产生警告sqrt(-1)也是NULL不是报错。在数据清洗时建议先用WHERE过滤掉非法值或者在表达式外面套一层ifnull兜底。4. RAND随机数与抽样4.1 RAND()与RAND(N)的区别rand()返回0到1之间的随机浮点数包含0但不包含1。rand(n)是带种子参数的随机数用相同种子的情况下每次调用返回的随机序列是一致的。这一点在做可复现的A/B测试分组时很有用。举个例子某活动运营要给用户随机分成两组做红包测试要求每次跑SQL时分组结果一致这样才能复现同一批用户的分组。可以在分组字段上用rand(user_id)而不是rand()这样同一个user_id永远返回同一个伪随机数用户分组稳定。4.2 生成指定范围随机整数的方法最常见的是FLOOR(RAND() * (max - min 1)) min。比如生成1到100之间的随机整数写floor(rand() * 100) 1。如果想生成更多位数的验证码可以用LPAD(FLOOR(RAND() * 1000000), 6, 0)生成6位数字。LPAD是字符串函数这里借用了上一篇文章的内容可见函数是组合使用的单靠数值函数做不了完整的格式化。某抽奖项目里中奖名单要随机抽取100个用户直接ORDER BY RAND() LIMIT 100。这个写法在小表上没问题但在千万级用户表上ORDER BY RAND()会给每一行都生成随机数再排序全表扫描加文件排序性能非常差。更优的做法是先SELECT COUNT(*)拿到总行数然后用随机偏移量去取SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users)) 1) LIMIT 100;这种方式只做一次随机数计算加索引查询比ORDER BY RAND()快一个数量级。但要注意如果id有空洞最终条数可能不足100通常可以配合LIMIT多取一些再补齐或者接受少量偏差。4.3 RAND在WHERE条件里每行执行一次这个坑很隐蔽WHERE id FLOOR(RAND() * 1000)这类写法很多人以为只执行一次随机数实际上MySQL会对每一行都重新计算RAND。也就是说每一行的返回条件都不一样最终结果不可控甚至查不出来。如果你需要“随机取一行”应该把随机数算好放进变量或者用子查询先算好SET rand_id FLOOR(RAND() * 1000) 1; SELECT * FROM users WHERE id rand_id;在存储过程或脚本里这样写没问题。如果是纯SQL可以用SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users)) 1);这里的子查询先执行外层比较用的是固定的随机值。5. 三角函数与进制转换冷门但关键时刻救命5.1 三角函数先把角度转弧度再说MySQL里的sin、cos、tan、asin、acos、atan、atan2、cot参数单位都是弧度不是角度。所以如果你拿到的是45度直接sin(45)会得到一个奇怪的结果必须先转成弧度sin(radians(45))。radians(x)将角度转弧度degrees(x)将弧度转角度。atan2(y, x)返回点(x, y)与x轴正方向之间的角度范围是-pi到pi。这个函数在计算两个坐标点之间的方位角时非常实用。某地图路径计算项目里需要算两个GPS点之间的初始方位角公式就是atan2(lon2 - lon1, lat2 - lat1)再转成角度。这类场景虽然不常见但一旦遇到分不清弧度和角度就会算出一个完全不对劲的方位。实际开发中还有一类用法把二维平面上的角度转换成弧度后配合cos和sin做坐标旋转。某图像处理Demo里要把标注框旋转30度就用到了cos、sin和radians的组合。5.2 进制转换BIN、OCT、HEX与CONVbin(x)将十进制转为二进制字符串oct(x)转八进制字符串hex(x)转十六进制字符串。conv(n, from_base, to_base)是通用函数可以在任意进制间转换。某项目里需要把设备上报的十六进制状态码转成二进制判断第几位是1直接SELECT CONV(HEX(status_code), 16, 10);或者更直接地用conv(status_code, 10, 2)把十进制转二进制再用字符串函数SUBSTRING提取对应位。hex还有个隐藏用法把字符串转成十六进制比如hex(ABC)返回414243。但这不是数值函数的本意容易混淆使用前最好确认参数类型是数字还是字符串。这里建议在做IP地址等场景时优先用精确的数值运算函数。6. 常见问题排查与实战避坑指南6.1 ROUND返回类型不一致导致排序错乱round(字段, 2)在处理不同字段类型时返回类型可能不同。比如字段是DECIMAL返回DECIMAL字段是DOUBLE返回DOUBLE。DOUBLE是浮点数在小数比较时可能出现精度问题。某报表项目里同一个金额字段有的表是DECIMAL(10,2)有的表是FLOATunion all之后按金额排序结果出现103.10排在103.9后面的情况。排查后发现是FLOAT字段经过round后变成DOUBLE精度已经丢失。建议做法统一用CAST(ROUND(amount, 2) AS DECIMAL(10,2))保证所有表的结果类型一致。6.2 FORMAT返回值是字符串别拿去参与计算format(12345.678, 2)返回的是12,345.68带千位分隔符而且类型是字符串。有人做报表时用format做展示再把这个字段参与计算比如SUM(format(amount, 2))MySQL在隐式转换时会把字符串转成数值但带逗号的字符串会变成奇怪的数字或者直接导致警告。要区分两个需求展示用format没问题计算用round或truncate。不要在计算中间层使用格式化函数。6.3 RAND在ORDER BY里能做抽样但代价要心里有数前面提过ORDER BY RAND()在大表上的性能问题。这里再补充一个经验如果表有主键且数字分布相对连续用偏移量抽样的方式比ORDER BY RAND()快得多。但如果主键空洞特别多比如大量删除导致id不连续建议用JOIN一张随机生成的序号表或者直接接受取样偏差。随机抽样还有一个常见场景从100个用户里抽10个做测试。此时用SELECT * FROM users WHERE id IN ( SELECT id FROM users ORDER BY RAND() LIMIT 10 );配合IN子查询相对可控。6.4 浮点精度丢失金额永远别用FLOAT/DOUBLE这件事怎么强调都不过分。0.1 0.2在计算机里不等于0.3这在MySQL的DOUBLE里同样存在。比如某账单系统用DOUBLE存金额0.1 * 3算出0.30000000000000004页面上显示出来就是0.30000000000000004财务看到会直接炸。MySQL里浮点数与DECIMAL的区别就类似于你用分数表示1/3和用小数表示0.333333333的差别后者总会丢掉一点精度。金额字段用DECIMAL(10, 2)或DECIMAL(12, 4)计算时用DECIMAL运算最终展示再format。如果历史表已经用了FLOAT迁移时做数据订正千万不要在SQL里依赖round去修补那只会把问题隐藏起来。6.5 数值函数速查表下表是实际开发中最常用到的几个函数及其典型场景可以截图保存。函数作用典型场景注意点ROUND(x, d)四舍五入保留d位小数账单金额、评分计算浮点输入可能导致边界误差TRUNCATE(x, d)直接截断保留d位小数手续费只舍不入不四舍五入CEIL(x)向上取整计算需要几个箱子/车子恰好整数时不进位FLOOR(x)向下取整分桶、分页负数边界注意ABS(x)取绝对值差异统计无特殊情况SIGN(x)返回符号1/0/-1趋势判断结果非正即负或零MOD(a, b)取模分表、奇偶判断MySQL负数取模结果非负POW(a, b)幂运算特征计算注意返回DOUBLE精度SQRT(x)平方根几何计算负数返回NULLRAND()0到1随机数抽样、分组WHERE里每行执行一次RADIANS(x)角度转弧度三角函数别忘记转换CONV(n, a, b)任意进制转换状态码解析返回字符串我在实际项目里最大的体会是数值函数本身不复杂复杂的是业务对精度的要求。你写SQL之前一定要确认好三个问题——这个字段参与计算吗结果要参与排序吗要不要再和其他类型比较确认完这三个问题函数选型基本不会出偏差。最后分享一个小技巧如果要用ROUND但结果又要保证绝对精确可以在ROUND之前先CAST成DECIMAL比如ROUND(CAST(amount AS DECIMAL(20, 4)), 2)。这看起来多了一步实际上能帮你躲掉90%的浮点精度坑。数值函数的坑我踩了一遍把你的原始字段类型和结果类型管理好这张表里的函数就用得明明白白。