如何利用SQL语句将旧版本数据库中的BLOB图片数据迁移到新表

旧表BLOB字段乱码或空值需先验证真实存在性：执行SELECT LENGTH(blob_column)确认长度非零，Oracle用DBMS_LOB.GETLENGTH、SQL Server用DATALENGTH更可靠；若长度正常但导出为空，可能因应用层加密或Base64封装，须核查旧代码中encodeBase64等调用。

确认旧表 BLOB 字段的存储格式和可读性

直接 SELECT 出来是乱码或空值？先验证数据是否真实存在且未损坏。很多旧系统用 LONG RAW、IMAGE（SQL Server 2005 前）或未指定字符集的 BLOB，导致新库无法识别。

实操建议：

• 在旧库执行 SELECT LENGTH(blob_column) FROM old_table WHERE id = 123，确认长度非零
• 用 DBMS_LOB.GETLENGTH（Oracle）或 DATALENGTH（SQL Server）比 LEN 更可靠
• 若长度正常但导出为空，可能是应用层做了加密或 Base64 封装——查旧代码里有没有 encodeBase64 或 encryptImage 类调用

INSERT … SELECT 迁移时必须绕过客户端字节流限制

用普通 JDBC/ODBC 驱动直连执行 INSERT INTO new_table (id, image_data) SELECT id, blob_column FROM old_table 很容易失败：驱动默认把 BLOB 当字符串处理，触发截断、编码转换或内存溢出。

实操建议：

• MySQL：确保连接参数含 useServerPrepStmts=true&allowLoadLocalInfile=true，并用 LOAD_FILE() 配合临时文件（仅限同机）
• PostgreSQL：用 pg_dump --column-inserts 导出时加 --inserts，再手动替换 lo_import 调用
• 通用稳妥法：改用服务端脚本（Python + psycopg2 / cx_Oracle），逐行 fetch 后用 cursor.setinputsizes(image_data=psycopg2.BINARY) 显式声明类型

新表字段类型与 NULL 约束必须匹配业务实际

迁完发现部分图片打不开？大概率是新表定义了 NOT NULL 但旧数据里有空 BLOB（即 EMPTY_BLOB() 或 0x），或者用了错误类型：比如 PostgreSQL 把 BYTEA 写成 TEXT，导致自动转义损坏二进制。

实操建议：

• Oracle 新表用 BLOB，别用 BFILE（路径依赖文件系统）
• MySQL 新表用 LONGBLOB，不是 MEDIUMBLOB（上限 16MB，老扫描仪图常超）
• 迁移前跑一次校验：SELECT COUNT(*) FROM old_table WHERE blob_column IS NULL OR DBMS_LOB.GETLENGTH(blob_column) = 0，结果不为 0 就得在 INSERT 里补 COALESCE(blob_column, EMPTY_BLOB())

迁移后校验不能只看行数一致

行数对得上，但图片缩略图全黑？说明二进制内容被静默篡改。常见于客户端设置了 character_set_client=utf8mb4 却去插二进制，MySQL 自动尝试 UTF-8 解码再报错回退，结果存入乱码。

实操建议：

• 抽样对比 MD5：SELECT DBMS_CRYPTO.HASH(blob_column, 2) FROM old_table WHERE id = 123 和新表对应记录比对（Oracle）
• PostgreSQL 用 md5(decode(image_data::text, 'escape'))（注意 escape 模式）
• 最简验证：用 hex() 或 TO_HEX() 取前 32 字节做字符串比对，比完整哈希快且足够暴露编码问题

真正麻烦的是跨数据库迁移（如 Oracle → PostgreSQL），BLOB 的 chunk 处理逻辑不同，哪怕字节一致，某些驱动仍会多写几个 x00。这种得在应用层加一层 CRC32 校验字段，而不是只信数据库层面的“迁移成功”。