【MySQL】物化视图（2）

-- 定义存储过程来定时执行更新数据
CREATE PROCEDURE refresh_mv_now ()
BEGIN
  -- 清空表里边的数据
  TRUNCATE TABLE record_data;
  -- 更新记录表里边的聚合查询数据
  insert into record_data select * from record_view;
END;
call refresh_mv_now ();

CREATE TRIGGER purchase_mv_trigger_ins AFTER INSERT ON purchase_order FOR EACH ROW
BEGIN
  SET @old_pro_price_sum = 0;
  SET @old_pro_price_avg = 0;
  SET @old_pro_num_sum = 0;
  SET @old_pro_num_avg = 0;
  SET @old_pro_count = 0;
  # 查询出之前的信息然后记录到不同的变量中
  SELECT
    IFNULL(pro_price_sum,0),IFNULL(pro_price_avg,0),
    IFNULL(pro_num_sum,0),IFNULL(pro_num_avg,0),
    IFNULL(pro_count,0)
  FROM
    purchase_mv
  WHERE
    supply_name = NEW.supply_name
  INTO
    @old_pro_price_sum,@old_pro_price_avg,@old_pro_num_sum,@old_pro_num_avg,@old_pro_count;
  # 然后再去计算更新操作之后的内容
  SET @new_pro_count = @old_pro_count + 1;
  SET @new_pro_price_sum = @old_pro_price_sum + NEW.pro_price;
  SET @new_pro_price_avg = @new_pro_price_sum / @new_pro_count;
  SET @new_pro_num_sum = @old_pro_num_sum + NEW.pro_num;
  SET @new_pro_num_avg = @new_pro_num_sum / @new_pro_count;
  REPLACE INTO
    purchase_mv
  VALUES(
    NEW.supply_name, @new_pro_count,
    @new_pro_price_sum, IFNULL(@new_pro_price_avg, 0),
    @new_pro_num_sum, IFNULL(@new_pro_num_avg, 0)
  );
END;