mysql temporary table cann't reopen solution

When you create temporary tables, you can use the temporary keyword. Such as:

 
create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null); 

or

 
create temporary table if not exists sp_output_tmp engine= memory select  ... from  ...  where ID=current_id; 

The temporary table is only visible on the current connection, and when the connection is closed, drop is automatically available. This means that you can use the same temporary table name in two different joins without conflict with each other, or you can use existing tables that are not temporary table names. While the temporary table exists, the existing table is hidden; if the temporary table is drop, the existing table is visible. Create temporary tables you must have
create temporary table permissions.
The following are the limitations of temporary tables:
1. Temporary tables can only be used in memory,myisam,merge, or innodb
2. Temporary table does not support mysql cluster(cluster)
3. You can only look up the temporary table once in the same query statement. The following is not available

 
mysql> SELECT * FROM temp_table, temp_table AS t2; 
ERROR 1137: Can't reopen table: 'temp_table' 

mysql bug address: http: / / bugs mysql. com/bug php? id = 10327
This error occurs if you look up a temporary table multiple times in a storage function using a different alias, or with a different statement in the storage function.
4. The show tables statement does not list temporary tables
You cannot rename a temporary table with rename. However, you can alter table instead:

 
mysql>ALTER TABLE orig_name RENAME new_name; 

Remember to drop the drop when you run out of temporary watches:

 
DROP TEMPORARY TABLE IF EXISTS sp_output_tmp;