数据库运算 SQL技巧 mysql取余与取整方法详解

数据库运算 | SQL技巧 | MySQL取余与取整方法详解

场景引入：电商库存管理的常见需求

"小王最近负责公司电商平台的库存管理系统开发，遇到了一个实际问题：当用户下单购买多个商品时，如何快速计算需要从哪个仓库调货？比如用户买了15件商品，每个仓库标准包装是6件一箱，那么需要从几个仓库调货？剩余零散的又该怎么处理？"

这类问题在日常开发中很常见，而MySQL提供的取余和取整函数正是解决这类计算的神器,今天我们就来详细聊聊MySQL中的这些实用运算技巧。

MySQL中的取整函数

FLOOR() - 向下取整

FLOOR函数返回不大于参数的最大整数值，也就是"地板函数"。

SELECT FLOOR(3.7);  -- 结果为3
SELECT FLOOR(-2.3); -- 结果为-3

实际应用场景： 计算商品打包箱数，每箱装10件,123件商品需要多少箱？

SELECT FLOOR(123/10) AS 整箱数;  -- 结果为12箱

CEILING() / CEIL() - 向上取整

这两个函数功能相同,返回不小于参数的最小整数值。

SELECT CEILING(3.2);  -- 结果为4
SELECT CEIL(-1.8);   -- 结果为-1

实际应用场景： 计算服务器负载，每个容器最多处理1000请求,2350个请求需要多少容器？

SELECT CEIL(2350/1000) AS 所需容器数;  -- 结果为3

ROUND() - 四舍五入取整

ROUND函数可以对数字进行四舍五入,可以指定保留小数位数。

SELECT ROUND(3.4);     -- 结果为3
SELECT ROUND(3.5);     -- 结果为4
SELECT ROUND(3.456, 2); -- 结果为3.46

实际应用场景： 计算商品平均评分,保留1位小数：

SELECT ROUND(AVG(rating), 1) AS 平均评分 FROM product_reviews;

TRUNCATE() - 截断小数

与ROUND不同,TRUNCATE直接截断指定位数后的小数。

SELECT TRUNCATE(3.456, 1); -- 结果为3.4
SELECT TRUNCATE(3.456, 2); -- 结果为3.45

实际应用场景： 财务系统需要精确到分,不进行四舍五入：

SELECT TRUNCATE(total_amount, 2) AS 精确金额 FROM orders;

MySQL中的取余运算

MOD()函数与%运算符

MySQL中取余运算有两种写法,功能完全相同：

SELECT MOD(10, 3);  -- 结果为1
SELECT 10 % 3;      -- 结果为1

实际应用场景： 判断数字奇偶性：

SELECT IF(number % 2 = 0, '偶数', '奇数') AS 奇偶性 FROM numbers;

取余运算的实用案例

案例1：分页查询计算总页数

-- 假设每页显示15条记录，总记录数238条
SELECT 
    FLOOR(238/15) AS 完整页数,
    IF(238%15 > 0, 1, 0) AS 是否有剩余页;

案例2：时间周期计算 计算某个日期是该年的第几周,以及剩余天数：

SELECT 
    FLOOR(DAYOFYEAR('2025-08-20')/7) AS 完整周数,
    MOD(DAYOFYEAR('2025-08-20'), 7) AS 剩余天数;

综合应用实例

电商库存分配问题

回到开头的场景：15件商品，每箱6件,如何分配？

SELECT 
    FLOOR(15/6) AS 整箱数,  -- 2箱
    MOD(15, 6) AS 零散数;   -- 3件

员工排班系统

假设有30个员工，每天需要7人值班,如何循环分配？

SELECT 
    employee_id,
    MOD(employee_id, 7) AS 值班组别
FROM employees
ORDER BY 值班组别;

数据分片策略

对用户ID进行取模分库分表：

-- 分为4个表
SELECT 
    user_id,
    CONCAT('user_table_', MOD(user_id, 4)) AS 分表名称
FROM users;

性能考虑与注意事项

1. 函数选择：在只需要整数结果时，使用FLOOR/CEIL比ROUND效率更高
2. 负数处理：取余运算的结果符号与被除数相同，这在处理负数时要特别注意

   SELECT MOD(-10, 3);  -- 结果为-1
   SELECT -10 % 3;      -- 结果为-1

3. 除零错误：取余运算的除数不能为0，否则会报错
4. 浮点数精度：浮点数取余可能产生意外结果，建议先转换为DECIMAL类型

进阶技巧

实现周期循环

利用取模运算实现循环序号：

-- 生成1-7的循环序号
SELECT MOD((row_number - 1), 7) + 1 AS 循环序号 FROM some_table;

数据均匀分布

确保数据均匀分布到不同分区：

-- 均匀分配到5个分区
SELECT *, MOD(id, 5) AS partition_num FROM large_table;

自定义分组

按自定义规则分组：

-- 按年龄每10岁一组
SELECT 
    name,
    age,
    FLOOR(age/10)*10 AS 年龄分组
FROM users;

MySQL的取余和取整函数看似简单，但在实际业务场景中应用广泛,掌握这些技巧可以让你：

1. 更优雅地解决分页、分组等常见问题
2. 优化数据分布和查询性能
3. 简化复杂的业务逻辑计算
4. 提高SQL代码的可读性和维护性

下次当你遇到需要分配、分组或周期处理的问题时，不妨先想想这些基础的数学运算函数,它们往往能以最简洁的方式解决看似复杂的问题。