Last Updated: 17 June, 2023
In MySQL, a temporary table is a special type of table that is created and exists only for the duration of the current database session. If the current session is closed, expires, or the connection is terminated, MySQL deletes all the temporary tables automatically.
There are two types of temporary tables in MySQL:
To create a temporary table in MySQL, we can use the CREATE TEMPORARY TABLE statement followed by the table definition.
To create a temporary table in MySQL, we can use the following syntax:
CREATE TEMPORARY TABLE temp_table_name( column_1 datatype, column_2 datatype, ..., table_constraints );
Example:
CREATE TEMPORARY TABLE student( stu_id INT NOT NULL AUTO_INCREMENT, stu_name VARCHAR(50) NOT NULL, stu_class VARCHAR(15), stu_roll SMALLINT, PRIMARY KEY (stu_id) );
In the current session, if our requirement is to delete the temporary table, then we can also delete it using the DROP TEMPORARY TABLE statement followed by table name.
To drop a temporary table in MySQL, we can use the following syntax:
DROP TEMPORARY TABLE temp_table_name;
Example:
DROP TEMPORARY TABLE student;
Temporary tables are useful in the following situations:
A temporary table can have the same name as an existing table in a database. For example, in the school_DB database, if we create a temporary table named student, the existing student table becomes inaccessible. Every query we execute against the student table now refers to the temporary table student. When we drop the temporary table student, the permanent student table becomes available and accessible.
That's all, guys. I hope this MySQL article is helpful for you.
Happy Learning... 😀
feedback@javabytechie.com