如何在SQL中新增列并通过Python高效填充数组？解决executemany卡顿问题

首先说为啥你用executemany跑20分钟还没好：默认情况下mysql.connector的executemany是把每条UPDATE语句单独发给数据库执行，相当于跑了N次单条UPDATE，每条都要写日志、锁行，开销拉满，数据量大的时候自然慢到离谱。

下面给几个靠谱的解决方案，按速度从快到慢排：

方案1：用LOAD DATA INFILE（最快的批量更新方式） #

这是MySQL官方推荐的批量数据操作方式，速度比executemany快几个数量级，步骤如下：

1. 把你的id_count和对应的postal_id存成一个CSV文件（比如postal_mapping.csv），格式就是每行id_count,postal_id
2. 创建临时表，用来存这个映射关系：

CREATE TEMPORARY TABLE postal_map (
    id_count INT PRIMARY KEY,
    postal_id INT
);

3. 用LOAD DATA把CSV导入临时表：

LOAD DATA INFILE '/path/to/postal_mapping.csv'
INTO TABLE postal_map
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 如果CSV有表头就加这句

4. 关联临时表批量更新主表：

UPDATE vehicules v
JOIN postal_map pm ON v.id_count = pm.id_count
SET v.postal_id = pm.postal_id;

5. 最后可以删掉临时表（可选，临时表会话结束会自动删）：

DROP TEMPORARY TABLE postal_map;

如果是用Python操作，生成CSV可以用Pandas的to_csv，然后用cursor.execute()执行上面的SQL语句就行。

方案2：优化executemany的执行方式 #

如果不想搞CSV，调整mysql.connector的配置，让它真正批量执行，而不是逐行跑：

• 关闭自动提交，手动批量提交
• 确保使用的是支持批量执行的协议

示例代码：

import mysql.connector

# 建立连接，开启事务
conn = mysql.connector.connect(
    host='your_host',
    user='your_user',
    password='your_pass',
    database='your_db',
    autocommit=False  # 关闭自动提交
)
cursor = conn.cursor()

# 你的数据，格式是(postal_id, id_count)的元组列表
update_data = [(1,1), (1,2), (1,3), (2,4), (2,5)]

# 执行executemany
cursor.executemany(
    "UPDATE vehicules SET postal_id = %s WHERE id_count = %s;",
    update_data
)

# 手动提交事务
conn.commit()

cursor.close()
conn.close()

另外，如果数据量特别大，可以分成若干批次提交，比如每1000条提交一次，避免占用过多内存。

方案3：用INSERT ... ON DUPLICATE KEY UPDATE #

如果id_count是vehicules表的主键或者唯一键，可以用这条语句来批量更新：

INSERT INTO vehicules (id_count, postal_id)
VALUES (1,1), (2,1), (3,1), (4,2), (5,2)
ON DUPLICATE KEY UPDATE postal_id = VALUES(postal_id);

这条语句会尝试插入数据，如果id_count已经存在，就更新对应的postal_id，效率比逐行UPDATE高很多。

补充：为啥Pandas做起来这么简单？ #

因为Pandas是内存操作，直接在内存里给DataFrame的列赋值，不需要写磁盘、不需要处理事务日志，相当于在内存数组里填值，自然快。而SQL操作的是磁盘上的数据库，每一次更新都要涉及磁盘IO、事务日志写入、行锁等开销，逐行操作的话这些开销会被放大无数倍，所以不能用Pandas的思路套到SQL上。

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