mysql - 查询从数据库中选择所有表而不是物化 View

Question

我正在使用查询从数据库中选择所有表

select table_name from all_tables where owner='XYZ' order by table_name

但上面的查询也返回物化 View 。如何只获取表而不获取物化 View

Answer 1

你可以忽略物化 View 和它们的日志表(如果它们存在的话)

select *
  from all_tables t
 where owner = user
   and (owner, table_name) not in (select owner, mview_name
                                     from all_mviews l
                                   union all
                                   select log_owner, log_table
                                     from all_mview_logs)