MV_INFOS
更新时间：2025-10-16
描述
表函数，生成异步物化视图临时表，可以查看某个 db 中创建的异步物化视图信息。
语法
SQL
1MV_INFOS("database"="<database>")必填参数 (Required Parameters)
<database>
指定需要查询的集群数据库名
返回值
| 字段名称 | 类型 | 说明 |
|---|---|---|
| Id | BIGINT | 物化视图 id |
| Name | TEXT | 物化视图 Name |
| JobName | TEXT | 物化视图对应的 job 名称 |
| State | TEXT | 物化视图状态 |
| SchemaChangeDetail | TEXT | 物化视图 State 变为 SchemaChange 的原因 |
| RefreshState | TEXT | 物化视图刷新状态 |
| RefreshInfo | TEXT | 物化视图定义的刷新策略信息 |
| QuerySql | TEXT | 物化视图定义的查询语句 |
| EnvInfo | TEXT | 物化视图创建时的环境信息 |
| MvProperties | TEXT | 物化视属性 |
| MvPartitionInfo | TEXT | 物化视图的分区信息 |
| SyncWithBaseTables | BOOLEAN | 是否和 base 表数据同步，如需查看哪个分区不同步，请使用SHOW PARTITIONS |
示例
查看 test 下的所有物化视图
SQL
1select * from mv_infos("database"="test");
Text
1+-------+--------------------------+------------------+--------+--------------------+--------------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+--------------------+
2| Id | Name | JobName | State | SchemaChangeDetail | RefreshState | RefreshInfo | QuerySql | MvProperties | MvPartitionInfo | SyncWithBaseTables |
3+-------+--------------------------+------------------+--------+--------------------+--------------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+--------------------+
4| 19494 | mv1 | inner_mtmv_19494 | NORMAL | | SUCCESS | BUILD DEFERRED REFRESH AUTO ON MANUAL | SELECT `internal`.`test`.`user`.`k2`, `internal`.`test`.`user`.`k3` FROM `internal`.`test`.`user` | {partition_sync_limit=100, partition_sync_time_unit=YEAR} | MTMVPartitionInfo{partitionType=FOLLOW_BASE_TABLE, relatedTable=user, relatedCol='k2', partitionCol='k2'} | 1 |
5| 21788 | test_tablet_type_mtmv_mv | inner_mtmv_21788 | NORMAL | | SUCCESS | BUILD DEFERRED REFRESH AUTO ON MANUAL | SELECT `internal`.`test`.`test_tablet_type_mtmv_table`.`k2`, `internal`.`test`.`test_tablet_type_mtmv_table`.`k3` from `internal`.`test`.`test_tablet_type_mtmv_table` | {} | MTMVPartitionInfo{partitionType=SELF_MANAGE} | 0 |
6+-------+--------------------------+------------------+--------+--------------------+--------------+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+--------------------+查看 test 下的物化视图名称为 mv1 的物化视图
SQL
1select * from mv_infos("database"="test") where Name = "mv1";
Text
1+-------+------+------------------+--------+--------------------+--------------+---------------------------------------+---------------------------------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+--------------------+
2| Id | Name | JobName | State | SchemaChangeDetail | RefreshState | RefreshInfo | QuerySql | MvProperties | MvPartitionInfo | SyncWithBaseTables |
3+-------+------+------------------+--------+--------------------+--------------+---------------------------------------+---------------------------------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+--------------------+
4| 19494 | mv1 | inner_mtmv_19494 | NORMAL | | SUCCESS | BUILD DEFERRED REFRESH AUTO ON MANUAL | SELECT `internal`.`test`.`user`.`k2`, `internal`.`test`.`user`.`k3` FROM `internal`.`test`.`user` | {partition_sync_limit=100, partition_sync_time_unit=YEAR} | MTMVPartitionInfo{partitionType=FOLLOW_BASE_TABLE, relatedTable=user, relatedCol='k2', partitionCol='k2'} | 1 |
5+-------+------+------------------+--------+--------------------+--------------+---------------------------------------+---------------------------------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+--------------------+