在Mysql或PHP中处理十进制精度

Question

我有一个MySQL表，即estimate_item，它包含几个包含固定精度的十进制值的列。这是我的桌子结构。

tableName : estimate_item

​

​

在使用PHP检索记录时，我使用MySQL的函数进行一些基本计算，我使用的SQL查询如下。

SELECT 
    COUNT(ei.item_id) as total_item,
    SUM(ei.quantity) as total_quantity,
    SUM(ei.number_of_carton) as total_carton,
    SUM(ei.number_of_carton * ei.cbm_per_carton) as total_cbm,
    SUM(ei.quantity * ei.cost) as total_cost,
    SUM(ei.quantity * ei.rate) as total_rate,
    e.commission_amount,
    SUM(ei.quantity * ei.rate) + e.commission_amount as total_amount
FROM
    ai_estimate e
LEFT JOIN
    ai_estimate_item ei ON ei.estimate_id = e.id
WHERE
    ei.estimate_id = ?

例如，上面的查询返回结果。

+------------+----------------+--------------+-----------+------------+------------+-------------------+--------------+
| total_item | total_quantity | total_carton | total_cbm | total_cost | total_rate | commission_amount | total_amount |
+------------+----------------+--------------+-----------+------------+------------+-------------------+--------------+
|          2 |            120 |          807 | 1122.6440 |     2.7500 |   137.5000 |           1500.00 |    1637.5000 |
+------------+----------------+--------------+-----------+------------+------------+-------------------+--------------+

由于下面的列包含4个精度值total_cbm_total, total_cost, total_rate, total_amount，所以我希望以这种方式对所有四个列的值进行汇总。

( a)如果值为1122.6440，则将其四舍五入为1122.644

( b)如果值为2.7500，则将其舍入到2.75

( c)如果值为137.5000，则将其舍入到137.50

( d)如果值为1200.0000，则将其舍入到1200.00

也就是说，我希望这些值包含两个精度的最小值，根据这些值可以达到3或4的精度。

我能在MySQL中直接做到这一点吗？还是PHP的方式？

谢谢和问候。

Answer 1

如果在MySQL中这样做，我的猜测是，当.结束。

所以我会用PHP来做这个。

function reprecise($value)
{
    $two_digit = number_format($value, 2);
    $three_digit = number_format($value, 3);
    return (float)$two_digit == (float)$three_digit ? $two_digit : $three_digit;
}

这使用了一个技巧--首先用2位和3位数字计算表示，然后检查它们是否是相同的浮点数。如果是，则三位数版本的最后一位数必须为零，并使用两位数版本。

要扩展到2、3或4位数字，您可以在一个周期中这样做：

function reprecise($value)
{
    $digits = 4;
    for ($rep = number_format($value, $digits--); $digits >= 2; $digits--)
    {
            $new = number_format($value, $digits);
            if ($new != $rep)
                    break;
            $rep = $new;
    }
    return $rep;
}

或者，由于字符串函数很可能比number_format更快：

function reprecise($value)
{
    $digits = 9;
    $base   = strrev(number_format($value, $digits));
    for ($i = 0; $i < $digits-2; $i++)
            if ($base[$i] != '0')
                    break;
    return strrev(substr($base, $i));
}

Answer 2

一种方法是使用regex删除小数位后数字末尾的所有0，然后用两个小数位格式化一个数字并比较两个结果--返回最长(按字符串排列)的结果。虽然这可以在mysql中完成，但在php中要容易得多：

//$input - number to format
//$num - minimum number of decimal places to keep
function reformat($input, $num) {
    $parse = preg_replace('/(\.[1-9]*)0+$/', "$1", $input);
    $withmin = number_format($parse, 2); 
    return strlen($withmin) < strlen($parse) ? $parse : $withmin;
}

Answer 3

在数据库端，请注意，表结构预先定义了列成本、速率和cmb_per_carton的尾随0的数量，在您的例4 0中：

cost DECIMAL 19,4
rate DECIMAL 19,4
cmb_per_carton DECIMAL 19,4

如果对这些字段进行操作，结果自然会有相同的尾随0。

PHP方面，这里有一个简单的函数，我刚刚入侵了它，它将完成您想要的内容：

function getDecimalFormatted($value, $minPrecision) {
    $value = (float) $value;
    return ((round((($value - floor($value))*pow(10, 5)))%1000) > 0) ? $value : number_format($value, $minPrecision, '.', '');
}

用法示例：

$value = "1234.71200";
echo getDecimalFormatted($value, 2)."\n<br>";

$value = "1234.70000";
echo getDecimalFormatted($value, 2)."\n<br>";

这一产出如下：

1234.712
1234.70