写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.397，round之后变成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.565，round(2.565, 2)就能正确得到2.57。
我踩过这个坑。某账单系统的金额字段用的是DECIMAL(10, 2)，后来改成从第三方接口读DOUBLE类型，结果日志里一堆舍入差异。排查到最后，问题就出在存储类型上。
实操建议：
- 金额计算尽量用
DECIMAL，不要用FLOAT或DOUBLE - 如果需要绝对精确的四舍五入，先把字段转成
DECIMAL再round round的第二个参数d可以省略，取整；也可以传负数，比如round(1234, -2)得到1200，round(1234, -3)得到1000，这是对整数位做舍入，做粗略统计时有用
2.2 TRUNCATE：直接截断，不跟你商量
truncate(x, d)和round长得像，但行为完全不同：它把小数点后d位之后的数字直接砍掉，不四舍五入。truncate(2.567, 2)结果是2.56，truncate(2.999, 1)结果是2.9。
业务里什么时候必须用truncate？银行存款利息计算、手续费分账、部分业务统计里“只舍不入”规则。比如某支付通道的分润规则是“按交易金额的0.6%计算，分润金额保留两位小数，不足一分的部分不做四舍五入，直接舍去”。用round就会出现多给的情况，用truncate才对。
truncate也支持负数位，truncate(1234, -2)结果是1200，truncate(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、SQRT
3.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与CONV
bin(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)，有的表是FLOAT，union 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) | 平方根 | 几何计算 | 负数返回NULL |
| RAND() | 0到1随机数 | 抽样、分组 | WHERE里每行执行一次 |
| RADIANS(x) | 角度转弧度 | 三角函数 | 别忘记转换 |
| CONV(n, a, b) | 任意进制转换 | 状态码解析 | 返回字符串 |
我在实际项目里最大的体会是：数值函数本身不复杂，复杂的是业务对精度的要求。你写SQL之前，一定要确认好三个问题——这个字段参与计算吗？结果要参与排序吗？要不要再和其他类型比较？确认完这三个问题，函数选型基本不会出偏差。
最后分享一个小技巧：如果要用ROUND，但结果又要保证绝对精确，可以在ROUND之前先CAST成DECIMAL，比如ROUND(CAST(amount AS DECIMAL(20, 4)), 2)。这看起来多了一步，实际上能帮你躲掉90%的浮点精度坑。数值函数的坑我踩了一遍，把你的原始字段类型和结果类型管理好，这张表里的函数就用得明明白白。
源码导读：从IoC容器初始化到Bean生命周期)
