将表创建为SELECT-使用MEMORY ENGINE(在RAM内存中) [英] CREATE TABLE as SELECT - using MEMORY ENGINE (in RAM memory)

问题描述

我正在使用 MEMORY ENGINE (表)阅读有关表的信息存储在ram中.)

I was reading about tables using MEMORY ENGINE (tables stored in ram).

• 是否可以使用CREATE TABLE AS SELECT语法，但创建的表是否为MEMORY ENGINE?

• Is it possible to use CREATE TABLE AS SELECT syntax, but have the created table us MEMORY ENGINE?

MEMORY表的大小是否受到限制?是否可以创建一个内存表，该表是1.5 GB的典型表的副本?

Is there a limit to the size of a MEMORY table? Could create a MEMORY table that is a copy of a pysical 1.5 GB table?

推荐答案

• 是的，可以做到，但是不能使用简单的语法CREATE TABLE t AS SELECT ...-您将需要在

  • Yes, it can be done, but not with simple syntax CREATE TABLE t AS SELECT ... - you will need to specify table columns in full statement, Sample:

    CREATE TABLE t (col1 INT(11), col2 INT(11)) 
    ENGINE=MEMORY 
    AS 
    SELECT * FROM another_t
    

  • 如果为16Mb，默认情况下表的最大大小，但是可以使用

  • Maximum size of table by default if 16Mb, but it can be adjusted with max_heap_table_size server system variable. But please, note, that this limit is per engine - not per table. I.e. all your memory tables will share it. To restrict size of individual table, you'll need to operate on session value for max_heap_table_size, like

     mysql> SET max_heap_table_size = 24*1024*1024;
    

  这篇关于将表创建为SELECT-使用MEMORY ENGINE(在RAM内存中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！