MySQL生产库向Staging库同步数据报错及优化方案咨询

解决字段不匹配问题（无需手动指定字段） #

首先要确保staging.listing和production.listing的表结构完全一致，这是避免1136错误的核心。可以通过以下SQL自动生成结构对齐语句，不用手动编写所有字段：

1. 生成Staging表缺失字段的添加语句 #

SELECT CONCAT(
    'ALTER TABLE staging.listing ADD COLUMN ', 
    COLUMN_NAME, ' ', COLUMN_TYPE, ' ', 
    IF(IS_NULLABLE='YES', 'NULL', 'NOT NULL'), ' ', 
    IF(COLUMN_DEFAULT IS NOT NULL, CONCAT('DEFAULT ', QUOTE(COLUMN_DEFAULT)), ''), 
    ';'
) AS alter_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'production' AND TABLE_NAME = 'listing'
AND COLUMN_NAME NOT IN (
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'staging' AND TABLE_NAME = 'listing'
);

2. 生成Staging表字段属性修正语句（匹配Production表） #

SELECT CONCAT(
    'ALTER TABLE staging.listing MODIFY COLUMN ', 
    c1.COLUMN_NAME, ' ', c1.COLUMN_TYPE, ' ', 
    IF(c1.IS_NULLABLE='YES', 'NULL', 'NOT NULL'), ' ', 
    IF(c1.COLUMN_DEFAULT IS NOT NULL, CONCAT('DEFAULT ', QUOTE(c1.COLUMN_DEFAULT)), ''), 
    ';'
) AS alter_sql
FROM INFORMATION_SCHEMA.COLUMNS c1
JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.COLUMN_NAME = c2.COLUMN_NAME
WHERE c1.TABLE_SCHEMA = 'production' AND c1.TABLE_NAME = 'listing'
AND c2.TABLE_SCHEMA = 'staging' AND c2.TABLE_NAME = 'listing'
AND (
    c1.COLUMN_TYPE != c2.COLUMN_TYPE 
    OR c1.IS_NULLABLE != c2.IS_NULLABLE 
    OR COALESCE(c1.COLUMN_DEFAULT, '') != COALESCE(c2.COLUMN_DEFAULT, '')
);

执行上述查询得到的alter_sql语句，就能自动对齐两张表的结构。

解决锁表超限问题（高效增量同步） #

针对1206锁表错误，核心是避免一次性处理全量数据，采用分批增量同步的方式，每次只处理小批量记录，减少锁资源占用。

方案1：存储过程分批同步新增记录 #

创建存储过程，按主键id分段处理，每次同步指定数量的新记录：

DELIMITER //
CREATE PROCEDURE SyncListingToStaging()
BEGIN
    DECLARE batch_size INT DEFAULT 1000; -- 可根据服务器性能调整
    DECLARE max_prod_id INT;
    DECLARE current_min_id INT;
    DECLARE current_max_id INT;

    -- 获取生产表最大ID
    SELECT MAX(id) INTO max_prod_id FROM production.listing;
    -- 获取Staging表当前最大ID，无数据则设为0
    SELECT COALESCE(MAX(id), 0) INTO current_min_id FROM staging.listing;

    WHILE current_min_id < max_prod_id DO
        SET current_max_id = current_min_id + batch_size;
        
        -- 插入当前批次的新增记录
        INSERT INTO staging.listing
        SELECT * FROM production.listing
        WHERE id > current_min_id AND id <= current_max_id
        AND NOT EXISTS (SELECT 1 FROM staging.listing WHERE id = production.listing.id);
        
        SET current_min_id = current_max_id;
        COMMIT; -- 手动提交，释放锁资源
    END WHILE;
END //
DELIMITER ;

调用方式：CALL SyncListingToStaging();

方案2：Shell脚本定时分批同步 #

如果更倾向于用外部脚本控制，可编写Shell脚本（结合mysql命令）：

#!/bin/bash

BATCH_SIZE=1000
PROD_DB="production"
STAGING_DB="staging"
TABLE="listing"
DB_USER="your_username"
DB_PASS="your_password"

# 获取Staging表当前最大ID
current_min_id=$(mysql -u $DB_USER -p$DB_PASS -se "SELECT COALESCE(MAX(id), 0) FROM $STAGING_DB.$TABLE;")
# 获取生产表最大ID
max_prod_id=$(mysql -u $DB_USER -p$DB_PASS -se "SELECT MAX(id) FROM $PROD_DB.$TABLE;")

while [ $current_min_id -lt $max_prod_id ]; do
    current_max_id=$((current_min_id + BATCH_SIZE))
    # 执行同步
    mysql -u $DB_USER -p$DB_PASS -e "
        INSERT INTO $STAGING_DB.$TABLE
        SELECT * FROM $PROD_DB.$TABLE
        WHERE id > $current_min_id AND id <= $current_max_id
        AND NOT EXISTS (SELECT 1 FROM $STAGING_DB.$TABLE WHERE id = $PROD_DB.$TABLE.id);
        COMMIT;
    "
    current_min_id=$current_max_id
done

关键优化点 #

• 用NOT EXISTS替代左连接：相比原SQL的LEFT JOIN，NOT EXISTS在查询新增记录时效率更高，尤其是当id字段有主键索引时。
• 增量过滤：仅同步Staging库中不存在的新增记录（通过id范围或NOT EXISTS），避免全表扫描。
• 分批提交：每处理完一批数据就提交事务，及时释放锁资源，避免锁表超限。

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