Deleting Data from MySQL

What is a effective way to simulate deletion of data from a MySQL database?

[Sekka]

Hi,

At present when we develop CMS driven websites, when content is deleted it is marked as deleted within our MySQL database so it is never actually deleted incase, for example, it was done by accident. We do this by having a table field simply called "deleted" set to either 0 or 1.

What I am asking is does anyone else do this or something similar? And if you do, what method do you undertake yourself or recommend?

Thank you.

[Scally_Ally]

There was a thread on this a couple of days ago..
check out this link
[webmasterworld.com...]

[Sekka]

Thanks for the help.

That was more the user interaction rather than the actual deleting process.

I was hoping there werw other methods out there, maybe better ones. My current method screws up LEFT JOINs so I can't use them. :/

[mcibor]

What I do is move the "deleted" fields to table "deleted". This way I free a bit my real table and I don't loose any data.

Just remember, when you move, to move with id (don't change it), so that you can someday restore it.

Also when performing many deletions run OPTIMIZE [dev.mysql.com]

However it all depends if you want to preserve content or not.
I have one table, where I perform huge deletions, I delete everything older than 30 days and some data older than 3 days and don't store anything, but that's up to you.

Regards
Michal