Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 25.6Mb
HTML Download (TGZ) - 6.4Mb
HTML Download (Zip) - 6.5Mb
HTML Download (RPM) - 5.5Mb
Man Pages (TGZ) - 158.5Kb
Man Pages (Zip) - 262.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

B.4.6.2 TEMPORARY Table Problems

Temporary tables created with CREATE TEMPORARY TABLE have the following limitations:

  • TEMPORARY tables are supported only by the InnoDB, MEMORY, MyISAM, and MERGE storage engines.

  • Temporary tables are not supported for NDB Cluster.

  • The SHOW TABLES statement does not list TEMPORARY tables.

  • To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead:

    ALTER TABLE old_name RENAME new_name;
  • You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

    SELECT * FROM temp_table JOIN temp_table AS t2;

    The statement produces this error:

    ERROR 1137: Can't reopen table: 'temp_table'
  • The Can't reopen table error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

  • If a TEMPORARY is created with the same name as an existing non-TEMPORARY table, the non-TEMPORARY table is hidden until the TEMPORARY table is dropped, even if the tables use different storage engines.

  • There are known issues in using temporary tables with replication. See Section 17.4.1.30, “Replication and Temporary Tables”, for more information.