MySQL：使用触发器模拟物化视图

分手后的思念是犯贱 2022-04-23 02:18 348阅读 0赞

1、什么叫做物化视图

*  不是基于基表的虚表
 *  根据基表实际存在的实表
 *  预先计算并保存耗时较多的SQL操作结果（如多表链接(join)或者group by等）

2、使用触发器模拟物化视图

*  创建测试表

-- 创建Orders表
    mysql> CREATE TABLE IF NOT EXISTS Orders (
        order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        product_name VARCHAR(30) NOT NULL,
        price DECIMAL(8 , 2 ) NOT NULL,
        amount SMALLINT NOT NULL,
        PRIMARY KEY (order_id)
    );
    
    -- 插入测试数据
    mysql> insert into Orders values 
    (null, 'cpu', 135.5 ,1),
    (null, 'memory', 48.2, 3),
    (null, 'cpu', 125.6, 3),
    (null, 'cpu', 105.3, 4);
    
    mysql> select * from  Orders;
    +----------+--------------+--------+--------+
    | order_id | product_name | price  | amount |
    +----------+--------------+--------+--------+
    |        1 | cpu          | 135.50 |      1 |
    |        2 | memory       |  48.20 |      3 |
    |        3 | cpu          | 125.60 |      3 |
    |        4 | cpu          | 105.30 |      4 |
    +----------+--------------+--------+--------+

*  建立一个用来物化视图的表

-- 建立一个模拟物化视图的表（即用这张表来模拟物化视图）
    mysql> CREATE TABLE Orders_MV (
        product_name VARCHAR(30) NOT NULL,
        price_sum DECIMAL(8 , 2 ) NOT NULL,
        amount_sum INT NOT NULL,
        price_avg FLOAT NOT NULL,
        orders_cnt INT NOT NULL,
        UNIQUE INDEX (product_name)
    );
    
    -- 通过Orders表的数据，将测试数据初始化到Orders_MV表中
    mysql> INSERT INTO Orders_MV
         SELECT product_name, SUM(price),SUM(amount), AVG(price), COUNT(*)
    		FROM Orders
    		GROUP BY product_name;
    
    mysql> select * from Orders_MV;
    +--------------+-----------+------------+-----------+------------+
    | product_name | price_sum | amount_sum | price_avg | orders_cnt |
    +--------------+-----------+------------+-----------+------------+
    | cpu          |    366.40 |          8 |   122.133 |          3 |
    | memory       |     48.20 |          3 |      48.2 |          1 |
    +--------------+-----------+------------+-----------+------------++-

*  创建触发器

delimiter //
    
    CREATE TRIGGER tgr_Orders_insert -- 创建触发器为tgr_Orders_insert
    	AFTER INSERT ON Orders  -- 触发器是INSERT类型的，且作用于Orders表
    	FOR EACH ROW
    BEGIN
    	SET @old_price_sum := 0;  -- 设置临时存放Orders_MV表(模拟物化视图)的字段的变量
    	SET @old_amount_sum := 0;
    	SET @old_price_avg := 0;
    	SET @old_orders_cnt := 0;
    	SELECT   -- select ... into ... 在更新Orders_MV之前，将Orders_MV中对应某个产品的信息写入临时变量
    		IFNULL(price_sum, 0),
    		IFNULL(amount_sum, 0),
    		IFNULL(price_avg, 0),
    		IFNULL(orders_cnt, 0)
    	FROM
    		Orders_MV
    	WHERE
    		product_name = NEW.product_name INTO @old_price_sum , @old_amount_sum , @old_price_avg , @old_orders_cnt;
    
    	SET @new_price_sum = @old_price_sum + NEW.price; -- 累加新的值
    	SET @new_amount_sum = @old_amount_sum + NEW.amount;
    	SET @new_orders_cnt = @old_orders_cnt + 1;
    	SET @new_price_avg = @new_price_sum / @new_orders_cnt ;
    
        REPLACE INTO Orders_MV
    			VALUES(NEW.product_name, @new_price_sum,
    				   @new_amount_sum, @new_price_avg, @new_orders_cnt );
       -- REPLACE 将对应的物品（唯一索引）的字段值替换new_xxx的值
    END;//
    
    delimiter ;

*  测试

mysql> insert into Orders values (null, 'memory', 47.9, 5);
    
    mysql> mysql> select * from Orders_MV;
    +--------------+-----------+------------+-----------+------------+
    | product_name | price_sum | amount_sum | price_avg | orders_cnt |
    +--------------+-----------+------------+-----------+------------+
    | cpu          |    366.40 |          8 |   122.133 |          3 |
    | memory       |     96.10 |          8 |     48.05 |          2 |  -- 数量自动增加了1，价格也发生了变化
    +--------------+-----------+------------+-----------+------------+
    
    mysql> insert into Orders values (null, 'ssd', 299, 3);
    
    mysql> select * from Orders_MV;
    +--------------+-----------+------------+-----------+------------+
    | product_name | price_sum | amount_sum | price_avg | orders_cnt |
    +--------------+-----------+------------+-----------+------------+
    | cpu          |    366.40 |          8 |   122.133 |          3 |
    | memory       |     96.10 |          8 |     48.05 |          2 | -- 数量自动增加了1，价格也发生了变化
    | ssd          |    299.00 |          3 |       299 |          1 | -- 新增加的ssd产品
    +--------------+-----------+------------+-----------+------------+