如何获取数据库表中最后修改及新增、变更的记录？

Hey there! Let's tackle your two database requirements one by one—these are super common scenarios, so I’ve got practical, actionable solutions for you.

The easiest way to do this depends on whether your table has a dedicated timestamp field for tracking modifications. Here’s how to handle both cases:

Case 1: Your table has an updated_at (or similar) field #

This is the best practice—if you don’t have this field already, add it first! Most databases let you configure it to auto-update whenever a record changes.

Example SQL queries: #

• For MySQL/MariaDB:

  -- First, add and configure the auto-updating field
  ALTER TABLE your_table ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  
  -- Fetch the latest modified record
  SELECT * FROM your_table ORDER BY updated_at DESC LIMIT 1;
  

• For PostgreSQL:

  -- Add auto-updating field (PostgreSQL 12+)
  ALTER TABLE your_table ADD COLUMN updated_at TIMESTAMPTZ DEFAULT NOW() GENERATED ALWAYS AS (NOW()) STORED;
  -- For older versions, use a trigger instead
  CREATE OR REPLACE FUNCTION update_updated_at()
  RETURNS TRIGGER AS $$
  BEGIN
      NEW.updated_at = NOW();
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  CREATE TRIGGER trigger_update_updated_at BEFORE UPDATE ON your_table FOR EACH ROW EXECUTE FUNCTION update_updated_at();
  
  -- Fetch latest record
  SELECT * FROM your_table ORDER BY updated_at DESC LIMIT 1;
  

• For SQL Server:

  -- Add auto-updating field and trigger
  ALTER TABLE your_table ADD updated_at DATETIME DEFAULT GETDATE();
  CREATE TRIGGER trigger_update_updated_at ON your_table AFTER UPDATE AS
  BEGIN
      UPDATE your_table SET updated_at = GETDATE() WHERE id IN (SELECT id FROM Inserted);
  END;
  
  -- Fetch latest record
  SELECT TOP 1 * FROM your_table ORDER BY updated_at DESC;
  

Case 2: No updated_at field (not recommended!) #

If you can’t add a modification timestamp, your options are limited:

• Use auto-increment ID: If your table uses an auto-incrementing id field, you might assume the highest ID is the latest—but this only works for newly inserted records, not modified ones. Query:

  SELECT * FROM your_table ORDER BY id DESC LIMIT 1; -- Use TOP 1 for SQL Server
  

• Check table-level modification time: Most databases track when the entire table was last modified (e.g., MySQL’s information_schema.tables.UPDATE_TIME), but this doesn’t tell you which specific record changed. It’s a last resort.

This requires tracking changes over time, and the right approach depends on your scalability needs and real-time requirements. Here are the most common solutions:

1. Timestamp-based Polling (Simple & Low Effort) #

If real-time isn’t critical (e.g., you can tolerate a 1-5 minute delay), this is the easiest method. Use the updated_at field from the first requirement:

• Keep track of the last timestamp you queried (store this in a config table or application memory).
• Each time you run your sync/query, fetch all records where updated_at is greater than that last timestamp:

  SELECT * FROM main_table WHERE updated_at > '2024-05-20 14:30:00';
  

• Update your stored timestamp to the maximum updated_at from the results.

Pros: No complex setup, works with any database.
Cons: Has inherent delay, frequent polling can increase database load.

2. Database Triggers (Real-Time, Self-Contained) #

For instant change tracking, create a change log table and use triggers to log every insert/update/delete on your main table.

Step 1: Create a changelog table #

CREATE TABLE main_table_changelog (
    id SERIAL PRIMARY KEY,
    record_id INT NOT NULL, -- Links to main_table.id
    operation_type VARCHAR(10) NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    old_data JSONB, -- Only for UPDATE/DELETE
    new_data JSONB -- Only for INSERT/UPDATE
);

Step 2: Add triggers to the main table #

Example for PostgreSQL (similar logic works for MySQL/SQL Server):

-- Trigger for INSERTs
CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO main_table_changelog (record_id, operation_type, new_data)
    VALUES (NEW.id, 'INSERT', to_jsonb(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER main_table_insert_trigger AFTER INSERT ON main_table FOR EACH ROW EXECUTE FUNCTION log_insert();

-- Trigger for UPDATEs
CREATE OR REPLACE FUNCTION log_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO main_table_changelog (record_id, operation_type, old_data, new_data)
    VALUES (NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER main_table_update_trigger AFTER UPDATE ON main_table FOR EACH ROW EXECUTE FUNCTION log_update();

-- Trigger for DELETEs
CREATE OR REPLACE FUNCTION log_delete()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO main_table_changelog (record_id, operation_type, old_data)
    VALUES (OLD.id, 'DELETE', to_jsonb(OLD));
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER main_table_delete_trigger AFTER DELETE ON main_table FOR EACH ROW EXECUTE FUNCTION log_delete();

Now you can query main_table_changelog to get all changes, filtered by changed_at if needed.

Pros: Real-time tracking, captures full change history.
Cons: Adds overhead to main table operations; you’ll need to clean up old logs to avoid bloat.

3. Change Data Capture (CDC) (Enterprise-Grade, High Scalability) #

For large-scale systems or when you need to feed changes to other services (e.g., data warehouses, microservices), use your database’s built-in CDC features. This captures changes at the database level without affecting the main table’s performance.

• MySQL/MariaDB: Enable binary logging (binlog) and use tools like Debezium to capture changes and stream them to Kafka or another message broker.
• PostgreSQL: Use Logical Replication to create a publication of your main table, then subscribe to it to receive change events.
• SQL Server: Enable built-in CDC, which creates system tables to track changes that you can query or stream.

Pros: No impact on main table performance, supports real-time streaming, ideal for distributed systems.
Cons: Requires more setup and infrastructure (e.g., message brokers, CDC tools).

4. Version Numbering (Alternative to Timestamps) #

Instead of relying on timestamps (which can have clock sync issues), add a version integer field to your main table. Increment it every time a record is updated:

ALTER TABLE main_table ADD COLUMN version INT DEFAULT 1 NOT NULL;

-- When updating a record:
UPDATE main_table SET column1 = 'new_value', version = version + 1 WHERE id = 123;

To fetch changes, track the highest version you’ve seen, then query:

SELECT * FROM main_table WHERE version > 456 OR created_at > '2024-05-20 00:00:00';

(The created_at check catches new records, which start at version 1.)

Pros: Avoids timestamp-related bugs, simple to implement.
Cons: Same polling delay as the timestamp method if you’re not using triggers/CDC.

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