A temporary table exists only for the duration of the current session and is automatically dropped when the session ends.
Since temporary tables are not permanently stored in the database, they are useful when you need a table temporarily for testing or specific operations, after which it should disappear automatically.
The CREATE TEMPORARY TABLE statement is used to create a temporary table.
If you need to create a temporary table from scratch, use the TEMPORARY keyword when creating the table, for example, CREATE TEMPORARY TABLE instead of CREATE TABLE. Refer to the create table chapter for complete syntax and examples.
Temporary tables are particularly useful when testing SQL queries without impacting the main database. Here's how you can create a temporary copy of an existing table in a MySQL database:
Type the following command at the MySQL command prompt and press enter:
The above statement creates a temporary table named persons on-the-fly using the result set from an existing base table persons. Since it is temporary, you can safely perform operations like INSERT, UPDATE, or DELETE without affecting the original persons table.
Tip: You can name a temporary table the same as a permanent base table. If you do this, the permanent base table is hidden until the temporary table is dropped.
Note: Temporary tables are session-specific, which means two different sessions can use the same temporary table name without conflicting with each other.
Temporary tables are automatically dropped when the database connection or session in which they are created is closed. However, if you need to delete them without closing the current session, you can use the DROP TEMPORARY TABLE statement, like this:
The above statement will remove the temporary table persons from the database. After that, the original persons base table will become visible again.
Sign up to receive the latest updates and exclusive offers right in your inbox.