MySQL – Beginning Temporary Tables in MySQL
- by Pinal Dave
MySQL supports Temporary tables to store the resultsets temporarily for a given connection. Temporary tables are created with the keyword TEMPORARY along with the CREATE TABLE statement.
Let us create the temporary table named Temp
CREATE TEMPORARY TABLE TEMP
(id INT);
Now you can find out the column names using DESC command
DESC TEMP;
The above returns the following result
This table can be accessed only for the current connection and it can be used like a permanent table and automatically dropped when the connection is closed. However, you can not find temporary tables using INFORMATION_SCHEMA. TABLES system view. It will only list out the permanent tables.
MySQL usually stores the data of temporary tables in memory and processed by Memory Storage engine. But if the data size is too large MySQL automatically converts this to the on – disk table and use MyISAM engine.
You can also create a permanent table with the same name of a temporary table in the same connection. However the structure of permanent table is visible only if the temporary table with the same name is dropped.
Let us create a permanent table with the same name Temp as below
CREATE TABLE TEMP
(id INT,
names VARCHAR(100));
Now running the following command stills gives you the structure of the temporary table temp created earlier.
DESC TEMP;
You can drop the temporary table using DROP TEMPORARY TABLE command;
DROP TEMPORARY TABLE TEMP;
After you executed the temporary table, run the following command
DESC TEMP;
Now you will see the structure of the permanent table named temp
In summary – If there is a Temporary Table in MySQL it gets first priority over the permanent table in the session.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: MySQL, PostADay, SQL, SQL Authority, SQL Query, SQL Tips and Tricks, T SQL
