Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it necessary to drop temporary tables in MySQL?

Tags:

mysql

I'm having a problem dropping a temporary table. The user account does not have the 'drop' privilege. I don't want to grant that privilege due to security reasons.

I tried to find a privilege like 'drop temporary' but there isn't. It seems the only option is to remove all the 'drop table' statements. I know that the temporary tables will be automatically dropped after the DB sessions end.

However, I'm not sure if there are any side effects leaving this job to MySQL. Please, advice.

like image 620
sogno Avatar asked Jun 21 '12 04:06

sogno


People also ask

Do you need to drop temp tables in MySQL?

MySQL removes the temporary table automatically when the session ends or the connection is terminated. Of course, you can use the DROP TABLE statement to remove a temporary table explicitly when you are no longer use it. A temporary table is only available and accessible to the client that creates it.

Do you need to drop temp tables?

If you are wondering why it is not required to drop the temp table at the end of the stored procedure, well, it is because when the stored procedure completes execution, it automatically drops the temp table when the connection/session is dropped which was executing it.

Do you have to manually delete temporary tables?

The use of temporary tables, or temp tables in SQL terms, is common in SQL, but once we're done with those tables, they should be deleted, or dropped. Using the DROP TABLE command on a temporary table, as with any table, will delete the table and remove all data.

Why do we need temporary tables in MySQL?

A Temporary Table is needed when it is either expensive or virtually impossible to query data that needs a single SELECT statement to work in tandem with JOIN clauses. Here, you can leverage MySQL Temporary Tables to store the immediate result and use a different query to analyze or process it.


1 Answers

Temporary tables are dropped automatically as soon as you disconnect from database

A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

So - create them, use and don't bother of theirs deletion

like image 123
zerkms Avatar answered Oct 04 '22 23:10

zerkms