MySQL 5.7未提供原生物化视图支持，通过触发器结合中间表的方式可以模拟物化视图的核心能力，实现预计算数据的自动同步更新。这种方式适合查询频繁、数据更新频率不高的场景，能有效降低复杂查询的资源消耗。
实现原理
物化视图的核心是将查询的结果持久化存储，当源表数据发生变化时自动更新存储的结果。模拟实现的逻辑分为三部分：首先创建中间表存储预计算的聚合结果，然后针对源表的增删改操作创建对应的触发器，最后在触发器中编写逻辑同步更新中间表的数据。
具体实现步骤
1. 准备源表
假设我们有一个订单明细表order_items，存储每个订单的商品明细，结构如下：
-- 创建源表
CREATE TABLE order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
我们需要模拟一个物化视图，统计每个订单的总金额，避免每次查询都执行聚合计算。
2. 创建中间表
中间表用于存储预计算的每个订单总金额，相当于物化视图的存储载体：
-- 创建中间表，存储每个订单的总金额
CREATE TABLE order_total_amount (
order_id INT PRIMARY KEY,
total_amount DECIMAL(10,2) NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
3. 创建同步触发器
需要针对源表的插入、更新、删除操作分别创建触发器，保证中间表数据和源表一致。
插入操作触发器
当order_items表新增记录时，更新对应订单的总金额：
DELIMITER //
CREATE TRIGGER tr_order_items_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
-- 插入或更新中间表的订单总金额
INSERT INTO order_total_amount (order_id, total_amount)
VALUES (NEW.order_id, NEW.quantity * NEW.price)
ON DUPLICATE KEY UPDATE
total_amount = total_amount + NEW.quantity * NEW.price;
END //
DELIMITER ;
更新操作触发器
当order_items表的记录被修改时，先减去旧的金额，再加上新的金额：
DELIMITER //
CREATE TRIGGER tr_order_items_update
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
-- 先减去旧记录的金额
UPDATE order_total_amount
SET total_amount = total_amount - OLD.quantity * OLD.price
WHERE order_id = OLD.order_id;
-- 再加上新记录的金额
INSERT INTO order_total_amount (order_id, total_amount)
VALUES (NEW.order_id, NEW.quantity * NEW.price)
ON DUPLICATE KEY UPDATE
total_amount = total_amount + NEW.quantity * NEW.price;
END //
DELIMITER ;
删除操作触发器
当order_items表的记录被删除时，从对应订单的总金额中减去该记录的金额：
DELIMITER //
CREATE TRIGGER tr_order_items_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
UPDATE order_total_amount
SET total_amount = total_amount - OLD.quantity * OLD.price
WHERE order_id = OLD.order_id;
-- 如果订单下没有明细了，删除中间表的记录
DELETE FROM order_total_amount
WHERE order_id = OLD.order_id
AND total_amount <= 0;
END //
DELIMITER ;
功能验证
插入测试数据验证同步效果：
-- 插入测试数据 INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 101, 2, 50.00), (1, 102, 1, 30.00), (2, 103, 3, 20.00); -- 查询中间表数据 SELECT * FROM order_total_amount;
此时中间表会生成两条记录，order_id为1的总金额是130.00，order_id为2的总金额是60.00，符合预期计算结果。
修改order_id为1的第一条记录的数量：
UPDATE order_items SET quantity = 3 WHERE id = 1; SELECT * FROM order_total_amount WHERE order_id = 1;
查询结果会显示order_id为1的总金额更新为180.00，说明更新触发器生效。
注意事项
- 触发器的逻辑需要覆盖所有源表的数据变更场景，避免中间表数据和源表不一致。
- 如果源表数据量非常大，触发器的执行会增加写操作的开销，需要评估性能影响。
- 中间表需要建立合适的索引，比如本例中的order_id主键索引，提升查询效率。
- 这种方式模拟的物化视图不支持原生物化视图的查询重写功能，查询时需要直接访问中间表。