MySQL函数：`FORMAT()`格式化数字并添加千位分隔符，美化查询结果。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL `FORMAT()` 函数：数字格式化的利器

大家好！今天我们来深入探讨MySQL中一个非常实用且容易被忽略的函数：FORMAT()。它能帮助我们格式化数字，添加千位分隔符，从而使查询结果更加易读和美观。在数据库报表、数据分析等场景下，FORMAT() 简直是提升用户体验的利器。

`FORMAT()` 函数的基本语法

FORMAT() 函数的基本语法如下：

FORMAT(number, decimal_places, locale)

让我们逐一解释这些参数：

number: 这是要格式化的数值，可以是任何数值类型的表达式，例如 INT、DECIMAL、FLOAT 等。
decimal_places: 这是一个整数，指定要保留的小数位数。如果省略，默认值为 0。
locale: 这是一个字符串，指定用于格式化的区域设置。区域设置会影响千位分隔符和小数点符号的显示方式。如果省略，将使用服务器的默认区域设置。常见的区域设置包括 ‘en_US’ (美国英语), ‘de_DE’ (德国), ‘fr_FR’ (法国), ‘zh_CN’ (中国) 等。

`FORMAT()` 函数的用法示例

我们通过一些例子来具体了解 FORMAT() 函数的用法。假设我们有一个 products 表，包含 id (INT), name (VARCHAR), price (DECIMAL(10, 2)) 字段。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (name, price) VALUES
('Product A', 1234.567),
('Product B', 9876543.21),
('Product C', 123),
('Product D', 0.99);

基本格式化，保留两位小数，使用默认区域设置：

SELECT id, name, FORMAT(price, 2) AS formatted_price
FROM products;

假设服务器的默认区域设置为 en_US，则查询结果如下：

id	name	formatted_price
1	Product A	1,234.57
2	Product B	9,876,543.21
3	Product C	123.00
4	Product D	0.99

注意：FORMAT() 函数返回的是字符串类型。

指定区域设置：

SELECT id, name, FORMAT(price, 2, 'de_DE') AS formatted_price
FROM products;

查询结果如下：

id	name	formatted_price
1	Product A	1.234,57
2	Product B	9.876.543,21
3	Product C	123,00
4	Product D	0,99

可以看到，使用 de_DE 区域设置后，千位分隔符变成了句点（.），小数点符号变成了逗号（,）。

不指定小数位数：

SELECT id, name, FORMAT(price, 0) AS formatted_price
FROM products;

查询结果如下（假设区域设置为 en_US）：

id	name	formatted_price
1	Product A	1,235
2	Product B	9,876,543
3	Product C	123
4	Product D	1

注意，当 decimal_places 为 0 时，会对数值进行四舍五入。

格式化常量值：

SELECT FORMAT(1234567.89, 2); -- 结果：1,234,567.89 (假设区域设置为 en_US)
SELECT FORMAT(1234567.89, 2, 'fr_FR'); -- 结果：1 234 567,89

结合 COALESCE() 处理 NULL 值：

如果 price 字段可能包含 NULL 值，可以使用 COALESCE() 函数将其替换为 0 或其他默认值，然后再进行格式化。

SELECT id, name, FORMAT(COALESCE(price, 0), 2) AS formatted_price
FROM products;

这样可以避免 FORMAT() 函数处理 NULL 值时可能出现的问题。

`FORMAT()` 函数的注意事项

返回值为字符串： FORMAT() 函数始终返回字符串类型，即使 decimal_places 为 0。因此，如果需要对格式化后的数值进行数学运算，需要先将其转换为数值类型，例如使用 CAST() 或 CONVERT() 函数。
性能影响： 在处理大量数据时，FORMAT() 函数可能会对性能产生一定的影响。因为它需要进行字符串格式化操作。因此，应该谨慎使用，避免在不必要的情况下使用。
区域设置的依赖性： FORMAT() 函数的输出结果依赖于指定的区域设置。不同的区域设置会产生不同的格式化结果。因此，在应用程序中，应该根据用户的区域设置动态地选择合适的区域设置，以确保输出结果符合用户的期望。
四舍五入： 当 decimal_places 小于实际小数位数时，FORMAT() 函数会对数值进行四舍五入。

`FORMAT()` 函数与其他格式化方法的比较

除了 FORMAT() 函数，MySQL 还提供了其他的格式化方法，例如 CONVERT() 函数和自定义函数。

CONVERT() 函数：

CONVERT() 函数可以将数值转换为字符串，并指定格式。但是，CONVERT() 函数的功能相对简单，不如 FORMAT() 函数灵活。例如，CONVERT() 函数不能直接添加千位分隔符。

SELECT CONVERT(1234.56, CHAR); -- 结果：1234.56

要使用 CONVERT() 函数添加千位分隔符，需要编写复杂的 SQL 语句或自定义函数。

自定义函数：

可以编写自定义函数来实现更复杂的格式化需求。但是，编写自定义函数需要一定的编程经验，并且可能会增加代码的维护成本。

相比之下，FORMAT() 函数使用简单，功能强大，可以满足大多数常见的数字格式化需求。因此，在大多数情况下，FORMAT() 函数是数字格式化的首选方法。

实际应用场景

FORMAT() 函数在实际应用中有很多用途，以下是一些常见的例子：

财务报表： 在财务报表中，通常需要将金额格式化为带有千位分隔符和货币符号的字符串。可以使用 FORMAT() 函数轻松地实现这个需求。
```
SELECT FORMAT(revenue, 2, 'en_US') AS formatted_revenue
FROM sales_data;
```
数据分析： 在数据分析中，经常需要将数值格式化为易于阅读的形式。例如，可以将百分比格式化为带有百分号的字符串。
```
SELECT CONCAT(FORMAT(percentage * 100, 2), '%') AS formatted_percentage
FROM performance_data;
```
电子商务网站： 在电子商务网站上，通常需要将商品价格格式化为带有货币符号和千位分隔符的字符串。
```
SELECT CONCAT('$', FORMAT(price, 2, 'en_US')) AS formatted_price
FROM products;
```
仪表盘： 在仪表盘中，可以使用 FORMAT() 函数将数据格式化为易于理解的形式，提高仪表盘的可读性。

使用 `FORMAT()` 函数的进阶技巧

动态区域设置： 可以使用变量或存储过程来动态地设置区域设置。
```
SET @locale = 'fr_FR';
SELECT FORMAT(1234.56, 2, @locale);
```

结合 CASE 语句： 可以使用 CASE 语句根据不同的条件选择不同的格式化方式。

SELECT
    CASE
        WHEN price > 1000 THEN FORMAT(price, 2, 'en_US')
        ELSE FORMAT(price, 2, 'de_DE')
    END AS formatted_price
FROM products;

自定义格式化函数 (不推荐，优先使用 FORMAT):

虽然 FORMAT 函数已经很强大，但有时你可能需要更精细的控制。在这种情况下，你可以创建一个自定义函数。请注意，这通常是不必要的，因为 FORMAT 已经足够灵活。这里只是为了演示目的。

DELIMITER //

CREATE FUNCTION format_number_custom(number DECIMAL(20,2), decimals INT, thousands_sep VARCHAR(1), decimal_sep VARCHAR(1))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
  DECLARE formatted_number VARCHAR(255);
  DECLARE integer_part VARCHAR(255);
  DECLARE fractional_part VARCHAR(255);
  DECLARE i INT;
  DECLARE k INT;

  -- 处理 NULL 值
  IF number IS NULL THEN
    RETURN NULL;
  END IF;

  -- 格式化小数部分
  IF decimals > 0 THEN
    SET fractional_part = SUBSTRING(CAST(ROUND(number, decimals) AS CHAR), LENGTH(CAST(ROUND(number, decimals) AS CHAR)) - decimals + 1, decimals);
    SET fractional_part = CONCAT(decimal_sep, fractional_part);
  ELSE
    SET fractional_part = '';
  END IF;

  -- 格式化整数部分
  SET integer_part = FLOOR(number);
  SET formatted_number = '';
  SET i = LENGTH(integer_part);
  SET k = 0;

  WHILE i > 0 DO
    SET formatted_number = CONCAT(SUBSTRING(integer_part, i, 1), formatted_number);
    SET i = i - 1;
    SET k = k + 1;
    IF k = 3 AND i > 0 THEN
      SET formatted_number = CONCAT(thousands_sep, formatted_number);
      SET k = 0;
    END IF;
  END WHILE;

  SET formatted_number = CONCAT(formatted_number, fractional_part);
  RETURN formatted_number;
END //

DELIMITER ;

-- 使用示例
SELECT format_number_custom(1234567.89, 2, '.', ','); -- 结果: 1.234.567,89
SELECT format_number_custom(9876.54, 0, ',', '.');  -- 结果: 9,877 (四舍五入)

代码解释:

DELIMITER // 和 DELIMITER ;: 用于定义和重置分隔符，因为函数体中包含分号。
CREATE FUNCTION ... RETURNS ... DETERMINISTIC: 创建一个确定性函数，意味着对于相同的输入，它总是返回相同的输出。
DECLARE: 声明函数内部使用的变量。
IF number IS NULL THEN RETURN NULL; END IF;: 处理 NULL 输入的情况。
ROUND(number, decimals): 将数字四舍五入到指定的小数位数。
SUBSTRING(..., LENGTH(...) - decimals + 1, decimals): 提取小数部分。
FLOOR(number): 获取数字的整数部分。
WHILE 循环: 从整数部分的末尾开始，每三位添加一个千位分隔符。
CONCAT: 连接字符串。

重要提示:

自定义函数会增加代码的复杂性。在大多数情况下，FORMAT 函数已经足够满足需求。
确保你的自定义函数处理了所有可能的输入情况，包括 NULL 值、负数等。

总结

FORMAT() 函数是 MySQL 中一个非常实用的函数，可以帮助我们格式化数字，添加千位分隔符，从而使查询结果更加易读和美观。它使用简单，功能强大，可以满足大多数常见的数字格式化需求。在实际应用中，FORMAT() 函数可以用于财务报表、数据分析、电子商务网站、仪表盘等多个场景。掌握 FORMAT() 函数的使用方法，可以显著提高数据呈现的质量和用户体验。

灵活使用，提升数据可读性

FORMAT() 函数通过格式化数字，让数据更易于理解和分析，在数据库应用开发中扮演着重要角色。掌握它，能帮助我们编写出更清晰、更友好的 SQL 查询。

MySQL FORMAT() 函数：数字格式化的利器

FORMAT() 函数的基本语法

FORMAT() 函数的用法示例

FORMAT() 函数的注意事项

FORMAT() 函数与其他格式化方法的比较