根据触发器和存储过程更新汇总表

Question

我有一个典型的基于 LAMP 的站点 + Zend Framework，其中有一个基表和一个汇总表。汇总表用于显示报告中的数据。

基表 -

ID | Status
1 | 1
2 | 1
3 | 2
4 | 2
5 | 1
6 | 1

汇总表 -

Status | Count
1 | 4
2 | 2

基表平均每天会更改（插入、更新、删除）20 次。

目前，我正在使用触发器调用存储过程，该存储过程将根据基表更新汇总表。

这是存储过程。

CREATE PROCEDURE UpdateSummary()

BEGIN

UPDATE summary a

INNER JOIN

(SELECT status, count(*) c from base group by status) b

ON a.status = b.status

SET a.count = b.c;

END

我有 3 个触发器（每个触发器一个 - 插入、删除和更新）。我在下面单独展示了插入样本。其他的也与此类似。

CREATE TRIGGER S_T_TRIGGER_I

AFTER INSERT ON base

FOR EACH ROW

CALL UpdateSummary();

我希望汇总表始终更新为最新值。 使用像这样的触发器和存储过程是最好的方法，或者是否有一种优雅的方法来做到这一点？

Answer 1

好吧，您正在一遍又一遍地重新查询数据库以获取您已经知道的数据。

为什么不只更新摘要并仅进行更改。

DELIMITER $

CREATE TRIGGER ai_base_each AFTER INSERT ON base FOR EACH ROW
BEGIN
  INSERT INTO summary (status, count) VALUES (NEW.status,1)
  ON DUPLICATE KEY UPDATE
    SET count = count + 1;
END $

CREATE TRIGGER ad_base_each AFTER DELETE ON base FOR EACH ROW
BEGIN
  UPDATE summary s
    SET s.count = s.count - 1 
    WHERE s.status = OLD.status;
END $


CREATE TRIGGER au_base_each AFTER UPDATE ON base FOR EACH ROW
BEGIN
  UPDATE summary s
    SET s.count = s.count - 1 
    WHERE s.status = OLD.status;

  INSERT INTO summary (status, count) VALUES (NEW.status,1)
  ON DUPLICATE KEY UPDATE
    SET count = count + 1;
END $

DELIMITER ;

这会更快、更简洁、更优雅。

Answer 2

为什么不使用这样的视图：

 CREATE VIEW Summary AS 
      SELECT status, count(*)
      FROM Base
      GROUP BY status;

每次需要时，只需执行：

SELECT * 
FROM Summary 

您就会实时获得结果（每次调用都会重新计算）。

视图的使用方式与 Zend Framework 中使用表的方式相同。只是您需要按照解释明确指定主键 这里