Mysql temporary table cann' treopen solution _ MySQL

Mysql temporary table cann bitsCN.com

When creating a temporary table, you can use the temporary keyword. For example:

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 to the current connection. when the connection is closed, it is automatically dropped. This means that you can use the same temporary table name in two different connections without conflict with each other, or use an existing table, but not a temporary table name. (When the temporary table exists, the existing table is hidden. if the temporary table is dropped, the existing table is visible ). You must have
Create temporary table permission.
The following are restrictions on temporary tables::
1. temporary tables can only be used in memory, myisam, merge, or innodb
2. temporary tables do not support mysql cluster (cluster)
3. in the same query statement, you can only search for a temporary table once. For example, 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
If you use different aliases to search for a temporary table multiple times in a storage function, or use different statements in the storage function, this error will occur.
4. the show tables statement does not list temporary tables.
You cannot use rename to rename a temporary table. However, you can replace alter table:

Mysql> alter table orig_name RENAME new_name;

Remember to drop the temporary table after it is used up:

Drop temporary table if exists sp_output_tmp;

BitsCN.com