Remix.run Logo
jsemrau 7 months ago

MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.

evanelias 7 months ago | parent | next [-]

You're probably thinking of the --safe-updates option [1] for the `mysql` CLI, also available as the memorable alias --i-am-a-dummy. This requires UPDATE and DELETE to have either a WHERE clause or a LIMIT clause. Under the hood, the command-line client option just manipulates the sql_safe_updates session variable [2] to enforce the UPDATE and DELETE requirement, as well as a couple other unrelated variables to prevent overly-huge SELECTs.

It's not enabled by default out of the box, but some companies do override their configuration to enable it for new sessions, iirc Facebook did this.

[1] https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html#safe...

[2] https://dev.mysql.com/doc/refman/8.4/en/server-system-variab...

physicsguy 7 months ago | parent | prev | next [-]

That just wouldn’t fly where you have business customers, many insist on data deletion at the end of contracts. In practice though partitioning or using seperate databases can be a better strategy for dealing with that as otherwise dealing with backups are challenging.

kijin 7 months ago | parent | prev [-]

It might depend on the version, but last time I checked, DELETEing an entire table was much slower than TRUNCATE TABLE.

eddd-ddde 7 months ago | parent [-]

I'm pretty sure that only applies to Postgres.

magicalhippo 7 months ago | parent [-]

Sybase SQLAnywhere as well, and not unlikely MSSQL too given its shared ancestry.

Delete with WHERE is sufficiently slow in MSSQL we have to do batched deletes, but I can't recall offhand if that holds for whole table deletion as well.