如何通过SQL将一个表中的BLOB字段批量复制到另一个表

mysql中直接insert ... select无法处理大blob字段，因协议限制导致packet超限；须用sscursor流式读取+executemany批量写入，禁用缓存、控制批次、确保类型一致且max_allowed_packet足够。

如何通过sql将一个表中的blob字段批量复制到另一个表_流式读写处理

MySQL中直接INSERT ... SELECT无法处理BLOB字段？

不能。MySQL 8.0+ 虽支持 INSERT INTO t2 SELECT * FROM t1，但若表含 BLOB、TEXT 或大 JSON 字段，且单行超 max_allowed_packet（默认4MB），会直接报错 Packet for query is too large。这不是语法问题，是客户端/服务端协议限制——整个SQL语句（含二进制内容）被打包发送，BLOB被转义为十六进制字符串（如 0x89504E47...），体积翻倍，极易触发截断或拒绝。

所以必须绕过“全量拼SQL”路径，改用流式逐行读取 + 参数化写入。

Python + pymysql 流式读写BLOB的最小可靠写法

关键不是“快”，而是“不OOM、不断连、不丢数据”。需禁用 cursor 缓存、手动控制 fetch 大小、用 executemany 批量提交（但每批不超过 100 行，避免参数过多）：

import pymysql
<p>conn_src = pymysql.connect(host='a', database='db1', cursorclass=pymysql.cursors.SSCursor)  # 关键：SSCursor = ServerSide Cursor
conn_dst = pymysql.connect(host='b', database='db2')</p><p>cur_src = conn_src.cursor()
cur_dst = conn_dst.cursor()</p><p>cur_src.execute("SELECT id, name, content FROM src_table WHERE status = 'ready'")</p><p>batch_size = 100
while True:
rows = cur_src.fetchmany(batch_size)
if not rows:
break</p><h1>注意：BLOB字段（如content）在rows中是bytes对象，无需decode</h1><pre class="brush:php;toolbar:false;">cur_dst.executemany(
    "INSERT INTO dst_table (id, name, content) VALUES (%s, %s, %s)",
    rows
)
conn_dst.commit()

conn_src.close() conn_dst.close()

SSCursor 防止源库结果集全加载进内存；普通 Cursor 会把全部BLOB缓存住，1000行 × 2MB = 2GB 内存直接爆掉
不用 fetchall() —— 即使数据量小也不推荐，逻辑不健壮
目标表 content 字段类型必须与源表严格一致（如都是 MEDIUMBLOB），否则 pymysql 可能静默截断
若跨实例（如主从分离），确保 max_allowed_packet 在目标库也调高（至少 ≥ 单个BLOB最大尺寸）

PostgreSQL用copy_expert做BLOB流式迁移更高效

PostgreSQL 的 copy_expert 直接走二进制 COPY 协议，绕过SQL解析层，吞吐量比逐行 execute 高 5–10 倍，且天然支持大对象：

import psycopg2
from io import BytesIO
<p>conn_src = psycopg2.connect("dbname=db1 host=a")
conn_dst = psycopg2.connect("dbname=db2 host=b")</p><p>cur_src = conn_src.cursor()
cur_dst = conn_dst.cursor()</p><p>cur_src.execute("SELECT id, name, content FROM src_table")
while True:
rows = cur_src.fetchmany(500)
if not rows:
break</p><h1>构造COPY格式：每行用\t分隔，BLOB用bytes原样写入</h1><pre class="brush:php;toolbar:false;">f = BytesIO()
for row in rows:
    # 假设 content 是第3列，bytes类型；其他列转str，\t连接
    line = f"{row[0]}\t{row[1]}\t".encode() + row[2] + b"\n"
    f.write(line)
f.seek(0)
cur_dst.copy_expert(
    "COPY dst_table (id, name, content) FROM STDIN WITH (FORMAT BINARY)",
    f
)
conn_dst.commit()

copy_expert 要求目标表字段顺序、类型、NOT NULL约束完全匹配源查询结果，否则报 column "xxx" is of type bytea but expression is of type text
必须用 FORMAT BINARY，文本格式（CSV/Text）对BLOB编码不可靠
PostgreSQL 默认 bytea_output = 'hex'，但 copy_expert 二进制模式下自动处理，不用干预

容易被忽略的三个硬性前提

流式迁移不是加个循环就完事，以下三点任一缺失都会导致静默失败或数据损坏：

源表必须有确定顺序和可分片条件（如 id > ? ORDER BY id LIMIT ?），否则 SSCursor 或游标可能漏行或重复读（尤其并发写入时）
目标库连接必须开启 autocommit=False（pymysql默认关，psycopg2默认开），否则每条 execute 都自动提交，性能崩盘且无法回滚
BLOB字段在源库实际存储方式影响读取行为：若用 LONGTEXT 存base64字符串，需先 base64.b64decode()；若用 oid 指向大对象（pg_largeobject），必须用 lo_get() 显式读取，不能直接查字段