MySQL与Oracle是否支持满足特定要求的数据库级软删除机制

Great question! Let's dive into how MySQL and Oracle handle this, since neither has an out-of-the-box database-level soft delete mechanism that meets all three of your requirements—but both can be configured to achieve exactly what you need with intentional setup.

MySQL doesn’t include a native database-level soft delete feature, but you can combine views and triggers to enforce all three of your rules. Here’s how to set it up:

1. Add a soft delete marker column to your base table
   First, add columns to track deleted status (and optionally a timestamp for audit purposes):

   ALTER TABLE your_table 
   ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0,
   ADD COLUMN deleted_at DATETIME NULL;
   

2. Create a view that filters out deleted rows
   This view will be the primary interface for users—all queries against it automatically exclude soft-deleted data, no explicit WHERE clause needed:

   CREATE VIEW v_your_table AS
   SELECT * FROM your_table WHERE is_deleted = 0;
   

3. Add INSTEAD OF triggers to handle write operations
   These triggers intercept DELETE and UPDATE actions on the view and redirect them to the base table with soft delete logic:

   • For DELETE (converts to soft delete):

     DELIMITER //
     CREATE TRIGGER trg_v_your_table_delete
     INSTEAD OF DELETE ON v_your_table
     FOR EACH ROW
     BEGIN
         UPDATE your_table 
         SET is_deleted = 1, deleted_at = NOW()
         WHERE id = OLD.id;
     END //
     DELIMITER ;
     

   • For UPDATE (only affects non-deleted rows):

     DELIMITER //
     CREATE TRIGGER trg_v_your_table_update
     INSTEAD OF UPDATE ON v_your_table
     FOR EACH ROW
     BEGIN
         UPDATE your_table
         SET col1 = NEW.col1, col2 = NEW.col2 -- List all updatable columns here
         WHERE id = OLD.id AND is_deleted = 0;
     END //
     DELIMITER ;
     

4. Enforce access controls
   To prevent users from bypassing the view and modifying the base table directly, revoke write permissions on your_table and grant them only on v_your_table.

Oracle offers more native tools to achieve this, combining Virtual Private Database (VPD) (for automatic query filtering) and triggers (for intercepting deletes). Here’s the step-by-step setup:

1. Add a soft delete marker column
   Start by adding tracking columns to your table:

   ALTER TABLE your_table
   ADD COLUMN is_deleted CHAR(1) NOT NULL DEFAULT 'N',
   ADD COLUMN deleted_at TIMESTAMP NULL;
   

2. Use VPD to auto-filter deleted rows
   VPD (also called Row-Level Security) injects a WHERE clause automatically into all SELECT/UPDATE statements against the table, ensuring only non-deleted data is accessed:

   • First, create a policy function:

     CREATE OR REPLACE FUNCTION soft_delete_policy(p_schema IN VARCHAR2, p_table IN VARCHAR2)
     RETURN VARCHAR2
     IS
     BEGIN
         RETURN 'is_deleted = ''N''';
     END;
     /
     

   • Apply the policy to your table:

     BEGIN
         DBMS_RLS.ADD_POLICY(
             object_schema => 'your_schema',
             object_name => 'your_table',
             policy_name => 'soft_delete_filter',
             function_schema => 'your_schema',
             policy_function => 'soft_delete_policy',
             statement_types => 'SELECT,UPDATE' -- Auto-filters for these operations
         );
     END;
     /
     

   This ensures all SELECT queries return only non-deleted rows, and UPDATEs only affect non-deleted rows—no explicit WHERE clauses required.

3. Add a trigger to intercept DELETE operations
   This trigger converts hard deletes into soft deletes by updating the is_deleted column instead:

   CREATE OR REPLACE TRIGGER trg_soft_delete_your_table
   BEFORE DELETE ON your_table
   FOR EACH ROW
   BEGIN
       UPDATE your_table
       SET is_deleted = 'Y', deleted_at = SYSTIMESTAMP
       WHERE id = :OLD.id;
       -- Raise an error to block the hard delete
       RAISE_APPLICATION_ERROR(-20001, 'Hard delete is disabled; soft delete performed instead');
   END;
   /
   

Key Notes for Both Databases #

• Index the soft delete column: Add an index on is_deleted to avoid performance hits from filtering.
• Audit and cleanup: Regularly archive or purge soft-deleted rows to keep your tables performant.
• Transaction consistency: Triggers run in the same transaction as the original operation, so failures will roll back all changes.

内容的提问来源于stack exchange，提问作者Surabhi Mundra