What are Temporary Tables?

Q: What are Temporary Tables?

CREATE TEMPORARY TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); DROP TEMPORARY TABLE table_name;

Difficulty: 6/10

Session Scope, In-Memory vs Disk, Connection Pooling

The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually.

Query:

Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to.

Scenario Questions

0-2 years experience

1We have a multi-step reporting script where we need to store some intermediate user metrics temporarily. If we use 'CREATE TEMPORARY TABLE', what happens to that data if the script crashes halfway through? Do we need to write a cleanup script to drop it?
2Imagine you create a temporary table named 'temp_users' in your database GUI client to run some test queries. If your teammate logs into the exact same database from their machine, can they see or query your 'temp_users' table? Why or why not?

Follow-up Questions

What happens to a temporary table if the client connection is returned to a connection pool without being explicitly closed?
How does MySQL 8.0's TempTable storage engine differ from the traditional MEMORY engine when handling overflow?
Under what conditions will MySQL's optimizer implicitly create an internal temporary table on disk instead of in memory?