Identify and archive old MySql records

Question

Identify and archive old MySql records

Navigation

#1 by (1 votes)

1

I'm working on a project using database as MySql where some areas will be constantly fed generating a large number of records in a short time, as is the case of an area for maintenance tickets. However, this data becomes obsolete as quickly as it is created, and after a certain time, it no longer needs to be used.

How can I mount a system to "Archive" these records so that it no longer 'weighs' in the table?

I currently have a related table structure, where I relate clients and moderators to Ticket. Example:

tabela: Cliente
id | nome_cliente | sobrenome_cliente | etc..

tabela: Moderador
id | nome_moderador | cargo | etc..

tabela: Ticket
id | id_cliente | id_moderador | id_assunto  | etc..

I could just create a new column arquivado in the Ticket table and use a SQL to select all but the archived. But they would still be registered in that table.

Creating a new duplicate table from the Ticket table would be the solution? Eg: ticket_arquivos and then move records to it?

Or what other method can I use to do this management?

Another doubt about this would be regarding the identification of this obsolete data. I currently have the column status and data_update , where the status should be terminated and the current date should be greater than 15 days from the update date. Is it possible to make this data_update column automatically refresh regardless of the type of sql that it suffers? Be a UPDATE or a SELECT ?

mysql sql

asked by anonymous 30.05.2016 / 02:38

1 answer

Turning to decimal in Excel sum of PHP and MySql values

score 1 · Accepted Answer

Friend, I think the best solution is to actually create a table that gets these "obsolete" records.

You could create a job ( link ) that is run periodically and will be responsible for creating the records in the file table and deleting from the original table.

Example job , as it is called in MySQL ):

delimiter |

CREATE EVENT e_arquivamento_diario
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Transfere registros obsoletos para tabela de arquivamento'
    DO
      BEGIN
        INSERT INTO meu_esquema.meu_arquivo (col1, col2, col3)
          SELECT col1, col2, col3
            FROM meu_esquema.minha_tabela
           WHERE status = 'encerrado'
             AND DATEDIFF(CURDATE(), data_update) >= 15;

         // aqui também adicionar código de exclusão da tabela original

      END |

delimiter ;

As for maintaining the data_update column you can use a trigger ( link ) to update its value on INSERT and UPDATE operations, but there is no such device to control access to the registry via SELECT . I think in this case control would have to be done via the system.

A trigger is triggered when a INSERT , UPDATE or DELETE operation is performed on a table. The code allocated to the trigger can be executed before ( BEFORE ) or after AFTER >) of the original operation command.

Example of trigger that will be executed before INSERT , to fill the value of a certain column:

CREATE TRIGGER ins_data_update BEFORE INSERT ON minha_tabela
   FOR EACH ROW SET NEW.data_update = CURDATE();

Trigger for UPDATE :

CREATE TRIGGER upd_data_update BEFORE UPDATE ON minha_tabela
   FOR EACH ROW SET NEW.data_update = CURDATE();

Note: The values, table names, and columns of the examples are illustrative only, and should be replaced with actual values and names for execution in the production environment.