MySQL temp table Cann ' t reopen solution _mysql

When you create a temporary table, you can use the TEMPORARY keyword. Such as:

 code as follows:

The Create temporary table tmp_table (name varchar (a) not null,passwd char (6) is not NULL);

Or

 code as follows:

Create temporary table if not exists sp_output_tmp engine= memory Select ... where id=current_id;

Temporary tables are visible only at the current connection, and are automatically drop when the connection is closed. This means that you can use the same temporary table name in two different connections and not conflict with each other, or use a table that already exists, but not a table name for a temporary table. (When this temporary table exists, the existing table is hidden, and if the temporary table is drop, the existing table is visible). Create a temporary table you must have
Create temporary table permission.
The following are the restrictions for temporary tables：
1, temporary table can only be used in Memory,myisam,merge, or InnoDB
2, temporary table does not support MySQL cluster (cluster)
3, in the same query statement, you can only find a temporary table. For example: The following is not available

e code as follows:

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 can occur if, in a stored function, you look up a temporary table multiple times with a different alias, or find it in a different statement in the stored function.
4. The show tables statement does not enumerate temporary tables
You can't use rename to rename a temporary table. However, you can alter TABLE instead:

 code as follows:

Mysql>alter TABLE orig_name RENAME new_name;

Remember to drop the temporary table when you are done with it:

 code as follows:

DROP temporary TABLE IF EXISTS sp_output_tmp;