SHOW MATERIALIZED VIEW
Description
Shows all or one specific asynchronous materialized view.
Syntax
SHOW MATERIALIZED VIEW
[FROM db_name]
[
WHERE
[NAME { = "mv_name" | LIKE "mv_name_matcher"}
]
Parameters in brackets [] is optional.
Parameters
| Parameter | Required | Description |
|---|---|---|
| db_name | no | The name of the database to which the materialized view resides. If this parameter is not specified, the current database is used by default. |
| mv_name | no | The name of the materialized view to show. |
| mv_name_matcher | no | The matcher used to filter materialized views. |
Returns
| Return | Description |
|---|---|
| id | The ID of the materialized view. |
| database_name | The name of the database in which the materialized view resides. |
| name | The name of the materialized view. |
| rows | The number of data rows in the materialized view. |
| text | The statement used to create the materialized view. |
Examples
MySQL > SHOW MATERIALIZED VIEW\G
*************************** 1. row ***************************
id: 10099
name: order_mv
database_name: test
text: CREATE MATERIALIZED VIEW `order_mv` (`order_id`, `total`)
DISTRIBUTED BY HASH(`order_id`)
REFRESH ASYNC START("2022-09-01 10:00:00") EVERY(INTERVAL 1 DAY)
PROPERTIES (
"replication_num" = "3",
"storage_medium" = "HDD"
)
AS SELECT `order_list`.`order_id`, sum(`goods`.`price`) AS `total`
FROM `wlc`.`order_list` INNER JOIN `wlc`.`goods` ON `goods`.`item_id1` = `order_list`.`item_id2`
GROUP BY `order_list`.`order_id`;
rows: 3
1 row in set (0.02 sec)