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');

-- 创建一个物化视图表
CREATE TABLE sales_summary (
    product VARCHAR(50),
    total_amount DECIMAL(10, 2)
);

-- 创建一个存储过程来刷新物化视图
DELIMITER //
CREATE PROCEDURE refresh_sales_summary()
BEGIN
    TRUNCATE TABLE sales_summary;
    INSERT INTO sales_summary (product, total_amount)
    SELECT product, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product;
END //
DELIMITER ;

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

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