如何在MySQL中编写自定义函数将数值格式化为带美元符号、千分位及两位小数的货币格式

前置检查 #

创建函数前先确认MySQL允许创建自定义函数，若执行创建逻辑时报权限错误，先执行以下语句临时开启权限：

SET GLOBAL log_bin_trust_function_creators = 1;

要永久生效需在MySQL配置文件my.cnf中添加log_bin_trust_function_creators = 1配置项后重启服务。

函数创建代码 #

直接执行以下SQL完成DOLLAR_FMT函数创建：

DELIMITER //
CREATE FUNCTION DOLLAR_FMT(salary DECIMAL(12,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    -- 空值处理，可根据需求调整返回值
    IF salary IS NULL THEN
        RETURN '$0.00';
    END IF;
    -- 内置FORMAT函数直接生成千分位+两位小数格式，拼接美元符号即可
    RETURN CONCAT('$', FORMAT(salary, 2));
END //
DELIMITER ;

功能说明 #

• 输入参数DECIMAL(12,2)适配最高999,999,999,999.99的数值场景，有更大数值需求可自行调整参数长度
• 自动对输入数值做四舍五入保留两位小数，自带千分位分隔符
• 兼容空值输入，默认返回$0.00，可修改IF分支逻辑调整空值返回结果

使用示例 #

创建完成后直接调用即可实现你需要的查询效果：

SELECT FIRST_NAME,LAST_NAME, DOLLAR_FMT(SALARY) AS SALARY
FROM EMPLOYEES;

输出示例：SALARY字段值为123456.7时，返回结果为$123,456.70；值为99999.999时返回$100,000.00。

内容的提问来源于stack exchange，提问作者Mr_question