MySQL CREATE TABLE as SELECT - 使用 MEMORY ENGINE（在 RAM 内存中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20144394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATE TABLE as SELECT - using MEMORY ENGINE (in RAM memory)
提问by Menios
I was reading about tables using MEMORY ENGINE(tables stored in ram).
我正在阅读有关使用MEMORY ENGINE（存储在 ram 中的表）的表的信息。
Is it possible to use
CREATE TABLE AS SELECTsyntax, but have the created table usMEMORY ENGINE?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 AS SELECT语法，但是创建的表是我们MEMORY ENGINE吗？MEMORY 表的大小有限制吗？可以创建一个 MEMORY 表，它是 1.5 GB 物理表的副本吗？
回答by Alma Do
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_tMaximum size of table by default if
16Mb, but it can be adjusted withmax_heap_table_sizeserver system variable. But please, note, that this limit is per engine- not per table. I.e. all yourmemorytables will share it. To restrict size of individual table, you'll need to operate on sessionvalue formax_heap_table_size, likemysql> SET max_heap_table_size = 24*1024*1024;
是的，它可以完成，但不能使用简单的语法
CREATE TABLE t AS SELECT ...- 您需要在完整语句中指定表列，示例：CREATE TABLE t (col1 INT(11), col2 INT(11)) ENGINE=MEMORY AS SELECT * FROM another_t默认情况下表的最大大小为 if
16Mb，但可以使用max_heap_table_size服务器系统变量进行调整。但是请注意，此限制是针对每个引擎的，而不是针对每个表的。即您所有的memory表都将共享它。要限制单个表的大小，您需要对会话值进行操作max_heap_table_size，例如mysql> SET max_heap_table_size = 24*1024*1024;
回答by Carlos Teevin
you can also create a temporary table this way:
您还可以通过以下方式创建临时表：
CREATE TEMPORARY TABLE IF NOT EXISTS tmp ENGINE=MEMORY AS (
SELECT 'abc' As tomcat, 'def' As apache);
Or this way:
或者这样：
CREATE TEMPORARY TABLE IF NOT EXISTS tmp ENGINE=MEMORY AS (
SELECT tomcat, apache From servers);
Be advised, the first option will create the two columns as varchar(3), meanwhile the second option will import any column information from the original table, even their comments.
请注意，第一个选项会将两列创建为 varchar(3)，同时第二个选项将从原始表中导入任何列信息，甚至是它们的注释。
