Movatterモバイル変換

On Unix, MySQL uses the value of theTMPDIR environment variable as the path name of the directory in which to store temporary files. IfTMPDIR is not set, MySQL uses the system default, which is usually/tmp,/var/tmp, or/usr/tmp.

On Windows, MySQL checks in order the values of theTMPDIR,TEMP, andTMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none ofTMPDIR,TEMP, orTMP are set, MySQL uses the Windows system default, which is usuallyC:\windows\temp\.

If the file system containing your temporary file directory is too small, you can use themysqld--tmpdir option to specify a directory in a file system where you have enough space.

The--tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (:) on Unix and semicolon characters (;) on Windows.

If the MySQL server is acting as a replica, you can set the system variablereplica_load_tmpdir to specify a separate directory for holding temporary files when replicatingLOAD DATA statements. This directory should be in a disk-based file system (not a memory-based file system) so that the temporary files used to replicate LOAD DATA can survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process. However, replication can now continue after a restart if the temporary files have been removed.

MySQL arranges that temporary files are removed ifmysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases,lsof +L1 may be helpful in identifying large files associated withmysqld.)

When sorting (ORDER BY orGROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

(length of what is sorted + sizeof(row pointer))* number of matched rows* 2

The row pointer size is usually four bytes, but may grow in the future for really big tables.

For some statements, MySQL creates temporary SQL tables that are not hidden and have names that begin with#sql.

SomeSELECT queries creates temporary SQL tables to hold intermediate results.

DDL operations that rebuild the table and are not performed online using theALGORITHM=INPLACE technique create a temporary copy of the original table in the same directory as the original table.

Online DDL operations may use temporary log files for recording concurrent DML, temporary sort files when creating an index, and temporary intermediate tables files when rebuilding the table. For more information, seeSection 17.12.3, “Online DDL Space Requirements”.

InnoDB user-created temporary tables and on-disk internal temporary tables are created in a temporary tablespace file namedibtmp1 in the MySQL data directory. For more information, seeSection 17.6.3.5, “Temporary Tablespaces”.

See alsoSection 17.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.

The optionalEXTENDED modifier causesSHOW TABLES to list hidden tables created by failedALTER TABLE statements. SeeSection 15.7.7.40, “SHOW TABLES Statement”.