如何创建包含当前信息的 mysql 视图

发布于 2024-10-29 06:42:03 字数 313 浏览 4 评论 0原文

我想创建当前信息的视图，如下所示：

CREATE VIEW activeProducts AS SELECT * FROM products WHERE isActive = 1

上面的语句创建查询的快照：

SELECT * FROM products WHERE isActive = 1

因此，如果我在运行此查询后更改表“products”中任何项目的状态，它不会反映在看法。我知道这是mysql中CREATE VIEW的功能，是否应该使用开关或命令来查看或过滤当前信息？

原文

I want to create a view of current information as such:

CREATE VIEW activeProducts AS SELECT * FROM products WHERE isActive = 1

The above statement creates a snapshot of the query:

SELECT * FROM products WHERE isActive = 1

So if I change the state of any item in the table 'products' after this query is run, it isn't reflected in the view. I understand that this is the function of CREATE VIEW in mysql, is there a switch or command that I should use for viewing or filtering current information?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半城柳色半声笛 2024-11-05 06:42:03

您正在寻找的东西称为物化视图。

MySQL 本身不支持物化视图。

有一些解决方法，但最简单直接（如果性能不佳）是使用 CREATE TABLE ... AS SELECT ... 而不是创建视图。这会复制数据，如果数据很多，速度可能会很慢。此外，我不相信它会创建任何索引，因此您可能需要稍后进行清理。

回复收藏 0 原文

帝王念 2024-11-05 06:42:03

您想要的是“物化视图”，也称为快照。

查尔斯提出了拍摄快照的最简单方法，但正如他所建议的那样，它不会有索引。如果您希望快照包含相同的索引，您可以使用：

create table if not exists snapshot_table like regular_table;
begin; 
  delete from snapshot_table; 
  insert into snapshot_table select * from regular_table where isActive = 1;
commit;

也就是说，如果表很大，或者您想使用联接或聚合，那么更新视图可能会非常昂贵。

如果您想研究可以快速刷新的“增量可刷新物化视图”，请查看此博文。

What you want is a "materialized view" which is also known as a snapshot.

Charles presented the easiest way to take a snapshot, but as he suggested it won't have indexes. If you want the snapshot to include the same indexes you can use:

create table if not exists snapshot_table like regular_table;
begin; 
  delete from snapshot_table; 
  insert into snapshot_table select * from regular_table where isActive = 1;
commit;

That being said, if the table is large, or you want to use joins or aggregation, then updating the view can be very expensive.

If you want to investigate "incrementally refreshable materialized views", which can be refreshed fast, then check out this blog post.

回复收藏 0 原文

~没有更多了~