php - 具有计算和舍入的 MassUpdate
问题描述
我有一个表列,我想用 2 个表字段和一个绑定参数进行计算。
id | transaction_id | local_price | supplier_price | discrepancy
我要计算
差异 = (supplier_price - local_price) / $count
(并且supplier_price
值local_price
在表中。discrepancy
应该在 INT 中。
所以我写了这个php函数
$cases = [];
$ids = [];
$params = [];
$discrepancyParam = [];
foreach ($array as $key => $value) {
$transactionId = $value['transaction_id'];
$cases[] = "WHEN {$transactionId} then ?";
$ids[] = $transactionId;
$params[] = "ceil((supplier_price-local_price)/" . $value['count'] . ")";
}
$ids = implode(',', $ids);
$cases = implode(' ', $cases);
\DB::update("UPDATE accounting SET `discrepancy` = CASE `transaction_id` {$cases} END WHERE `transaction_id` in ({$ids})", $params);
这会产生
更新会计 SET
discrepancy
= CASEtransaction_id
WHEN 37801 then ceil((supplier_price-local_price)/27) WHEN 37874 then ceil((supplier_price-local_price)/21) ... END WHEREtransaction_id
in (37801,37874,...)
但我收到一个错误:
一般错误:1366 不正确的整数值:'ceil((supplier_price-local_price)/27)' for column 'discrepancy' at row 14195
我认为我的查询做错了,但我不知道是什么。
更新
我从每个数组中只取了 1 个元素,它显示了这个查询:
一般错误:1366 不正确的整数值:第 14195 行的列 'discrepancy' 的'ceil(
ups_price
- )'(SQL:UPDATE accounting SET = CASE WHEN 37801 then ceil( - ) END WHERE in (37801))local_price
discrepancy
transaction_id
supplier_price
local_price
transaction_id
所以我拿了它并在 MySQLWorkbench 中运行
UPDATE accounting SET `discrepancy` = CASE `transaction_id` WHEN 37801 then ceil(`supplier_price`-`local_price`) END WHERE `transaction_id` in (37801)
把它放在 MySQLWorkbench 中是可行的,但在我的代码中却没有。
我怀疑这应该与我在 php 中调用数据库字段的方式有关
更新 2:
即使这也行不通,尽管supplier_price
肯定是 INT。它肯定是在 php 中调用表字段
These throw same error:
$params[] = "supplier_price";
$params[] = "`supplier_price`";
解决方案
您正在尝试将公式绑定到准备好的查询占位符,这是不允许的;您只能绑定值,因此您ceil((supplier_price-local_price)/21)
被视为字符串,这是整数列的非法值。如果您想防止来自此查询的注入,您只需要对transaction_id
andcount
值使用占位符,因此您必须在查询中包含大部分公式。这应该有效:
$params = array();
foreach ($array as $key => $value) {
$transactionId = $value['transaction_id'];
$cases[] = "WHEN ? then ceil((supplier_price-local_price)/?)";
$ids[] = '?';
$transaction_ids[] = $transactionId;
array_push($params, $transactionId, $value['count']);
}
$params = array_merge($params, $transaction_ids);
$ids = implode(',', $ids);
$cases = implode(' ', $cases);
\DB::update("UPDATE accounting SET `discrepancy` = CASE `transaction_id` {$cases} END WHERE `transaction_id` in ({$ids})", $params);