Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL Table drop after x hours

Is it possible to write extra code into a mysql table creation that will make it drop itself after X amount of time? Like a temp table, but it will last longer.

I need to create tables for temporary tasks, but i need them to last longer than a session

like image 299
cardi777 Avatar asked Jun 11 '12 12:06

cardi777


People also ask

How do I force drop a table in MySQL?

DROP TABLE MySQL Command Syntax. To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];

Does truncate drop and recreate table?

Although TRUNCATE TABLE is similar to DELETE , it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways: Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

What is table level locking in MySQL?

Table-Level Locking. MySQL uses table-level locking for MyISAM , MEMORY , and MERGE tables, permitting only one session to update those tables at a time. This locking level makes these storage engines more suitable for read-only, read-mostly, or single-user applications.


2 Answers

CREATE EVENT myevt
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
        DO
         DROP TABLE IF EXISTS MyTable;
like image 149
Romil Kumar Jain Avatar answered Oct 11 '22 09:10

Romil Kumar Jain


In your case with the CSV files, I would advise against creating a database table during your setup process, since that puts unecessary stress on your DBMS. A better way to do this would be:

  1. Move uploaded file to "incoming" directory
  2. Parse first few bytes of CSV to determine number of columns and additional data you need
  3. Let the user assign stuff
  4. Read CSV file to DB in the way your application needs it
  5. Delete file

In order to cleanup the CSV files, you can use a cronjob to delete old files: find /dir/with/csv/files -type f -cmin +TIMEINMINUTES -delete

like image 20
mensi Avatar answered Oct 11 '22 09:10

mensi