在MySQL中简单的“从视图创建表"语法? [英] Easy 'create table from view' syntax in mysql?

问题描述

我想创建一个表，该表是视图的结果缓存.是否有一种简单的方法可以根据视图的定义自动定义表，还是必须从show create table view将表拼凑在一起?

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?

推荐答案

您可以从视图中执行CREATE TABLE SELECT来构建它.这应该将视图的结构复制为一个包含所有视图行的新表.这是该语句的 MySQL语法参考.

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;

请注意，您将希望在列选择中非常明确.不建议从源代码视图执行SELECT *.同时确保您对任何计算所得或聚合列(例如COUNT(*), MAX(*), (col1 + col2)等)具有别名.

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.

这篇关于在MySQL中简单的“从视图创建表"语法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！