MySQL函数：`TRUNCATE()`截断数字，不进行四舍五入。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 函数 TRUNCATE()：数字截断详解

大家好，今天我们来深入探讨 MySQL 中的 TRUNCATE() 函数。这个函数的功能很简单，就是截断数字，移除小数点后的部分，但它与 ROUND() 函数有着本质的区别：TRUNCATE() 不进行四舍五入。理解这一点至关重要，因为它直接影响到计算结果的精度和准确性。

1. `TRUNCATE()` 函数的基本语法

TRUNCATE() 函数接受两个参数：

TRUNCATE(number, decimals)

number: 需要被截断的数值。它可以是常量、字段名、表达式或者其他返回数值的函数。
decimals: 指定保留的小数位数。
- 如果 decimals 为正数，则截断到指定的小数位数。例如，TRUNCATE(123.456, 2) 返回 123.45。
- 如果 decimals 为零或省略，则截断到整数部分。例如，TRUNCATE(123.456, 0) 或 TRUNCATE(123.456) 返回 123。
- 如果 decimals 为负数，则从小数点左侧开始截断，将指定位数设置为零。例如，TRUNCATE(123.456, -1) 返回 120，TRUNCATE(123.456, -2) 返回 100。

2. `TRUNCATE()` 与 `ROUND()` 的对比：核心区别

这是理解 TRUNCATE() 的关键所在。让我们通过几个例子来对比 TRUNCATE() 和 ROUND() 的行为：

数值	`TRUNCATE(数值, 2)`	`ROUND(数值, 2)`	备注
123.456	123.45	123.46	`ROUND()` 进行了四舍五入
123.454	123.45	123.45	`.454` 小于 `.5`，`ROUND()` 没有进位
123.450	123.45	123.45	即使最后一位是 0，`TRUNCATE()` 仍然截断
-123.456	-123.45	-123.46	负数的四舍五入规则同样适用
123.999	123.99	124.00	注意 `ROUND()` 可能导致进位到整数部分

从上表可以看出，ROUND() 会根据小数点后一位的大小决定是否进位，而 TRUNCATE() 总是直接舍弃指定位数之后的部分。

3. `TRUNCATE()` 的使用场景

TRUNCATE() 在以下场景中特别有用：

需要精确控制数值范围，避免四舍五入带来的误差。 例如，在计算折扣时，为了保证最低价格，可以使用 TRUNCATE() 截断计算结果，而不是 ROUND()。
需要强制将数值限制在某个精度范围内。 例如，在存储货币数据时，可能需要将所有金额截断到小数点后两位。
生成报告或进行数据分析时，需要保持数据的一致性。 使用 TRUNCATE() 可以确保所有数值都按照相同的规则进行截断，避免因四舍五入而产生偏差。
在某些特定的算法或逻辑中，需要直接舍弃小数部分。 某些算法可能依赖于整数运算，此时使用 TRUNCATE() 可以方便地将浮点数转换为整数。

4. `TRUNCATE()` 在 SQL 查询中的应用

让我们看一些 TRUNCATE() 在 SQL 查询中的实际应用例子。

4.1 从表中选择数据并截断：

假设我们有一个名为 products 的表，其中包含 price 字段，表示产品的价格。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    price DECIMAL(10, 3)  -- 价格，总共 10 位，其中 3 位小数
);

INSERT INTO products (name, price) VALUES
('Product A', 123.456),
('Product B', 456.789),
('Product C', 789.012);

-- 查询所有产品，并将价格截断到小数点后两位
SELECT id, name, TRUNCATE(price, 2) AS truncated_price
FROM products;

这条 SQL 语句会返回一个结果集，其中 truncated_price 列包含了 price 字段截断到小数点后两位的值。

4.2 在 WHERE 子句中使用 TRUNCATE() 进行过滤：

-- 查询价格截断到整数部分后等于 123 的产品
SELECT id, name, price
FROM products
WHERE TRUNCATE(price, 0) = 123;

这条 SQL 语句会返回 Product A，因为 TRUNCATE(123.456, 0) 的结果是 123。

4.3 在 UPDATE 语句中使用 TRUNCATE() 修改数据：

-- 将所有产品的价格截断到小数点后一位
UPDATE products
SET price = TRUNCATE(price, 1);

SELECT * FROM products;

这条 SQL 语句会将 products 表中所有产品的 price 字段截断到小数点后一位。

4.4 在计算中使用 TRUNCATE()：

-- 计算每个产品的折扣价（原价的 9 折），并将折扣价截断到小数点后两位
SELECT
    id,
    name,
    price,
    TRUNCATE(price * 0.9, 2) AS discounted_price
FROM products;

这个例子展示了如何在计算中使用 TRUNCATE()，确保折扣价符合预期的精度。

4.5 使用负数 decimals 进行截断

SELECT TRUNCATE(12345.678, -1); -- 返回 12340
SELECT TRUNCATE(12345.678, -2); -- 返回 12300
SELECT TRUNCATE(12345.678, -3); -- 返回 12000

这些例子展示了如何使用负数 decimals 参数，从小数点左侧开始截断。这在处理需要近似到整十、整百、整千的场景中非常有用。

5. `TRUNCATE()` 的注意事项

数据类型： TRUNCATE() 函数适用于数值类型的数据，包括 INT, DECIMAL, FLOAT, DOUBLE 等。如果传递给 TRUNCATE() 的参数不是数值类型，MySQL 会尝试将其转换为数值类型。如果转换失败，则会返回错误。
NULL 值： 如果 TRUNCATE() 函数的任何一个参数为 NULL，则结果也为 NULL。
性能： 对于大数据量的表，频繁使用 TRUNCATE() 函数可能会影响查询性能。在可能的情况下，建议在应用程序层面进行截断，而不是在数据库层面。
与 FLOOR() 和 CEILING() 的关系： TRUNCATE(x, 0) 等价于 FLOOR(x)，当 x 为正数时。对于负数，TRUNCATE(x, 0) 的行为更接近于 CEILING(x)。例如：
- TRUNCATE(3.14, 0) = FLOOR(3.14) = 3
- TRUNCATE(-3.14, 0) = -3，而 FLOOR(-3.14) = -4， CEILING(-3.14) = -3
存储过程和函数： TRUNCATE() 函数可以在 MySQL 的存储过程和函数中使用，以实现更复杂的业务逻辑。

6. `TRUNCATE()` 的高级用法示例

6.1 结合 CASE 语句进行条件截断：

-- 根据不同的产品类型，采用不同的截断精度
SELECT
    id,
    name,
    price,
    CASE
        WHEN category = 'Electronics' THEN TRUNCATE(price, 2)
        WHEN category = 'Clothing' THEN TRUNCATE(price, 0)
        ELSE price  -- 默认不截断
    END AS adjusted_price
FROM products;

这个例子展示了如何使用 CASE 语句，根据不同的条件应用不同的截断精度。

6.2 在聚合函数中使用 TRUNCATE()：

-- 计算每个产品类别的平均价格，并将平均价格截断到整数部分
SELECT
    category,
    TRUNCATE(AVG(price), 0) AS average_price
FROM products
GROUP BY category;

这个例子展示了如何在聚合函数中使用 TRUNCATE()，对聚合结果进行截断。

6.3 创建一个自定义函数来实现特定截断逻辑：

-- 创建一个自定义函数，将数值截断到最接近的 0.5 的倍数
DELIMITER //
CREATE FUNCTION TRUNCATE_TO_HALF(number DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE truncated_value DECIMAL(10, 2);
    SET truncated_value = TRUNCATE(number * 2, 0) / 2;
    RETURN truncated_value;
END //
DELIMITER ;

-- 使用自定义函数
SELECT TRUNCATE_TO_HALF(3.78);  -- 返回 3.5
SELECT TRUNCATE_TO_HALF(4.22);  -- 返回 4.0
SELECT TRUNCATE_TO_HALF(5.99);  -- 返回 5.5

这个例子展示了如何创建一个自定义函数，来实现更复杂的截断逻辑。通过将数值乘以 2，截断到整数部分，再除以 2，就可以实现截断到最接近的 0.5 的倍数。

7. `TRUNCATE()` 的局限性

虽然 TRUNCATE() 在很多场景下都非常有用，但它也有一些局限性：

不支持指定舍入模式： TRUNCATE() 总是直接舍弃指定位数之后的部分，无法指定其他的舍入模式，例如向上舍入、向下舍入、四舍五入等。如果需要更复杂的舍入逻辑，需要使用 ROUND() 函数或者自定义函数。
不适用于字符串类型的数据： TRUNCATE() 函数主要用于数值类型的数据，不适用于字符串类型的数据。如果需要截断字符串，需要使用 LEFT() 或 SUBSTRING() 等字符串函数。

8. 如何选择合适的截断/舍入函数

在选择 TRUNCATE() 和 ROUND() 函数时，需要根据具体的业务需求和数据特点进行权衡。

如果需要精确控制数值范围，避免四舍五入带来的误差，或者需要强制将数值限制在某个精度范围内，则应该使用 TRUNCATE()。
如果需要进行四舍五入，或者需要根据小数点后一位的大小决定是否进位，则应该使用 ROUND()。
如果需要更复杂的舍入逻辑，例如向上舍入、向下舍入等，可以使用 CEILING()、FLOOR() 等函数，或者自定义函数。

在实际应用中，建议充分了解各种截断/舍入函数的特性和适用场景，并根据实际情况选择最合适的函数。

9. 最佳实践

明确需求： 在使用 TRUNCATE() 之前，务必明确业务需求，确定需要保留的小数位数，以及是否需要进行四舍五入。
测试验证： 在生产环境中使用 TRUNCATE() 之前，建议进行充分的测试验证，确保截断结果符合预期。
代码注释： 在代码中添加清晰的注释，说明 TRUNCATE() 的用途和参数含义，方便后续维护和理解。
性能优化： 对于大数据量的表，尽量避免在循环或频繁调用的函数中使用 TRUNCATE()，以提高查询性能。可以考虑在应用程序层面进行截断。

结论：精确截断，避免混淆

TRUNCATE() 是一个强大的 MySQL 函数，可以帮助我们精确地截断数字，避免四舍五入带来的误差。理解 TRUNCATE() 和 ROUND() 的区别，并根据实际需求选择合适的函数，是编写高质量 SQL 代码的关键。掌握 TRUNCATE() 的使用方法和注意事项，可以帮助我们更好地处理数值数据，提高数据分析的准确性和效率。记住，TRUNCATE() 简单直接，它只进行截断，不进行任何形式的舍入。

MySQL 函数 TRUNCATE()：数字截断详解

1. TRUNCATE() 函数的基本语法

2. TRUNCATE() 与 ROUND() 的对比：核心区别

3. TRUNCATE() 的使用场景

4. TRUNCATE() 在 SQL 查询中的应用

5. TRUNCATE() 的注意事项

6. TRUNCATE() 的高级用法示例

7. TRUNCATE() 的局限性