是在内存中还是在磁盘上使用“CREATE TEMPORARY TABLE”创建的表？

In MySql, when you create a temporary table, for example "CREATE TEMPORARY TABLE ...", is that table created and held in memory or on the disk?

在MySql中，当您创建临时表时，例如“CREATE TEMPORARY TABLE ...”，该表是在内存中还是在磁盘上创建并保存的？

I have read through the docs and Google'd it and have not come up with an answer.

我已经阅读了文档和谷歌，并没有得到答案。

2 个解决方案

#1

It depends on what engine you specify. By default the table data will be stored on disk. If you specify the MEMORY engine, the data will only be stored in memory.

这取决于您指定的引擎。默认情况下，表数据将存储在磁盘上。如果指定MEMORY引擎，则数据将仅存储在内存中。

It should be possible to actually find the files that are created in the filesystem when the temporary tables are created. After running the following commands:

应该可以在创建临时表时实际查找在文件系统中创建的文件。运行以下命令后：

CREATE TABLE test.table_myisam (x int) ENGINE=MyISAM;
CREATE TABLE test.table_memory (x int) ENGINE=MEMORY;
CREATE TEMPORARY TABLE test.temp_table_myisam (x int) ENGINE=MyISAM;
CREATE TEMPORARY TABLE test.temp_table_memory (x int) ENGINE=MEMORY;

I then checked the directory: C:\ProgramData\MySQL\MySQL Server 5.5\data\test (on Windows) and the files present were:

然后我检查了目录：C：\ ProgramData \ MySQL \ MySQL Server 5.5 \ data \ test（在Windows上），存在的文件是：

table_innodb.frm   # Table definition.
table_innodb.MYD   # MyISAM table data file.
table_innodb.MYI   # MyISAM table index file.

table_memory.frm   # No MYD or MYI file for the MEMORY engine.

The temporary tables are stored in C:\Windows\Temp and have unusual names, but internally the data is stored in the same way.

临时表存储在C：\ Windows \ Temp中，并且具有不寻常的名称，但在内部数据以相同的方式存储。

#sql9a0_7_d.frm    # This is the MyISAM temporary table.
#sql9a0_7_d.MYD    # MyISAM data file for temporary table.
#sql9a0_7_d.MYI    # MyISAM index file for temporary table.

#sql9a0_7_c.frm    # This is the MEMORY engine file. No MYD or MYI.

#2

Like Mark said, it depends on what ENGINE you tell it to use. If you don't give an ENGINE, it will do "something", but not necessarily keep it just in memory. If you want to force the table to be in memory, you have to define it explicitly:

像马克所说，这取决于你告诉它使用什么引擎。如果你不给一个引擎，它会做“某事”，但不一定只是在内存中。如果要强制表在内存中，则必须明确定义它：

CREATE TEMPORARY TABLE foobar (id int) ENGINE=MEMORY;

However, the use of the MEMORY-engine is restricted. For more information have a look at http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html .

但是，MEMORY引擎的使用受到限制。有关更多信息，请查看http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html。

#1