MYSQL:临时表问题，不支持自联接_MySQL

bitsCN.com

MYSQL:临时表问题，不支持自联接

 

TEMPORARY TABLE Problems

The following list indicates limitations on the use of TEMPORARY tables: 

 

A TEMPORARY table can only be of type MEMORY, ISAM, MyISAM, MERGE, or InnoDB. 

 

Temporary tables are not supported for MySQL Cluster. 

 

You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work: 

 

mysql> SELECT * FROM temp_table, temp_table AS t2;

ERROR 1137: Can't reopen table: 'temp_table'

 

The SHOW TABLES statement does not list TEMPORARY tables. 

 

You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead: 

 

mysql> ALTER TABLE orig_name RENAME new_name;

 

There are known issues in using temporary tables with replication. See Section 6.7, “Replication Features and Known Problems”, for more information. 

 

Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure: 

 

Issue a STOP SLAVE statement. 

 

Use SHOW STATUS to check the value of the Slave_open_temp_tables variable. 

 

If the value is 0, issue a mysqladmin shutdown command to stop the slave. 

 

If the value is not 0, restart the slave threads with START SLAVE. 

 

Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave.

 

bitsCN.com