是否使用“创建临时表"创建表?在内存中还是在磁盘上? [英] Are tables created with "CREATE TEMPORARY TABLE" in memory or on disk?

问题描述

在 MySQL 中，当您创建临时表(例如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?

我已经阅读了文档，并用Google搜索了它，还没有给出答案.

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

推荐答案

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

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.

创建临时表时，应该有可能实际找到在文件系统中创建的文件.运行以下命令后:

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;

然后我检查了以下目录:C:\ ProgramData \ MySQL \ MySQL Server 5.5 \ data \ test(在Windows上)，显示的文件是:

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

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.

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

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

#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.

这篇关于是否使用“创建临时表"创建表?在内存中还是在磁盘上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！