MySQL存储过程参数自动四舍五入问题求助

大家好，我遇到了一个头疼的问题，希望能得到各位的帮助。我写的这个存储过程里，当我传入比如2387.97这样的小数作为thisAmount参数时，它总会被自动四舍五入成2388，但不管是参数声明、表字段类型，还是我手动用CAST转成DECIMAL(10,2)，都没能解决这个问题。存储过程其他逻辑都正常，就是这个四舍五入的问题卡了我好久，搜了好多资料试了各种方法都没用，实在没办法了来请教各位大佬！

以下是我的存储过程代码：

CREATE DEFINER=`myUserName`@`%` PROCEDURE `billing_addOCIPTrueUp`(IN `thisQuoteID` BIGINT,IN `thisAmount` DECIMAL, IN `thisTable` BLOB, IN `thisDetailTable` BLOB) 
BEGIN 
    SET @thisAmount = CAST(thisAmount AS DECIMAL(10,2)); 
    SET @newJobName = (SELECT JobName FROM Quotes WHERE QuoteID = thisQuoteID); 
    SET @q = CONCAT('INSERT INTO ', thisTable, ' (QuoteID, ClientID, PrimaryClientContactID, PrimaryJobContactID, ParentQuoteID, QuoteDate, BidDueDate, DateSent, QuoteTotal, JobName, `Status`, ApprovalStatus, PmtStatus, WrapType, QuoteType, Retention, ClosingDay) SELECT ', thisTable, '.QuoteID + 1 AS QuoteID,', thisTable, '.ClientID,', thisTable, '.PrimaryClientContactID,', thisTable, '.PrimaryJobContactID,', thisTable, '.ParentQuoteID, DATE(NOW()) AS QuoteDate,', thisTable, '.BidDueDate,', thisTable, '.DateSent,', @thisAmount, ' AS QuoteTotal, "', @newJobName, ' y||y Insurance Deduct" AS JobName, 3 AS `Status`, 2 AS ApprovalStatus,', thisTable, '.PmtStatus,', thisTable, '.WrapType, 14 AS QuoteType,', thisTable, '.Retention,', thisTable, '.ClosingDay FROM ', thisTable, ' WHERE ', thisTable, '.QuoteID LIKE "', thisQuoteID,'00%" ORDER BY ', thisTable, '.QuoteID DESC LIMIT 1; INSERT INTO ', thisDetailTable, '(QuoteID, Heading, Description, Qty, Rate, Total, Misc, Variable, Color, Production, PlanPageNumber, DetailPageNumber, DetailNumber, Draw, DrawApproved, Measured, Mat, Cut, Fab, Coat, Delivered, Installed, ItemType, ItemTypeStyle, InQueue)VALUES( ( SELECT IFNULL((SELECT ' , thisTable, '.QuoteID + 1 AS QuoteID FROM ', thisTable, ' WHERE ', thisTable,'.QuoteID LIKE "', thisQuoteID, '10%" ORDER BY ', thisTable,'.QuoteID DESC LIMIT 1), ', thisQuoteID, '1001) ), "OCIP Insurance Deduction", "", 1,', thisAmount, ',', thisAmount, ', 0.00, 0.00, "n/a", 0, "", "", "", 0, 0, 0, 0, 0, 0, 0, 0, 0, 100, NULL, 0 )'); 
    SELECT CAST(@q AS CHAR(10000) CHARACTER SET utf8) AS text; 
    #PREPARE stmt FROM @q; 
    #EXECUTE stmt; 
    #DEALLOCATE PREPARE stmt; 
END

问题分析 #

我仔细排查了你的代码，找到了两个核心问题：

1. 参数声明的默认精度坑：你把thisAmount参数声明为DECIMAL，但没有指定精度和小数位（比如DECIMAL(10,2)）。在MySQL中，只写DECIMAL的话，默认会使用DECIMAL(10,0)——也就是整数类型！这就导致你传入的2387.97在进入存储过程的瞬间，就被自动四舍五入成2388了，后续的CAST操作也只是把整数转成带两位小数的形式（比如2388→2388.00），根本救不回原来的小数。

2. 细节表插入复用了原参数：你在插入主表时用了处理后的@thisAmount，但插入thisDetailTable时直接用了原参数thisAmount——这个参数已经是被四舍五入后的整数了，所以细节表的Rate和Total字段自然也会是整数。

解决方案 #

针对这两个问题，按以下步骤修改即可：

1. 修正参数声明的精度

把thisAmount的参数类型从DECIMAL改成DECIMAL(10,2)，确保传入的小数能完整保留两位小数：

IN `thisAmount` DECIMAL(10,2)

2. 统一使用处理后的变量

在插入thisDetailTable时，把所有用到thisAmount的地方替换成@thisAmount，确保用的是保留两位小数的正确值。

3. 可选：确认表字段类型

检查目标表中QuoteTotal（主表）、Rate和Total（细节表）的字段类型是否为DECIMAL(10,2)或更高精度的小数类型，避免插入后被截断。

修改后的完整代码 #

CREATE DEFINER=`myUserName`@`%` PROCEDURE `billing_addOCIPTrueUp`(
    IN `thisQuoteID` BIGINT,
    IN `thisAmount` DECIMAL(10,2),  -- 修正参数精度
    IN `thisTable` BLOB, 
    IN `thisDetailTable` BLOB
) 
BEGIN 
    SET @thisAmount = thisAmount;  -- 原参数已经是正确的小数，直接赋值即可（也可以保留CAST做双重保险）
    SET @newJobName = (SELECT JobName FROM Quotes WHERE QuoteID = thisQuoteID); 
    SET @q = CONCAT(
        'INSERT INTO ', thisTable, ' (QuoteID, ClientID, PrimaryClientContactID, PrimaryJobContactID, ParentQuoteID, QuoteDate, BidDueDate, DateSent, QuoteTotal, JobName, `Status`, ApprovalStatus, PmtStatus, WrapType, QuoteType, Retention, ClosingDay) 
         SELECT ', thisTable, '.QuoteID + 1 AS QuoteID,', thisTable, '.ClientID,', thisTable, '.PrimaryClientContactID,', thisTable, '.PrimaryJobContactID,', thisTable, '.ParentQuoteID, 
         DATE(NOW()) AS QuoteDate,', thisTable, '.BidDueDate,', thisTable, '.DateSent,', @thisAmount, ' AS QuoteTotal, 
         ""', @newJobName, ' y||y Insurance Deduct"" AS JobName, 3 AS `Status`, 2 AS ApprovalStatus,', thisTable, '.PmtStatus,', thisTable, '.WrapType, 14 AS QuoteType,', thisTable, '.Retention,', thisTable, '.ClosingDay 
         FROM ', thisTable, ' WHERE ', thisTable, '.QuoteID LIKE ""', thisQuoteID,'00%"" ORDER BY ', thisTable, '.QuoteID DESC LIMIT 1; 
         INSERT INTO ', thisDetailTable, '(QuoteID, Heading, Description, Qty, Rate, Total, Misc, Variable, Color, Production, PlanPageNumber, DetailPageNumber, DetailNumber, Draw, DrawApproved, Measured, Mat, Cut, Fab, Coat, Delivered, Installed, ItemType, ItemTypeStyle, InQueue)
         VALUES( 
             ( SELECT IFNULL((SELECT ' , thisTable, '.QuoteID + 1 AS QuoteID FROM ', thisTable, ' WHERE ', thisTable,'.QuoteID LIKE ""', thisQuoteID, '10%"" ORDER BY ', thisTable,'.QuoteID DESC LIMIT 1), ', thisQuoteID, '1001) ), 
             ""OCIP Insurance Deduction"", """", 1,', @thisAmount, ',', @thisAmount, ', 0.00, 0.00, ""n/a"", 0, """", """", """", 0, 0, 0, 0, 0, 0, 0, 0, 0, 100, NULL, 0 
         )'
    ); 
    SELECT CAST(@q AS CHAR(10000) CHARACTER SET utf8) AS text; 
    -- PREPARE stmt FROM @q; 
    -- EXECUTE stmt; 
    -- DEALLOCATE PREPARE stmt; 
END

这样修改后，传入的2387.97就能完整保留下来，不会再被四舍五入了！

内容来源于stack exchange