Laravel中部分数据库列SUM求和计算异常问题求助

在Laravel项目中使用SUM函数对数据库列求和时，buffalomilk列的计算结果比实际值少1，totalmilk列结果也少1，但a2milk和jerseymilk列求和正常。

执行代码：

$milksalecustomerdata = customermilksale::selectraw('(SUM(buffalomilk)) as "totalbmilk", (SUM(a2milk)) as "totala2milk", (SUM(jerseymilk)) as "totaljmilk", (SUM(totalmilk)) as "totalmilk"')
    ->whereBetween('saledate',['2023-01-31','2023-01-31'])
    ->get();

输出结果：

"totalbmilk" => "36.50"      实际总值应为37.50
"totala2milk" => "26.50"     总值正确
"totaljmilk" => "15.50"      总值正确
"totalmilk" => "78.50"       总值少1

已在SQL Workbench中执行同一查询，结果正确。

排查步骤 #

1. 输出Laravel生成的原生SQL语句 #

直接获取Laravel实际执行的SQL，对比和你在Workbench中执行的语句是否一致：

// 方法1：打印生成的SQL
$sql = customermilksale::selectraw('(SUM(buffalomilk)) as "totalbmilk", (SUM(a2milk)) as "totala2milk", (SUM(jerseymilk)) as "totaljmilk", (SUM(totalmilk)) as "totalmilk"')
    ->whereBetween('saledate',['2023-01-31','2023-01-31'])
    ->toSql();
dd($sql);

// 方法2：通过查询日志查看完整执行语句
DB::enableQueryLog();
$milksalecustomerdata = customermilksale::selectraw(...)
    ->whereBetween(...)
    ->get();
dd(DB::getQueryLog());

将生成的SQL复制到Workbench执行，若结果异常，说明SQL本身有问题；若结果正常，问题出在Laravel的查询处理环节。

2. 检查数据类型与精度 #

• 确认buffalomilk和totalmilk列的数据类型：如果是FLOAT类型，可能存在浮点精度丢失，建议改用DECIMAL(10,2)这类精确小数类型。
• 核对当日所有记录的buffalomilk和totalmilk值，看是否存在某条记录的值被错误截断（比如本该是1.00的记录被处理为0.00）。

3. 排查模型全局作用域 #

查看customermilksale模型的boot方法，确认是否存在全局作用域自动添加了过滤条件，导致部分记录被排除：

protected static function boot()
{
    parent::boot();
    // 检查是否有类似的全局约束
    static::addGlobalScope('filter', function (Builder $builder) {
        $builder->where('status', '=', 1);
    });
}

如果存在全局作用域，临时注释后重新执行查询，看结果是否恢复正常。

4. 验证数据一致性 #

手动计算当日所有buffalomilk记录的总和，确认实际总值确实为37.50；若totalmilk是计算列（由buffalomilk+a2milk+jerseymilk得出），检查每条记录的totalmilk值是否正确，是否存在单条记录少1的情况。

5. 用DB门面执行原生查询 #

跳过Eloquent模型，直接用DB门面执行原生SQL，验证结果：

$milksalecustomerdata = DB::select('SELECT SUM(buffalomilk) as totalbmilk, SUM(a2milk) as totala2milk, SUM(jerseymilk) as totaljmilk, SUM(totalmilk) as totalmilk FROM customermilksale WHERE saledate BETWEEN ? AND ?', ['2023-01-31', '2023-01-31']);
dd($milksalecustomerdata);

若结果正常，问题出在customermilksale模型的配置或逻辑；若结果仍异常，检查数据库连接的精度、字符集等配置。

内容的提问来源于stack exchange，提问作者Rahul W