如何基于t2.id变更自动同步更新t1.t2id？MySQL跨库方案咨询

Absolutely, you can use a stored procedure to handle this sync, but there are also more efficient, real-time or model-based solutions depending on your specific workflow and MySQL setup. Let’s break down your options clearly:

1. Stored Procedure + Scheduled Event (Timed Sync) #

This is a straightforward approach if you can tolerate a small delay between t2.id changes and t1.t2id updates. The idea is to create a procedure that matches records by the stable f field and updates mismatched t2id values, then schedule it to run after your daily t2.id changes.

Example Stored Procedure: #

DELIMITER //

CREATE PROCEDURE SyncT1T2ID()
BEGIN
    -- Update only records where t2id doesn't match the current t2.id for the same f
    UPDATE db1.t1 t1
    JOIN db2.t2 t2 ON t1.f = t2.f
    SET t1.t2id = t2.id
    WHERE t1.t2id != t2.id;
END //

DELIMITER ;

Schedule It with MySQL Event Scheduler: #

First, make sure the event scheduler is enabled, then create a daily event:

-- Enable event scheduler (run once if not already on)
SET GLOBAL event_scheduler = ON;

-- Create event to run after your daily t2.id update window
CREATE EVENT SyncT1T2IDDaily
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:30:00' -- Adjust to your post-update time
DO
    CALL SyncT1T2ID();

Pros: Simple to implement, low overhead if t2 changes are infrequent.
Cons: Has inherent delay; relies on t2.f being a unique identifier (which it should be, given your matching logic).

2. Trigger (Real-Time Sync) #

If you need immediate sync when t2.id changes, a trigger on t2 is the way to go. This will automatically update t1 the moment t2.id is modified.

Example Trigger: #

DELIMITER //

CREATE TRIGGER AfterT2IDUpdate
AFTER UPDATE ON db2.t2
FOR EACH ROW
BEGIN
    -- Update all t1 records linked via the same f value
    UPDATE db1.t1
    SET t2id = NEW.id
    WHERE f = NEW.f AND t2id != NEW.id;
END //

DELIMITER ;

Pros: Real-time sync, no delay.
Cons: Adds minor overhead to t2 updates; requires t2.f to be unique; make sure the MySQL user has cross-database permissions to modify db1.t1 from db2's trigger.

3. Data Model Overhaul (Root Cause Fix) #

The most sustainable solution is to eliminate the need for sync entirely by adjusting your data model. Since t2.id changes daily, it’s not a stable identifier—so why rely on it?

Instead of storing t2id in t1, just keep the f field (which is the actual link between the two tables). When querying, join directly on t1.f = t2.f instead of t1.t2id = t2.id. This way, no sync is ever needed, regardless of how often t2.id changes.

Pros: Eliminates sync maintenance entirely, reduces data redundancy.
Cons: Requires modifying application query logic (if your app currently uses t2id for joins).

4. Bonus: Binlog-Based Sync (For Complex Scenarios) #

If t2.id changes via delete/reinsert instead of updates (triggers won’t catch deletes), you can use a binlog parsing tool to monitor t2’s changes and trigger updates to t1. This is more complex but works for advanced workflows where triggers or scheduled procedures fall short.

Quick Notes for All Solutions: #

• Always ensure t2.f has a unique constraint—this prevents ambiguous matches that could break your sync logic.
• Add transaction handling where needed (e.g., wrap the stored procedure logic in START TRANSACTION; ... COMMIT;) to avoid partial updates.
• Test edge cases: What if a t2 record is deleted? Decide if you want to set t1.t2id to NULL, keep the old value, or take other action based on your business rules.

内容的提问来源于stack exchange，提问作者m.alsioufi