MySQL中REPLACE INTO语句原理、用法与最佳实践

replace [into] table_name [(column_list)]
values (value_list)
-- 或
replace [into] table_name [(column_list)]
select ...

-- 从临时表同步数据到正式表
replace into products (id, name, price, stock)
select id, name, price, stock from temp_products;

-- 更新系统配置表
replace into system_config (config_key, config_value, update_time)
values ('max_connections', '100', now());

-- 更新缓存表数据
replace into user_cache (user_id, username, last_active)
values (123, 'john_doe', '2023-05-20 10:00:00');

-- 创建测试表
create table users (
    id int primary key auto_increment,
    username varchar(50) unique,
    email varchar(100),
    login_count int default 0
);

-- 第一次执行：插入新记录
replace into users (username, email, login_count)
values ('john_doe', 'john@example.com', 1);

-- 第二次执行（相同username）：替换原有记录
replace into users (username, email, login_count)
values ('john_doe', 'john.new@example.com', 2);

-- 创建有复合唯一键的表
create table user_roles (
    user_id int,
    role_id int,
    grant_date datetime,
    primary key (user_id, role_id)
);

-- 使用replace into
replace into user_roles (user_id, role_id, grant_date)
values (1001, 2, now());

-- 从一个表同步数据到另一个表
replace into target_table (id, col1, col2)
select id, col1, col2 from source_table
where update_time > '2023-01-01';

start transaction;
replace into important_table (...) values (...);
-- 检查影响行数或其他条件
commit; -- 或 rollback

# python 批量操作示例
def batch_replace(table, data_list, batch_size=1000):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            for i in range(0, len(data_list), batch_size):
                batch = data_list[i:i+batch_size]
                values = ", ".join([
                    f"({pymysql.escape_string(str(item['id']))}, "
                    f"'{pymysql.escape_string(item['name'])}')"
                    for item in batch
                ])
                sql = f"replace into {table} (id, name) values {values}"
                cursor.execute(sql)
        conn.commit()
    except exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

-- 方法1：insert ignore（忽略错误）
insert ignore into table (...) values (...);

-- 方法2：on duplicate key update（更新特定字段）
insert into table (...) values (...)
on duplicate key update update_time = now();