物化视图mysql

-- 创建一个示例表
CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    amount DECIMAL(10, 2),
    sale_date DATE
);

-- 插入一些示例数据
INSERT INTO sales (id, product, amount, sale_date) VALUES
(1, 'Product A', 100.00, '2023-01-01'),
(2, 'Product B', 200.00, '2023-01-02'),
(3, 'Product A', 150.00, '2023-01-03');

-- 创建一个存储过程来刷新物化视图
DELIMITER //
CREATE PROCEDURE refresh_materialized_view()
BEGIN
    DROP TABLE IF EXISTS sales_mv;
    CREATE TABLE sales_mv AS
    SELECT product, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product;
END //
DELIMITER ;

-- 调用存储过程来初始化物化视图
CALL refresh_materialized_view();

-- 创建一个触发器，在sales表更新时刷新物化视图
DELIMITER //
CREATE TRIGGER refresh_mv_after_update
AFTER UPDATE ON sales
FOR EACH ROW
BEGIN
    CALL refresh_materialized_view();
END //
DELIMITER ;