如何批量更新SQL表？替代逐行执行UPDATE语句（Python实现）

嘿，我刚好处理过类似的批量更新需求，给你两种实用的方案：一种是优化后的单条SQL批量更新（比执行100条独立UPDATE效率高太多，还能减少数据库连接开销），另一种是用Python自动生成这个SQL脚本，完美适配你的100组条件。

MySQL（以及大多数关系型数据库）支持用CASE WHEN语法在单条UPDATE语句里处理多组更新规则，这样不用重复写100条UPDATE，既简洁又高效。

针对你的示例，转换后的批量更新SQL长这样：

UPDATE internal.temorary_check
SET 
    monday = CASE
        WHEN ID_recipient = 23771085 AND `action date hour` = 1 THEN 1
        WHEN ID_recipient = 23771085 AND `action date hour` = 3 THEN 4
        WHEN ID_recipient = 32243506 AND `action date hour` = 1 THEN 7
        ELSE monday  -- 保持其他行不变
    END,
    tuesday = CASE
        WHEN ID_recipient = 23771085 AND `action date hour` = 1 THEN 2
        WHEN ID_recipient = 23771085 AND `action date hour` = 3 THEN 7
        WHEN ID_recipient = 32243506 AND `action date hour` = 1 THEN 1
        ELSE tuesday  -- 保持其他行不变
    END
WHERE 
    (ID_recipient = 23771085 AND `action date hour` IN (1, 3))
    OR (ID_recipient = 32243506 AND `action date hour` = 1);

注意：action date hour字段有空格，必须用反引号（`）包裹，否则SQL会报错。最后的WHERE子句是为了只更新需要修改的行，避免全表扫描，提升性能。

因为你有100组条件，手动写CASE WHEN太麻烦，用Python来拼接字符串就很省心。你可以把所有更新规则存在一个列表里，然后让脚本自动生成完整的SQL。

示例代码： #

# 把你的100组更新规则都放在这个列表里，格式是：(ID_recipient, action_date_hour, monday_val, tuesday_val)
update_rules = [
    (23771085, 1, 1, 2),
    (23771085, 3, 4, 7),
    (32243506, 1, 7, 1),
    # 剩下97组规则都按这个格式加进来
]

# 拼接CASE WHEN部分
monday_case = ["    monday = CASE"]
tuesday_case = ["    tuesday = CASE"]
where_conditions = []

for rule in update_rules:
    recipient_id, hour, mon_val, tue_val = rule
    # 拼接monday的CASE分支
    monday_case.append(f"        WHEN ID_recipient = {recipient_id} AND `action date hour` = {hour} THEN {mon_val}")
    # 拼接tuesday的CASE分支
    tuesday_case.append(f"        WHEN ID_recipient = {recipient_id} AND `action date hour` = {hour} THEN {tue_val}")
    # 拼接WHERE条件
    where_conditions.append(f"(ID_recipient = {recipient_id} AND `action date hour` = {hour})")

# 加上ELSE和END
monday_case.append("        ELSE monday")
monday_case.append("    END,")
tuesday_case.append("        ELSE tuesday")
tuesday_case.append("    END")

# 拼接完整的SQL
sql_parts = [
    "UPDATE internal.temorary_check",
    "SET",
    "\n".join(monday_case),
    "\n".join(tuesday_case),
    "WHERE",
    "    " + " OR ".join(where_conditions) + ";"
]

final_sql = "\n".join(sql_parts)

# 打印生成的SQL，或者保存到文件
print(final_sql)
# 也可以保存到文件
with open("batch_update.sql", "w") as f:
    f.write(final_sql)

代码说明： #

1. 你只需要把100组规则按格式添加到update_rules列表里就行，不用管拼接逻辑。
2. 脚本会自动处理action date hour字段的反引号包裹，避免语法错误。
3. 生成的SQL会自动包含WHERE条件，只更新需要修改的行，性能更好。
4. 最后可以直接打印SQL，或者保存成.sql文件，然后在数据库客户端执行。

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