MySQL Create and delete temporary table SQL statements

Temporary tables can be very useful, in some cases, to maintain temporary data. The most important thing to know is that the temporary table is that they will be deleted when the current client session terminates

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

The code is as follows	Copy Code
Create temporary table tmp_table (name varchar (ten) not null,passwd char (6) NOT null) ' Or 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:

Temporary tables can only be used in Memory,myisam,merge, or InnoDB

Temporary table does not support MySQL cluster (clusters)

In the same query statement, you can only find a temporary table. For example: The following is not available

The code is as follows	Copy Code
Mysql> SELECT * from Temp_table, temp_table as T2; ERROR 1137:can ' t reopen table: ' Temp_table '

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.

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:

The code is as follows	Copy Code
Mysql>alter TABLE orig_name RENAME new_name;

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

The code is as follows	Copy Code
DROP temporary TABLE IF EXISTS sp_output_tmp;

Create a temporary table

Instance

Here is an example of using a temporary table in a PHP script that uses the mysql_query () function to use the same code.

The code is as follows

Copy Code

mysql> CREATE Temporary TABLE salessummary ( -> product_name VARCHAR (m) not NULL ->, Total_sales DECIMAL (12,2) not NULL DEFAULT 0.00 ->, Avg_unit_price DECIMAL (7,2) not NULL DEFAULT 0.00 ->, Total_units_sold INT UNSIGNED not NULL DEFAULT 0 ); Query OK, 0 rows Affected (0.00 sec) Mysql> INSERT into Salessummary -> (Product_Name, Total_sales, Avg_unit_price, Total_units_sold) -> VALUES -> (' cucumber ', 100.25, 90, 2); Mysql> SELECT * from Salessummary; +--------------+-------------+----------------+------------------+ | Product_Name | Total_sales | Avg_unit_price | Total_units_sold | +--------------+-------------+----------------+------------------+ |      Cucumber |          100.25 |                90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in Set (0.00 sec)

When a show Tables command is issued, the temporary table will not be listed in the list. Now if you log off the MySQL session, the Select command will be issued and you will find no data in the database. Even the temporary table will not exist.

To delete a temporary table:

By default, MySQL's database connection is terminated when all temporary tables are deleted. However, you should issue a drop table command before you can delete them.

The following example deletes a temporary table.

The code is as follows

Copy Code

mysql> CREATE Temporary TABLE salessummary ( -> product_name VARCHAR (m) not NULL ->, Total_sales DECIMAL (12,2) not NULL DEFAULT 0.00 ->, Avg_unit_price DECIMAL (7,2) not NULL DEFAULT 0.00 ->, Total_units_sold INT UNSIGNED not NULL DEFAULT 0 ); Query OK, 0 rows Affected (0.00 sec) Mysql> INSERT into Salessummary -> (Product_Name, Total_sales, Avg_unit_price, Total_units_sold) -> VALUES -> (' cucumber ', 100.25, 90, 2); Mysql> SELECT * from Salessummary; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_ Price | Total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber     |       100.25 |          90.00 |                 2 | +--------------+-------------+----------------+------------------+ 1 row in Set (0.00 sec) Mysql> DROP TABLE salessummary; mysql>  SELECT * from Salessummary; ERROR 1146:table ' tutorials. Salessummary ' doesn ' t exist