php - MySQL 过程 - 增量地重新计算行

Question

我有一项非常微不足道的计算预算条目(收入/结果/余额)的任务。可以有数千个条目，我可以在中间更改其中的任何一个。结果，必须重新计算所有以后的分录余额。

现在我在 PHP 中通过遍历所有条目的数组并更新更改的行来完成此操作。这样会花费太多时间——我的服务器停止响应几分钟。

我想它的发生是因为 PHP 为每个条目更新调用 MySQL，尽管对于 PHP 本身来说，数组迭代和重新计算的这个任务是相当便宜的。我认为必须有一种方法可以将此任务交给 MySQL，因此它会自己进行迭代/重新计算/更新，这也可能很便宜。

我根本不是 MySQL 专家，但我听说存储过程可能是解药。

这是我的 MySQL (5.5.33) 表:

CREATE TABLE `entry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `is_income` tinyint(1) NOT NULL DEFAULT '0',
  `income` decimal(20,2) DEFAULT NULL,
  `outcome` decimal(20,2) DEFAULT NULL,
  `balance` decimal(20,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

这是我的 PHP (5.3.27):

//...
//$DB is a class for operating DB
$entries = $DB->get_all('entry'); //retrieves all entries from 'entry' table, sorted by date
$balance = 0;
foreach ($entries as $e) {
    if ($e['is_income']) {
        $balance += $e['income'];
    } else {
        $balance -= $e['outcome'];
    }
    if ($balance <> $e['balance']) {
        $e1 = $e;
        $e1['balance'] = $balance;
        $DB->update('entry', $e1); //update the row by doing query('UPDATE `entry` ... WHERE id=' . $entry_id);
    }
}

你能给我指明正确的方向吗？谢谢!

Answer 1

我认为您可以在单个 SQL UPDATE 查询中执行此操作，无需任何过程。

UPDATE entry AS e1
JOIN (SELECT * FROM entry ORDER BY date) AS e2 ON e1.id = e2.id
CROSS JOIN (SELECT @balance := 0) AS var
SET e1.balance = (@balance := @balance + IF(e2.is_income, e2.income, -e2.outcome))

用户变量 @balance 与 PHP 变量 $balance 的作用相同。需要子查询，因为 MySQL 不允许在多表 UPDATE 查询中使用 ORDER BY，因此您需要加入一个在日期中生成 ID 的子查询订单。