mysql的临时表不支持自连接

最新推荐文章于 2024-07-18 02:05:01 发布

原创最新推荐文章于 2024-07-18 02:05:01 发布 · 580 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

MySQL 专栏收录该内容

39 篇文章

订阅专栏

本文介绍了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.