MySQL 高级函数：CEILING() 和 FLOOR() – 向上与向下取整的艺术
大家好，今天我们来深入探讨 MySQL 中两个非常有用的数学函数：CEILING() 和 FLOOR()。 它们的主要作用是对数值进行取整，但分别采用向上取整和向下取整的方式。 掌握这两个函数，可以帮助我们在处理数据时更加灵活，满足各种不同的业务需求。
1. CEILING() 函数：向上取整
CEILING() 函数的作用是返回大于或等于给定数值的最小整数。换句话说，它会将一个数值向上取整到最接近的整数。
- 语法:
CEILING(number)其中 number 是要进行取整操作的数值，可以是整数、小数或者包含数值的表达式。
- 示例:
SELECT CEILING(3.14); -- 输出: 4
SELECT CEILING(5); -- 输出: 5
SELECT CEILING(-2.8); -- 输出: -2
SELECT CEILING(0); -- 输出: 0
SELECT CEILING(-0.5); -- 输出: 0从上面的例子可以看出，CEILING() 函数总是返回大于或等于输入值的最小整数。对于正数，它会直接向上取整；对于负数，它会向零的方向取整。
2. FLOOR() 函数：向下取整
FLOOR() 函数的作用是返回小于或等于给定数值的最大整数。 也就是说，它会将一个数值向下取整到最接近的整数。
- 语法:
FLOOR(number)同样，number 是要进行取整操作的数值。
- 示例:
SELECT FLOOR(3.14); -- 输出: 3
SELECT FLOOR(5); -- 输出: 5
SELECT FLOOR(-2.8); -- 输出: -3
SELECT FLOOR(0); -- 输出: 0
SELECT FLOOR(-0.5); -- 输出: -1与 CEILING() 函数相反，FLOOR() 函数总是返回小于或等于输入值的最大整数。 对于正数，它会直接向下取整；对于负数，它会向负无穷的方向取整。
3. CEILING() 和 FLOOR() 的对比
为了更清晰地理解这两个函数的区别，我们可以用一个表格来总结它们在不同情况下的行为：
| 数值 | CEILING() 结果 |
FLOOR() 结果 |
|---|---|---|
| 3.14 | 4 | 3 |
| 5 | 5 | 5 |
| -2.8 | -2 | -3 |
| 0 | 0 | 0 |
| -0.5 | 0 | -1 |
| 7.99 | 8 | 7 |
| -7.99 | -7 | -8 |
4. 实际应用场景
CEILING() 和 FLOOR() 函数在实际开发中有很多应用场景，下面我们列举一些常见的例子：
-
分页功能： 在实现分页功能时，我们经常需要计算总页数。 假设我们有 105 条记录，每页显示 10 条记录，那么总页数应该是
CEILING(105 / 10)，即 11 页。SELECT CEILING(COUNT(*) / 10) AS total_pages FROM your_table; -
库存管理： 假设我们需要对库存进行盘点，如果库存数量小于一个最小单位，我们需要将其向上取整到最小单位。 例如，最小单位是 1，而实际库存是 0.2，那么我们需要将其向上取整到 1。
SELECT CEILING(inventory_quantity) AS adjusted_quantity FROM inventory_table; -
计费系统： 在某些计费系统中，例如按小时计费，即使使用时间不足一小时，也需要按一小时计算。 这时，我们可以使用
CEILING()函数将使用时间向上取整到小时数。SELECT CEILING(usage_time) AS billed_hours FROM usage_table; -
数据分析： 在数据分析中，我们可能需要将连续型数据离散化，例如将年龄划分为不同的年龄段。
FLOOR()函数可以帮助我们将年龄向下取整到某个范围的最小值。 比如，将年龄除以 10 然后向下取整，可以得到年龄段的起始值。SELECT FLOOR(age / 10) * 10 AS age_group FROM user_table; -
避免除零错误： 在进行除法运算时，如果除数可能为零，可以使用
CEILING()或FLOOR()函数来确保除数不为零。 例如，如果除数是某个统计量，我们可以将其加上一个很小的正数，然后使用CEILING()函数向上取整到 1，从而避免除零错误。 但需要根据具体场景判断是否合适，因为这会改变计算结果。SELECT value / CEILING(statistic + 0.0001) AS result FROM data_table; -
地理位置计算： 在某些地理位置计算中，例如计算距离某个位置最近的网点数量，我们可能需要将距离进行分组，然后统计每个组内的网点数量。
FLOOR()函数可以帮助我们将距离向下取整到某个精度。SELECT FLOOR(distance) AS distance_group, COUNT(*) AS num_branches FROM branch_table GROUP BY distance_group;
5. CEILING() 和 FLOOR() 与 ROUND() 的区别
很多人容易将 CEILING() 和 FLOOR() 函数与 ROUND() 函数混淆。 虽然它们都用于对数值进行处理，但它们的行为却完全不同。
-
ROUND()函数用于四舍五入到指定的位数。 它可以将数值向上或向下舍入到最接近的指定位数的值。 -
CEILING()函数始终向上取整到最接近的整数。 -
FLOOR()函数始终向下取整到最接近的整数。
下面用一个表格来对比它们的行为：
| 数值 | ROUND(number, 0) 结果 |
CEILING(number) 结果 |
FLOOR(number) 结果 |
|---|---|---|---|
| 3.14 | 3 | 4 | 3 |
| 3.5 | 4 | 4 | 3 |
| 3.9 | 4 | 4 | 3 |
| -2.1 | -2 | -2 | -3 |
| -2.5 | -3 | -2 | -3 |
| -2.9 | -3 | -2 | -3 |
从上面的表格可以看出，ROUND() 函数的行为取决于小数部分的大小，而 CEILING() 和 FLOOR() 函数则始终分别向上和向下取整。
6. 结合其他函数使用
CEILING() 和 FLOOR() 函数可以与其他函数结合使用，以实现更复杂的功能。 例如，我们可以将它们与 RAND() 函数结合使用，生成指定范围内的随机整数。
SELECT FLOOR(RAND() * (max - min + 1)) + min AS random_integer
FROM (SELECT 10 AS min, 100 AS max) AS params; -- 生成 10 到 100 之间的随机整数上面的代码首先使用 RAND() 函数生成一个 0 到 1 之间的随机数，然后将其乘以 (max - min + 1)，得到一个 0 到 (max - min + 1) 之间的随机数。 最后，使用 FLOOR() 函数将其向下取整，并加上 min，得到一个 min 到 max 之间的随机整数。
又比如，结合MOD()取模函数，可以实现一些更高级的逻辑。 比如，如果需要将某个数值调整到最接近的5的倍数，可以使用如下的组合：
SELECT value - MOD(value,5) AS floor_nearest_5,
value + (5 - MOD(value,5)) AS ceil_nearest_5
FROM (SELECT 12 AS value) AS t;7. 注意事项
-
CEILING()和FLOOR()函数只对数值类型有效。 如果输入的不是数值类型，MySQL 会尝试将其转换为数值类型。 如果转换失败，则会返回错误。 -
在处理非常大的数值时，可能会出现精度问题。 这是因为 MySQL 使用浮点数来存储数值，而浮点数的精度是有限的。 在这种情况下，可以使用
DECIMAL类型来存储数值，以提高精度。 -
CEILING()和FLOOR()函数不会改变原始数据的值。 它们只是返回一个新的值，表示取整后的结果。
8. 性能考量
CEILING() 和 FLOOR() 函数通常执行速度很快，不会对查询性能产生明显的影响。 但是，在处理大量数据时，仍然需要注意优化查询语句，以提高查询效率。 例如，可以避免在 WHERE 子句中使用函数，因为这会导致索引失效。
9. 总结：取整函数的应用价值
CEILING() 和 FLOOR() 函数是 MySQL 中非常有用的数学函数。 它们可以帮助我们对数值进行向上和向下取整，从而满足各种不同的业务需求。 掌握这两个函数，可以提高我们在处理数据时的灵活性和效率。 它们的应用场景十分广泛，从简单的分页计算到复杂的统计分析，都能发挥重要作用。 熟练运用这两个函数，可以使你的SQL代码更加简洁高效。