无需重构架构，如何追踪CRUD操作及实现特定表数据变更审计？

Hey there! Let's break down practical, low-impact solutions for your two auditing needs—since you can't refactor your existing setup or add a frontend middle layer, we'll stick to database-native approaches that work seamlessly with your current stack.

1. Tracking All CRUD Operations History Without Refactoring #

Here are three reliable methods that won’t require touching your application code:

- Database-Native Audit Plugins #

Most modern databases come with built-in audit tools that capture all CRUD activity out of the box:

• MySQL: Use the MySQL Audit Log Plugin—install it via your package manager, then enable it in my.cnf with parameters like audit_log=ON and audit_log_events=CONNECT,QUERY to log all operations.
• PostgreSQL: Install the pgAudit extension, then configure it to track READ, WRITE, or ALL events via postgresql.conf.
• SQL Server: Enable SQL Server Audit to create audit specifications that capture database-level or server-level CRUD actions.

These plugins run at the database level, so your app stays completely unchanged. Just make sure you set up proper storage and retention policies for audit logs to avoid disk bloat.

- Database Triggers #

Create triggers that automatically log CRUD actions to a dedicated audit table. This gives you full control over what data gets logged:
For example, a simple MySQL trigger to track updates on a users table:

-- First, create an audit table to store changes
CREATE TABLE user_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    operation_type VARCHAR(10) NOT NULL,
    user_id INT NOT NULL,
    old_email VARCHAR(255),
    new_email VARCHAR(255),
    change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50) DEFAULT CURRENT_USER()
);

-- Create an AFTER UPDATE trigger
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (operation_type, user_id, old_email, new_email)
    VALUES ('UPDATE', OLD.id, OLD.email, NEW.email);
END //
DELIMITER ;

You can replicate this pattern for INSERT and DELETE operations too. Just be mindful of performance—triggers add a small overhead, so test with your workload first.

- Parse Binary/Transaction Logs #

All databases write transaction logs to ensure data integrity. You can parse these logs to extract CRUD history:

• MySQL: Use mysqlbinlog to read binary logs, which contain all data modification events.
• PostgreSQL: Parse Write-Ahead Logs (WAL) using tools like pg_waldump or CDC tools built on top of WAL.
• SQL Server: Query the transaction log directly or use tools like fn_dblog to extract change details.

This method is non-intrusive but requires some scripting or third-party tools to turn raw logs into readable audit records.

2. Capturing Specific Table Data Changes for Auditing #

Since you can't add a middle layer, focus on table-specific, database-native solutions:

- Targeted Triggers (Most Direct) #

Instead of creating triggers for every table, only set them up on the specific tables you need to audit. For example, to track value changes in a orders table:

-- Audit table for orders
CREATE TABLE orders_audit (
    audit_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    old_total DECIMAL(10,2),
    new_total DECIMAL(10,2),
    change_timestamp TIMESTAMP DEFAULT NOW(),
    changed_by VARCHAR(50) DEFAULT CURRENT_USER
);

-- PostgreSQL trigger function to log updates
CREATE OR REPLACE FUNCTION log_order_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status != NEW.status OR OLD.total != NEW.total THEN
        INSERT INTO orders_audit (order_id, old_status, new_status, old_total, new_total)
        VALUES (OLD.id, OLD.status, NEW.status, OLD.total, NEW.total);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to orders table
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_changes();

This only logs changes when specific columns (status, total) are modified, keeping your audit data lean.

- Change Data Capture (CDC) #

Many databases offer CDC features that automatically track changes to specific tables without triggers:

• SQL Server: Enable CDC on your target table—SQL Server will create system tables that store before/after values for every modification.
• PostgreSQL: Use extensions like pg_cdc or wal2json to capture table-specific changes from WAL logs.
• MySQL: Tools like Debezium can tap into binary logs to capture CDC events for specific tables, running as a separate service without touching your app.

CDC is great for high-volume environments since it's often more efficient than triggers.

- Timestamp + Version Columns (Simple Retrospective Tracking) #

If you don't need real-time capture, add a last_updated_at timestamp and version integer column to your target table. Then, you can:

• Keep historical snapshots of the table (via backups or periodic exports)
• Compare current records with past snapshots to track value changes over time

This is a low-overhead option but works best for retrospective auditing rather than real-time monitoring.

Key Notes to Remember #

• Permissions: Ensure your audit tables/logs are read-only for regular users to prevent tampering.
• Performance: Test triggers/CDC with your production workload to avoid unexpected slowdowns.
• Retention: Set up automated archiving for audit data to manage storage costs.

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