悠悠楠杉
网站页面
MySQL中常用的数学函数及应用案例详解
09/01
本文详细介绍MySQL中10个核心数学函数的使用方法，通过实际业务场景案例演示如何应用这些函数解决数值计算、数据格式化及统计分析问题。
一、基础数学函数
1. ABS() 绝对值函数
sql
SELECT ABS(-23.5); -- 输出23.5
典型应用场景：计算账务差异时消除方向性影响。例如统计用户余额波动幅度：
sql
SELECT user_id, ABS(balance - previous_balance) AS change_amount
FROM account_records;
2. ROUND() 四舍五入
sql
SELECT ROUND(3.14159, 2); -- 输出3.14
实际案例：电商平台商品折扣价计算时保留两位小数：
sql
UPDATE products
SET sale_price = ROUND(original_price * 0.8, 2);
3. CEIL() / FLOOR() 取整函数
sql
SELECT CEIL(3.2); -- 输出4
SELECT FLOOR(3.9); -- 输出3
物流行业应用：计算货物需要的整箱数量（不允许部分装箱）：
sql
SELECT CEIL(total_quantity/box_capacity) AS required_boxes
FROM shipments;
二、高级计算函数
4. POWER() 幂运算
sql
SELECT POWER(2, 3); -- 输出8
金融复利计算案例：
sql
SELECT principal * POWER(1 + rate, years) AS final_amount
FROM investments;
5. SQRT() 平方根
sql
SELECT SQRT(9); -- 输出3
几何应用：计算地图两点间直线距离（勾股定理）：
sql
SELECT SQRT(POWER(x2-x1,2) + POWER(y2-y1,2)) AS distance
FROM coordinates;
6. RAND() 随机数
sql
SELECT RAND(); -- 输出0~1之间的随机浮点数
抽奖系统实现：
sql
SELECT user_id FROM users
ORDER BY RAND()
LIMIT 5; -- 随机抽取5名用户
三、统计分析函数
7. MOD() 取模运算
sql
SELECT MOD(10,3); -- 输出1
分表策略应用：根据用户ID哈希分表：
sql
CREATE TABLE user_orders_${MOD(user_id,10)}
AS SELECT * FROM orders WHERE MOD(user_id,10)=0;
8. TRUNCATE() 截断小数
sql
SELECT TRUNCATE(3.14159,3); -- 输出3.141
与ROUND()的区别：直接截断不进行四舍五入。财务合规要求场景：
sql
SELECT TRUNCATE(interest,4) AS legal_interest
FROM loans;
四、综合应用案例
库存预警系统实现
sql
-- 计算库存周转率并触发预警
SELECT
product_id,
ROUND(sales/AVG(stock),2) AS turnover_rate,
CASE
WHEN MOD(stock,2)=0 THEN '双数库存预警'
WHEN stock < FLOOR(reorder_point*0.7) THEN '紧急补货'
ELSE '正常'
END AS alert_status
FROM inventory
WHERE warehouse_id=5;
注意事项：
1. 数学函数处理NULL时通常返回NULL
2. 超大数值计算可能溢出，建议使用DECIMAL类型
3. RAND()在WHERE子句中会导致全表扫描
