【已解决】异步物化视图,可以实现UNIQUE KEY 表关联同步吗

Viewed 108
mtmv 2.1

由于表关联比较多，想能不能通过物化视图制作宽表。希望实现主表从表增删改，同步到物化视图中。

CREATE TABLE order_detail
(
	id  BIGINT ,
	order_id  BIGINT ,
	sku_id  BIGINT ,
	sku_name   VARCHAR(255)
)
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES (
	"replication_num" = "1"
);

CREATE TABLE order_info
(
	id  BIGINT ,
	user_name   VARCHAR(255)
)
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES (
    "replication_num" = "1"
);
liu_15
1 Answers

可以支持的：
我这边给出一个简单的demo，您可以参考下：

mysql> CREATE TABLE order_detail
    -> (
    -> id  BIGINT ,
    -> order_id  BIGINT ,
    -> sku_id  BIGINT ,
    -> sku_name   VARCHAR(255)
    -> )
    -> UNIQUE KEY(id)
    -> DISTRIBUTED BY HASH(id) BUCKETS 4
    -> PROPERTIES (
    -> "replication_num" = "1"
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE order_info
    -> (
    -> id  BIGINT ,
    -> user_name   VARCHAR(255)
    -> )
    -> UNIQUE KEY(id)
    -> DISTRIBUTED BY HASH(id) BUCKETS 4
    -> PROPERTIES (
    ->     "replication_num" = "1"
    -> );
Query OK, 0 rows affected (0.02 sec)





-- 插入 order_info 表的数据
INSERT INTO order_info (id, user_name) VALUES 
(1, 'User1'),
(2, 'User2'),
(3, 'User3'),
(4, 'User4'),
(5, 'User5'),
(6, 'User6'),
(7, 'User7'),
(8, 'User8'),
(9, 'User9'),
(10, 'User10'),
(11, 'User11'),
(12, 'User12'),
(13, 'User13'),
(14, 'User14'),
(15, 'User15'),
(16, 'User16'),
(17, 'User17'),
(18, 'User18'),
(19, 'User19'),
(20, 'User20');

-- 插入 order_detail 表的数据
INSERT INTO order_detail (id, order_id, sku_id, sku_name) VALUES 
(1, 1, 1001, 'Product1'),
(2, 2, 1002, 'Product2'),
(3, 3, 1003, 'Product3'),
(4, 4, 1004, 'Product4'),
(5, 5, 1005, 'Product5'),
(6, 6, 1006, 'Product6'),
(7, 7, 1007, 'Product7'),
(8, 8, 1008, 'Product8'),
(9, 9, 1009, 'Product9'),
(10, 10, 1010, 'Product10'),
(11, 11, 1011, 'Product11'),
(12, 12, 1012, 'Product12'),
(13, 13, 1013, 'Product13'),
(14, 14, 1014, 'Product14'),
(15, 15, 1015, 'Product15'),
(16, 16, 1016, 'Product16'),
(17, 17, 1017, 'Product17'),
(18, 18, 1018, 'Product18'),
(19, 19, 1019, 'Product19'),
(20, 20, 1020, 'Product20');




CREATE MATERIALIZED VIEW mv1 
        BUILD DEFERRED REFRESH COMPLETE ON MANUAL
        DISTRIBUTED BY RANDOM BUCKETS 2
        PROPERTIES ('replication_num' = '1') 
        AS 
        select order_info.id, user_name ,order_id, sku_id, sku_name,sum(sku_id)  from order_info left join order_detail on order_info.id = order_detail.id group by order_info.id, user_name ,order_id, sku_id, sku_name ;


DELETE FROM order_info where id = 1;

UPDATE order_info 
    SET user_name = 'zhangshan'
    WHERE id = 2;
阿渊@SelectDB5735