SQL自定义函数计算订单金额报错及调用函数统计客户订单总和问题

你遇到的创建函数报错最常见的是两类原因，按以下步骤排查解决：

• 权限与配置问题：如果报错信息提示二进制日志相关、无SUPER权限，可通过两种方式解决：
  方式1：临时开启函数创建信任权限，执行SQL命令：

  SET GLOBAL log_bin_trust_function_creators = 1;
  

  如需永久生效，需在MySQL配置文件（Linux为my.cnf、Windows为my.ini）的[mysqld]段落添加log_bin_trust_function_creators=1，之后重启MySQL服务。
  方式2：在函数定义中添加DETERMINISTIC声明，标识该函数为确定性函数（相同输入永远返回相同输出），更安全合规。
• 优化函数定义，添加重名判断避免已存在同名函数报错，修正后的完整函数代码如下：

DELIMITER $$
DROP FUNCTION IF EXISTS invoices.orderAmount$$
CREATE FUNCTION invoices.orderAmount(quantity INT, price INT) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE orderTotal INT;
  SET orderTotal = quantity * price;
  RETURN orderTotal;
END$$
DELIMITER ;

你原有查询语句存在两处问题：

1. 使用sum聚合函数时，无需查询非聚合字段price和quantity，若MySQL开启ONLY_FULL_GROUP_BY配置会直接报错
2. 调用函数时参数顺序与定义相反，虽然乘法交换律下结果一致，但后续修改函数逻辑时容易引发故障

修正后的查询SQL #

SELECT sum(invoices.orderAmount(quantity, price)) AS CustomersTotalBill 
FROM `invoices` 
WHERE purchaseId = ? AND status='ordered' AND OrderCancel='NO';

注意：原PHP代码直接拼接$purchaseId存在SQL注入风险，建议使用预处理语句传参，示例（PDO写法）如下：

// $pdo为已初始化的数据库连接实例
$totalOrderQuery = "SELECT sum(invoices.orderAmount(quantity, price)) AS CustomersTotalBill FROM `invoices` WHERE purchaseId = :purchaseId AND status='ordered' AND OrderCancel='NO'";
$stmt = $pdo->prepare($totalOrderQuery);
$stmt->bindParam(':purchaseId', $purchaseId, PDO::PARAM_INT);
$stmt->execute();
$customerTotal = $stmt->fetchColumn();

可选优化方案 #

如果你的订单金额计算仅需要数量乘单价，无需叠加其他逻辑（如运费、折扣等），可以不用自定义函数，直接在SQL中计算，性能更优：

SELECT sum(quantity * price) AS CustomersTotalBill 
FROM `invoices` 
WHERE purchaseId = ? AND status='ordered' AND OrderCancel='NO';

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