Easy 'create table from view' syntax in mysql?

Question

I want to create a table that's a cache of results from a view. Is there an easy way to automatically define the table from the view's definition, or will I have to cobble it together from show create table view?

Answer 1

You can do CREATE TABLE SELECT from the view to build it. That should duplicate the view's structure as a new table containing all the view's rows. Here's the MySQL syntax reference for this statement.

CREATE TABLE tbl_from_view AS       SELECT     col1,     col2,     col3,     col4,     col5   FROM your_view; 

Note that you will want to be very explicit in your column selections. It isn't advisable to do a SELECT * from the source view. Make sure as well that you have aliases for any calculated or aggregate columns like COUNT(*), MAX(*), (col1 + col2), etc.

Answer 2

I also found that in the mysqldump output, there are statements that create the view as a table, just before it defines the view. I can parse those out and run them as queries.