How to create a mysql view with current information

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 informatio开发者_Python百科n?

---

The thing you're looking for is called a materialized view.

MySQL does not natively support materialized views.

There are a few workarounds, but the most simple and straightforward (if poor performing) is using CREATE TABLE ... AS SELECT ... instead of creating a view. This duplicates data, and may be quite slow if there's a lot of it. Further, I don't believe it creates any indexes, so you might need to clean up a bit afterward.

---

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.