两个不同结构的数据库同步 mysql

-- 假设源数据库表结构
CREATE TABLE source_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

-- 目标数据库表结构
CREATE TABLE target_table (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(100),
    user_age INT
);

-- 同步数据时进行字段映射
INSERT INTO target_table (user_id, user_name, user_age)
SELECT id, name, age FROM source_table;

-- 添加时间戳字段
ALTER TABLE source_table ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- 同步时检查时间戳
INSERT INTO target_table (user_id, user_name, user_age)
SELECT id, name, age FROM source_table
WHERE updated_at > (SELECT MAX(updated_at) FROM target_table WHERE user_id = source_table.id);

-- 分批次同步数据
SET @batch_size = 1000;
SET @offset = 0;

WHILE (SELECT COUNT(*) FROM source_table LIMIT @offset, @batch_size) > 0 DO
    INSERT INTO target_table (user_id, user_name, user_age)
    SELECT id, name, age FROM source_table LIMIT @offset, @batch_size;
    SET @offset = @offset + @batch_size;
END WHILE;