MySQL中实现精确有理数运算的最优方案

嗨，我完全懂你在MySQL里处理有理数运算时的精度焦虑——像你举的SUM(1/3)例子，浮点数的精度误差累积起来确实会让人头疼，尤其是系统里大量依赖这类运算的时候。下面给你几个在MySQL里实现高精度有理数运算的实用方案，按需选择就行：

MySQL里的DECIMAL是定点数，和FLOAT/DOUBLE这类浮点数不同，它能精确表示有限十进制有理数，不会出现浮点数的二进制存储误差。

• 定义字段时指定足够的精度和小数位，比如DECIMAL(18,9)（18位总长度，9位小数），根据你的运算需求调整位数；
• 运算时确保参与计算的数值都转成DECIMAL类型，避免隐式转换为浮点数。比如把你例子里的查询改成：

SELECT SUM(TEST) FROM (
    SELECT @N := @N +1 AS rownumber, 
           CAST(1 AS DECIMAL(18,9)) / CAST(3 AS DECIMAL(18,9)) AS TEST 
    FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @N :=0 )dummyRowNums 
    LIMIT 3000
) AS test;

这样计算出来的SUM误差会比浮点数版本小很多，虽然1/3是无限循环小数还是会被截断，但精度完全能满足绝大多数业务场景。

如果你的业务需要100%无误差的有理数运算（比如财务、精密计算场景），可以把分数拆成分子和分母两个整数字段存储，所有运算都用整数操作实现：

• 比如创建表时设计成：

CREATE TABLE rational_numbers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    numerator INT NOT NULL,  -- 分子
    denominator INT NOT NULL CHECK (denominator != 0)  -- 分母（不能为0）
);

• 运算时通过通分、约分等整数操作完成，比如两个分数相加：

-- 计算a(n1/d1) + b(n2/d2)
SELECT 
    (n1*d2 + n2*d1) AS new_numerator,
    d1*d2 AS new_denominator
FROM rational_numbers a
JOIN rational_numbers b ON a.id = 1 AND b.id = 2;

这种方式完全不会有精度损失，因为所有计算都是整数运算，最后需要展示时再转换成DECIMAL或字符串即可。

如果经常需要做复杂的分数运算，可以写MySQL自定义函数封装整数运算逻辑，用起来更方便。比如一个简单的分数加法函数：

DELIMITER //
CREATE FUNCTION add_rational(a_num INT, a_den INT, b_num INT, b_den INT) 
RETURNS DECIMAL(20,10)
DETERMINISTIC
BEGIN
    -- 计算最小公倍数作为公分母
    DECLARE lcm INT;
    DECLARE gcd INT;
    SET gcd = GREATEST(a_den, b_den);
    WHILE LEAST(a_den, b_den) % gcd != 0 DO
        SET gcd = LEAST(a_den, b_den) % gcd;
    END WHILE;
    SET lcm = (a_den * b_den) / gcd;
    
    -- 计算分子和并返回结果
    RETURN (a_num * (lcm / a_den) + b_num * (lcm / b_den)) / lcm;
END //
DELIMITER ;

调用时直接传分子分母就行：SELECT add_rational(1,3,1,2);，结果是精确的0.8333333333。

如果你的系统允许，也可以在应用层用支持精确有理数的工具处理后再存入MySQL，比如Python的fractions模块、Java的BigDecimal类，把运算逻辑放在应用层，MySQL只负责存储最终的精确结果。不过这个方案更适合运算逻辑集中在应用层的场景。

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