1

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is that we have to put where deleted = 1 in all queries. What is the best way to keep some records as an archive?

Improve this question

1 Answer 1

Reset to default
2

You basically have two choices. You could use a view to do the filtering though this has some performance issues on MySQL, or you could use a trigger to copy deleted rows into an archive table. I suspect that on MySQL, the trigger solution will be much better (on other DB's the tradeoffs may go other ways.

Improve this answer
4
  • Chris Travers: In View mode we will have 2 problems : first we have to make a view for each table and second is Inserts with duplicate key while you can not insert in to a view! and For Triggers , all your foreign keys will fail and again you have to make duplicate table for each table.
    – Abadis
    Commented Feb 25, 2013 at 11:00
  • 1
    Yes, these are problems. The other solution is just to add "and not deleted" to every where clause. There is no free lunch.
    – Chris Travers
    Commented Feb 25, 2013 at 11:41
  • I think mysql and other db engines must do sth about this!
    – Abadis
    Commented Feb 25, 2013 at 13:41
  • 1
    You could probably repurpose Oracle's row-level security to do something like it....
    – Chris Travers
    Commented Feb 25, 2013 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.