MySQL函数编写遇ERROR 1064(42000)语法错误，求修正方案

我一眼就揪出了触发ERROR 1064的问题——第一个查询折扣率的语句末尾多了一个多余的右括号！除此之外，我还帮你把挤成一团的代码做了格式化，方便后续维护。

具体错误点 #

在第一个SELECT discDetail.DiscountPercentage into discountPer语句的末尾，你多写了一个)：

AND (quantity * unitPrice) <= discDetail.HigherLimit);

只要去掉这个多余的右括号，就能解决核心语法错误。

修正后的完整函数代码 #

DELIMITER //

CREATE FUNCTION calculateNetItemStandardDiscountAmount(
    productId INT,
    quantity INT
) RETURNS DOUBLE
BEGIN
    DECLARE discountPer DOUBLE;
    DECLARE unitPrice DOUBLE;
    DECLARE netItemDiscountAmount DOUBLE;
    DECLARE netItemAmount DOUBLE;

    -- 获取商品单价
    SELECT `SellingUnitPrice` INTO unitPrice 
    FROM `TBL_PRODUCT_MASTER` 
    WHERE `Id` = productId;

    SET netItemAmount = quantity * unitPrice;
    INSERT INTO debugtable SELECT CONCAT('ACTUAL AMOUNT ', netItemAmount);

    -- 按金额方案获取折扣率（已修正多余右括号）
    SELECT discDetail.`DiscountPercentage` INTO discountPer 
    FROM `TBL_DISCOUNT_DETAIL` AS discDetail 
    JOIN `TBL_DISCOUNT` AS disc ON discDetail.DiscountId = disc.Id
    JOIN TBL_PRODUCT_DISCOUNT AS prodDisc ON prodDisc.discountId = disc.Id
    WHERE disc.`DiscountStartDate` < NOW() 
      AND disc.`DiscountEndDate` > NOW() 
      AND disc.`SchemeType` = 'AMOUNT' 
      AND prodDisc.productId = productId 
      AND prodDisc.`IsEnabled` = 1 
      AND disc.`IsEnabled` = 1 
      AND (quantity * unitPrice) >= discDetail.LowerLimit 
      AND (quantity * unitPrice) <= discDetail.HigherLimit;

    INSERT INTO debugtable SELECT CONCAT('STD DISCOUNT PER BY AMOUNT ', discountPer);

    IF (discountPer IS NOT NULL) THEN
        SET netItemDiscountAmount = ((netItemAmount) * (discountPer * 0.01));
        INSERT INTO debugtable SELECT CONCAT('APPLY VOLUME DISCOUNT ', netItemDiscountAmount);
    END IF;

    -- 按数量方案获取折扣率
    SELECT discDetail.`DiscountPercentage` INTO discountPer 
    FROM `TBL_DISCOUNT_DETAIL` AS discDetail 
    JOIN `TBL_DISCOUNT` AS disc ON discDetail.DiscountId = disc.Id
    JOIN TBL_PRODUCT_DISCOUNT AS prodDisc ON prodDisc.discountId = disc.Id
    WHERE disc.`DiscountStartDate` < NOW() 
      AND disc.`DiscountEndDate` > NOW() 
      AND disc.`SchemeType` = 'VOLUME' 
      AND prodDisc.productId = productId 
      AND prodDisc.`IsEnabled` = 1 
      AND disc.`IsEnabled` = 1 
      AND Quantity >= discDetail.LowerLimit 
      AND Quantity <= discDetail.HigherLimit;

    INSERT INTO debugtable SELECT CONCAT('STD DISCOUNT PER BY VOLUME ', discountPer);

    IF (discountPer IS NOT NULL) THEN
        SET netItemDiscountAmount = netItemDiscountAmount + ((netItemAmount) * (discountPer * 0.01));
        INSERT INTO debugtable SELECT CONCAT('APPLY AMOUNT DISCOUNT ', netItemDiscountAmount);
    END IF;

    RETURN netItemDiscountAmount;
END //

DELIMITER ;

额外优化提示 #

1. 我把原来的隐式关联改成了显式JOIN语法，代码逻辑更清晰，也符合现代SQL的写法规范。
2. 注意：如果SELECT INTO语句没有匹配到任何行，MySQL会抛出NO DATA FOUND错误。如果需要兼容这种场景，可以在函数开头添加DECLARE CONTINUE HANDLER FOR NOT FOUND SET discountPer = NULL;来避免函数中断。

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