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) 'www.2cto.com 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 ). To create a temporary table, you must have the 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 mysql> SELECT * FROM temp_table, temp_table AS t2; www.2cto.com ERROR 1137: Can't reopen table: 'temp _ table' is not available'
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 rename them. However, you can replace alter table with: mysql> alter table orig_name RENAME new_name; after a temporary table is used up, remember to drop: drop temporary table if exists sp_output_tmp;
Author lei. Y