mysql中的数学函数与计算操作_技术教程

MySQL常见数学函数包括ABS、ROUND、FLOOR、CEILING、MOD/%、POWER，支持列名和嵌套；ROUND对FLOAT/DOUBLE因精度问题结果不定，建议用DECIMAL；MOD与%等价且余数符号随被除数；WHERE中使用函数会导致索引失效，应改用范围查询或生成列索引。

MySQL 中常见的数学函数怎么用

MySQL 提供了一批内置数学函数，直接在 SELECT、WHERE 或更新语句里调用即可，不需要额外导入或声明。它们处理的是数值型字段或字面量，对 NULL 输入通常返回 NULL。

常用且实用的包括：

ABS(x)：取绝对值，ABS(-3.5) → 3. 5
ROUND(x, d)：四舍五入到小数点后 d 位，ROUND(3.14159, 2) → 3.14；省略 d 则取整
FLOOR(x) 和 CEILING(x)：向下/向上取整，FLOOR(3.9) → 3，CEILING(3.1) → 4
MOD(a, b) 或 a % b：取模，注意 b 为 0 会报错 Division by zero
POWER(x, y) 或 POW(x, y)：幂运算，POWER(2, 3) → 8

这些函数支持列名、表达式甚至嵌套，比如 ROUND(AVG(price), 2) 在聚合中很常见。

为什么 ROUND(2.5) 有时返回 2 而不是 3

这是 MySQL 默认的“银行家舍入”（round half away from zero 的变体）在某些版本和模式下的表现差异，但更常见的是受 sql_mode 中 STRICT_TRANS_TABLES 或 ERROR_FOR_DIVISION_BY_ZERO 影响间接导致——其实根本原因在于浮点精度。

真正要注意的是：ROUND() 对 DECIMAL 类型行为稳定，对 FLOAT/DOUBLE 可能因存储误差出人意料。例如：

SELECT ROUND(2.5), ROUND(2.15, 1), ROUND(CAST(2.15 AS DECIMAL(10,2)), 1);

结果可能分别是 2、2.1、2.2。原因在于 2.15 存成 FLOAT 后实际是 2.1499999999999999，ROUND 按此截断。

建议：

涉及金额、精度要求高的场景，一律用 DECIMAL 类型存数据
需要确定性舍入时，显式转类型：ROUND(CAST(x AS DECIMAL(12,4)), 2)
避免在 GROUP BY 或索引字段上依赖 ROUND() 输出做等值判断

MOD 和 % 在负数时行为不一致？

一致，但结果符号取决于被除数（第一个操作数）。MySQL 中 MOD(a, b) 和 a % b 完全等价，定义为：a - FLOOR(a/b) * b。

所以：

MOD(7, 3) → 1
MOD(-7, 3) → 2（因为 -7 = -3×3 + 2）
MOD(7, -3) → 1（符号由被除数决定，除数符号被忽略）

这和 Python 的 % 不同（Python 结果符号跟随除数），也和 C 的 % 不同（C 要求除数非负）。如果业务逻辑依赖特定符号规则，务必在 SQL 层显式处理，例如用 ABS(MOD(a, b)) 统一为正余数。

计算性能与索引失效风险

在 WHERE 条件中对字段使用数学函数，几乎必然导致索引失效。例如：

SELECT * FROM sales WHERE ROUND(price, 0) = 100;

即使 price 有索引，MySQL 也无法用它加速这个查询，因为函数改变了原始值分布。

可行的替代方式：

改写为范围条件：WHERE price BETWEEN 99.5 AND 100.499999
增加生成列并建索引（MySQL 5.7+）：
```
ALTER TABLE sales ADD COLUMN price_rounded TINYINT AS (ROUND(price, 0)) STORED;
```
再 CREATE INDEX idx_price_r ON sales(price_rounded);
应用层预计算并存入冗余字段，避免每次查都算

函数本身开销不大，但一旦破坏索引，代价远超计算耗时——这点容易被忽略，尤其在数据量上来之后。