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

Are tables created with "CREATE TEMPORARY TABLE" in memory or on disk?

提问by Nate Weiner

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.

我已经通读了文档并谷歌了它，但没有想出答案。

回答by Mark Byers

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.

回答by Alexander Janssen

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:

就像马克说的，这取决于你告诉它使用什么 ENGINE。如果您不提供 ENGINE，它会做“某事”，但不一定将其保留在内存中。如果要强制该表在内存中，则必须明确定义它：

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

However, the use of MEMORY-engine is restricted. For more information have a look at Internal Temporary Table Use in MySQL.

但是，内存引擎的使用受到限制。有关更多信息，请查看MySQL中的内部临时表使用。